JDBC MySql SQL_MODE

[sebestenyb (Balazs Sebesteny)]

Hello,

I'm trying to set sql_mode for the logstash jdbc connection as per below, but get an error

input {
    jdbc {
        clean_run => true
        jdbc_connection_string => "jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"
        jdbc_user => "****"
        jdbc_password => "****"
        schedule => "* * * * *"
        jdbc_validate_connection => true
        jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-6.0.6.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        statement => "SELECT ..."
    }
}

The error is

[ERROR][logstash.inputs.jdbc     ] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near '=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'."}

What am I doing wrong here?

Thanks for any help!

Cheers, Sebi

[guyboertje]

Based on Connector/J 5.1 Developer Guide - you should probably not include the single quotes.

[sebestenyb (Balazs Sebesteny)]

Unfortunately that doesn't work either, the error message is the same:

[2017-10-09T10:20:02,209][ERROR][logstash.inputs.jdbc     ] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near '=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'."}

Actually even if I try to set only one mode, like:

jdbc_connection_string => "jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode=STRICT_TRANS_TABLES"

I still get the same error