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.