Data Update from MYSQL not appear

[dean92 (Hafizuddin)]

hi all,

I have one problem on my data. I export one table and a few column on my MYSQL to ES using logstash. there are one column(status_ticket) in table is frequently change e.g: from status new to open/closed and it effective to every row in table. first time i push config, the data look fine and tally with my DB but after a few moment i release, in DB that column(status_ticket) already change value but in Kibana, data still remain as first time i load. The only changes in my data at Kibana happen when there are new entry registered in my DB because i put id as :sql_last_value.

here my config:

input {
jdbc {
jdbc_driver_library => "C:\Program Files\mysql-connector-java-8.0.17\mysql-connector-java-8.0.17.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/nex?useTimezone=true&serverTimezone=UTC"
jdbc_user => "root"
jdbc_password => ""
schedule => "* * * * *"
statement => "SELECT id, id_ticket, cause_of_fault, created_date, Portion FROM td_ticket_closed WHERE id > :sql_last_value"
use_column_value => true
tracking_column => "id"
}
}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "nexdesk-test"
}
stdout {}

[Christian_Dahlqvist]

The query you are using need to be able to show the changes when run repeatedly. If I am reading your SQL query correctly you are only fetching new rows as you are looking for only new ids. If you want updates captured you probably need to add a last updated timestamp and use this with your sql_last_value condition instead.

[dean92 (Hafizuddin)]

it is possible if I used more that one tracking column?

[Christian_Dahlqvist]

No, I do not see that being a possibility.

[dean92 (Hafizuddin)]

I have one column with name update_date but i my table structure shown in varchar format. I try to use it as sql_last_ value but now my data duplicate every minutes.

input {
jdbc {
jdbc_driver_library => "C:\Program Files\mysql-connector-java-8.0.17\mysql-connector-java-8.0.17.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/nex?useTimezone=true&serverTimezone=UTC"
jdbc_user => "root"
jdbc_password => ""
schedule => "* * * * *"
statement => "SELECT id, id_ticket, cause_of_fault, created_date, Portion, updated_date FROM td_ticket_closed WHERE updated_date > :sql_last_value"
use_column_value => true
tracking_column => "updated_date"
}
}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "nexdesk-test"
}
stdout {}