Logstash skip records by adding latest updated record date in last run metadata file

My goal is to set up continuous data migration from MySQL to Elasticsearch. Everything is working fine but I've stuck with :sql_last_value. My server and database timezone is in UTC format.

Problem: When i update 4 to 6 records at mysql side. logstash should update those records in Elasticsearch. But logstash skipping two or three records. but putting the lastest updated record date in .logstash_jdbc_last_run file.

Example: I have update 4 records from mysql side. These records update_at date is as follow

2019-11-25 12:29:01
2019-11-25 12:29:58
2019-11-25 12:29:59
2019-11-25 12:29:00

logstash update the date in logstash_jdbc_last_run file which is '2019-11-25 12:29:01' and skip the 2 records which has dates as follow

2019-11-25 12:29:59
2019-11-25 12:29:00

Logstash logs it in local timezone, so next time it queries db, :sql_last_value value is correct.

So if the last received datetime field from db was like 2019-11-25 12:29:01, next query to db will be for :sql_last_value > 2019-11-25 12:29:01 and it missed the records with a timeframe of 3 to 5 seconds.

Following is my config file
@magnusbaeck please reply

jdbc {
jdbc_connection_string => "----"
jdbc_user => "------"
jdbc_password => "-----------"
jdbc_validate_connection => true
#jdbc_driver_library => "/home/ubuntu/mysql-connector-java-5.1.36-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
tracking_column => "updated_timestamp"
tracking_column_type => "timestamp"
use_column_value => true
jdbc_default_timezone => "UTC"
schedule => "* * * * * *"
statement => "SELECT
id as db_id,
DATE_FORMAT(updated_timestamp, '%Y-%m-%d %T') as updated_timestamp,
CONCAT('u','-', following_id) as user_id,
CONCAT('fg','-', id) as id,
CONCAT('u','-', follower_id) as object_id,
updated_timestamp > :sql_last_value order by updated_timestamp DESC "
last_run_metadata_path => "/home/ubuntu/logstash_track_date/.logstash_followings_jdbc_last_run"
add_field => { "doctype" => "followings" }

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.