Menu
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?
309
To set the SQL mode at server startup, use the --sql-mode=" modes " option on the command line, or sql-mode=" modes " in an option file such as my. cnf (Unix operating systems) or my. ini (Windows). modes is a list of different modes separated by commas.
3.3. How do you determine the server SQL mode? You can set the default SQL mode (for mysqld startup) with the --sql-mode option. Using the statement SET [GLOBAL|SESSION] sql_mode=' modes ' , you can change the settings from within a connection, either locally to the connection, or to take effect globally.
To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement: SET GLOBAL sql_mode = 'modes'; SET SESSION sql_mode = 'modes'; Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on.
Enable SQLCMD Scripting by Default To turn SQLCMD scripting on by default, on the Tools menu select Options, expand Query Execution, and SQL Server, click the General page, and then check the By default open new queries in SQLCMD Mode box.
It seems to be a bug in MySQL Connector/J. I have raised a bug report for it:
https://bugs.mysql.com/bug.php?id=92485
68
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=''
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.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
