JDBC Input plugin, Communication link failure in Logstash version 6.5.4 (latest)

Elastic Stack Logstash
1

Hi,

I am facing this issue with JDBC input plugin.

Sometime my logstash script throw this error :

[2019-01-21T00:59:25,984][ERROR][logstash.inputs.jdbc ] Unable to connect to database. Tried 1 times {:error_message=>"Java::ComMysqlCjJdbcExceptions::CommunicationsException: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server."}
[2019-01-21T00:59:26,016][ERROR][logstash.pipeline ] A plugin had an unrecoverable error. Will restart this plugin.
Pipeline_id:main
Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"bigdata_usr", jdbc_password=>, statement=>"select bigdata_id,brand_id,brand as brand_name,cur_level as level,CONCAT(DATE(now()),'T',TIME(now()),'Z')timestamp from fwall_level_bigdata\nWHERE pub_date >= DATE_ADD(CURRENT_DATE, INTERVAL - 1 MONTH) AND brand IS NOT NULL AND brand !='' and bigdata_id is not null;", jdbc_driver_library=>"/opt/db-recommendation/lib/mysql-connector-java-6.0.6.jar", jdbc_connection_string=>"jdbc:mysql://192.168.0.1:3306/Databasename", id=>"e2eae9c25bca0d594a94b9d9f8c54f9e26ec64ab1e0d728f65263e930bcde45c", jdbc_driver_class=>"com.mysql.cj.jdbc.Driver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_28d41ba3-ab3e-4de0-8ce6-7e2f585c3720", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validate_connection=>false, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts=>1, connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>2019-01-21 00:52:27 +0530}, last_run_metadata_path=>"/home/centos/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
Error: Java::ComMysqlCjJdbcExceptions::CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Exception: Sequel::DatabaseConnectionError
Stack: com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(com/mysql/cj/jdbc/exceptions/SQLError.java:590)
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(com/mysql/cj/jdbc/exceptions/SQLExceptionsMapping.java:57)
com.mysql.cj.jdbc.ConnectionImpl.createNewIO(com/mysql/cj/jdbc/ConnectionImpl.java:1606)
com.mysql.cj.jdbc.ConnectionImpl.(com/mysql/cj/jdbc/ConnectionImpl.java:633)
com.mysql.cj.jdbc.ConnectionImpl.getInstance(com/mysql/cj/jdbc/ConnectionImpl.java:347)
com.mysql.cj.jdbc.NonRegisteringDriver.connect(com/mysql/cj/jdbc/NonRegisteringDriver.java:219)
java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)

And pipeline go in indefinite state. I am using the latest version still this issue is coming.

How can i fix this error ??

And if i want to apply retry on this error. How can I do that ??

Please help.

2

probably your database is getting down. Please check your database connection when this error occurs.

3

I have checked this, no issues on database side.

4

ok then can you provide your input code to validate

5

Try setting connection_retry_attempts to 5
Look at increasing the connection_retry_attempts_wait_time to 2 (seconds)

6

input {
jdbc {
jdbc_connection_string => "jdbc:mysql://IP:PORT/DATABASE"
jdbc_user => "username"
jdbc_password => "password"
jdbc_driver_library => "/opt/db-recommendation/lib/mysql-connector-java-6.0.6.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_validate_connection => true
jdbc_validation_timeout => 120
jdbc_pool_timeout => 60
jdbc_paging_enabled => true
jdbc_page_size => 50000
statement => "select bigdata_id,brand_id,brand as brand_name,cur_level as level,CONCAT(DATE(now()),'T',TIME(now()),'Z')timestamp from fwall_level_bigdata
WHERE pub_date >= DATE_ADD(CURRENT_DATE, INTERVAL - 1 MONTH) AND brand IS NOT NULL AND brand !='' and bigdata_id is not null"
}
}

This is my updated input code in conf file.

7

Thanks.

I will try this.

8

Hi,

Thanks for replying,

But these settings are also not working. After printing Communication link failure in logs the pipeline terminates and not retrying.