Oracle JDBC connection - sql_last_value TIMESTAMP comparison isn't working as expected

[LChand]

Hello,

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 :
input{
jdbc{
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

[elasticforme (Sachin)]

this is what I will do

"select LOG_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE_BEFORE, STATE_AFTER, USERID from ppv where log_date > sysdate - interval '60' minute order by LOG_DATE"

which will pull last one hour date.

and you don't need last_run, column_value, tracking_column etc...

[LChand]

Thanks for the response. But I would like to use the database field log_date value (UTC timezone) to compare and pick from where it was left last rather than sysdate which is in EDT timezone. Strangely, I've jdbc connections to SQL servers and similar comparison with sql_last_value works fine there.. any ideas what might be the issue here?

[elasticforme (Sachin)]

I try on early time when I was testing elk without 100% success. but decided to use my own control over logstash deciding what to pull