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

[saifrehman]

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.

[saifrehman]

Following is my config file
@magnusbaeck please reply

input{
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,
created_at,
CONCAT('fg','-', id) as id,
CONCAT('u','-', follower_id) as object_id,
id,
status
FROM
friends
WHERE
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" }
}
}