Looking for some help asap please... I've an issue where same set of data i.e. all records for the current day gets picked with every scheduled run during the day even though the sql_last_value has the latest date-time value. It seems like the comparison isn't working.
Environment info -
*Logstash server - logstash-7.10.2-1.x86_64 ; *
Logstash Server is in EDT time zone
JDBC Driver Library - ojdbc7.jar
Log_Date Oracle field value is in UTC timezone
Here's an example :
Value in .logstash_jdbc_last_run before the scheduled run at 12:00 pm EDT (Pls note the below value is in UTC coming from the Oracle view)
-- !ruby/object:DateTime '2022-08-29 12:44:09.000000000 Z'
Entry in Logstash logfile during the scheduled run ..
[2022-08-29T12:30:03,549][INFO ][logstash.inputs.jdbc ] (2.489731s) select LOG_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE_BEFORE, STATE_AFTER, USERID from ppv where log_date > TIMESTAMP '2022-08-29 12:44:09.000000 +00:00' order by LOG_DATE
I output the SQL records to a file and found all records for 29th including the ones prior to 2022-08-29 12:44:09 which should have been ignored if the comparison worked correctly.
I noticed that .logstash_jdbc_last_run had a timestamp of 12:30 when the scheduled run took place
-rw-r--r-- 1 logstash logstash 60 Aug 29 12:30 .logstash_jdbc_last_run
and this the value .. there are actually no new records in the Oracle view after this date-time.
--- !ruby/object:DateTime '2022-08-29 12:44:09.000000000 Z'
This is my input config :
jdbc_driver_library => "/etc/logstash/sjdbc/ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))(DESCRIPTION=(RETRY_COUNT=3)))"
jdbc_user => "xxxxx"
jdbc_password => "xxxxx"
schedule => "*/55 * * * *"
statement => "select LOG_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE_BEFORE, STATE_AFTER, USERID from ppv where log_date > :sql_last_value order by LOG_DATE"
record_last_run => true
jdbc_default_timezone => "UTC"
use_column_value => true
tracking_column => "log_date"
tracking_column_type => "timestamp"
last_run_metadata_path => "/xxxxx/.logstash_jdbc_last_run"
I really appreciate any advice you can provide ... Thanks