'How do I set SQL mode in the JDBC connection string for MySQL 8?

I recently upgraded my MySQL instance from 5.7 to 8.0. I connected to my old instance using JDBC and a connection string that looked like this:

jdbc:mysql://[host:port]/[database]/?sessionVariables=sql_mode=''

After upgrading to 8.0 that results in this error

com.mysql.cj.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near '='')'.

How do I set SQL mode in the JDBC connection string for MySQL 8?

mysqljdbcconnection-stringmysql-8.0


Solution 1:[1]

Try this for MySQL 8.0 in your JDBC connection string:

jdbc:mysql://[host:port]/[database]/?sessionVariables=&&sql_mode=''

e.g.

jdbc:mysql://localhost:3306/dbName?sessionVariables=&&sql_mode=''



For MySQL 7.0 in your JDBC connection string:

jdbc:mysql://[host:port]/[database]/?sessionVariables=sql_mode=''

e.g.

jdbc:mysql://localhost:3306/dbName?sessionVariables=sql_mode=''

Solution 2:[2]

I faced the same problem with an old project and setting sessionVariables=sql_mode='' didn't work in my case. It was totally ignored. Finally I found this bug report and its explanation:

The driver needs the STRICT_TRANS_TABLES mode enabled to enforce JDBC compliance on truncation checks.

If you can't use STRICT_TRANS_TABLES as part of your sql_mode, then you'll have to disable truncation checks by adding "jdbcCompliantTruncation=false" as a URL configuration parameter.

And indeed, setting jdbcCompliantTruncation=false worked for me.

Solution 3:[3]

I think it could be because there's nothing in the sql_mode.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Marc
Solution 2 Flo
Solution 3

Related Questions