We are pulling data from mysql and pushing to elasticsearch through logstash.
Following is the configuration for jdbc input:
jdbc_driver_library => '/home/software/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar'
jdbc_driver_class => 'com.mysql.jdbc.Driver'
jdbc_connection_string => 'jdbc:mysql://url:ip/xx?tinyInt1isBit=false'
jdbc_user => 'XXXX'
jdbc_password => 'XXXX'
schedule => '*/1 * * * *'
statement => "select
id from infi_buzz_es_data where id > :sql_last_value order by id"
last_run_metadata_path => '/etc/logstash/.logstash_jdbc_last_run_es'
record_last_run => true
clean_run => false
use_column_value => true
tracking_column => 'id'
tracking_column_type => 'numeric'
jdbc_paging_enabled => 'true'
jdbc_page_size => '100000'
In logs i can see that the sql queries are being executed with any errors, but '/etc/logstash/.logstash_jdbc_last_run_es doesn't get updated with the last run value. Its always executing the query with id > 0.
when i manually created the file and entered a value(say, 100) it executes the query with id >100, but still doesn't get updated.
i had disabled es output and enabled stdout for debugging, there was (correction) output at stdout.
logstash version: 5.6.5
whey doesn't the file get updated?
logstash was started using initctl command of amazon linux server.
We were connecting to mariadb with mysql jdbc connector. We have another setup of es, logstash, mariadb where it works fine. In this case, the setup is in aws. We changed the connector to mariadb driver and it started creating the file. But, only if we pass "sessionVariables=sql_mode=ANSI_QUOTES" in jdbc string we are able to execute the sql query otherwise it throws syntax error. Here, if we pass this variable logstash won't create/update the last run file.