# JDBC MySql SQL\_MODE

**URL:** <https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496>\
**Category:** Logstash\
**Created:** [October 3, 2017, 12:13am UTC](https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496 "2017-10-03T00:13:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sebestenyb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sebestenyb/32/22711_2.png) [@sebestenyb](https://discuss.elastic.co/u/sebestenyb)\
**Post date:** [October 3, 2017, 12:13am UTC](https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496/1 "2017-10-03T00:13:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [October 5, 2017, 11:26am UTC](https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496/2 "2017-10-05T11:26:54Z")

</div>

Based on [Connector/J 5.1 Developer Guide](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html) - you should probably not include the single quotes.

---

<div class="post-metadata">

**Author:** ![sebestenyb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sebestenyb/32/22711_2.png) [@sebestenyb](https://discuss.elastic.co/u/sebestenyb)\
**Post date:** [October 8, 2017, 10:16pm UTC](https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496/3 "2017-10-08T22:16:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 5, 2017, 10:16pm UTC](https://discuss.elastic.co/t/jdbc-mysql-sql-mode/102496/4 "2017-11-05T22:16:37Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.