Kindly find my conf file as below:
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://172.23.125.52:3304/faredge"
jdbc_user => "faredge"
jdbc_password => "faredge"
jdbc_driver_library => "D:\Apeksha\ELK_6.4_Series\logstash-5.4.0\mysql-connector-java-5.1.47\mysql-connector-java-5.1.47-bin.jar"
jdbc_driver_class => "Java::com.mysql.jdbc.Driver"
statement => "select id, txn_type, request_time from faredge.txn_data_table WHERE request_time > CURRENT_DATE - interval '8' day"
use_column_value => true
tracking_column => "request_time"
tracking_column_type => "timestamp"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
jdbc_default_timezone => "Asia/Kolkata"
last_run_metadata_path => "C:\Users\apeksha.bhandari\.logstash_payse_sql_001"
schedule => "0 */1 * * *"
}
}
filter {
mutate {
convert => {
"mobile_no" => "string"
}
}
}
output {
stdout { codec => json }
elasticsearch {
hosts => ["10.1.54.76:9200"]
index => "thurs_28_002"
document_id => "%{id}"
retry_on_conflict => 3
}
file {
codec => json_lines
path => "D:\Apeksha\logstash-5.4.0\thurs_28_002.log"
}
}
If i run scheduler every 5min, i am not facing any issue, but if i use scheduler to run query every system hour, i get below error:
The last packet successfully received from the server was 1,424,940 milliseconds ago. The last packet sent successfully to the server was 30 milliseconds ago.: SELECT count(*) AS count FROM (select id, txn_type, request_time from faredge.txn_data_table WHERE request_time > CURRENT_DATE - interval '8' day) AS t1 LIMIT 1
17:00:00.188 [Ruby-0-Thread-18: D:/Apeksha/logstash-5.4.0/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:283] WARN logstash.inputs.jdbc - Exception when executing JDBC query {:exception=>#<Sequel::DatabaseDisconnectError: Java::ComMysqlJdbcExceptionsJdbc4::CommunicationsException: Communications link failure
The last packet successfully received from the server was 1,424,940 milliseconds ago. The last packet sent successfully to the server was 30 milliseconds ago.>}
Kindly help.