Session variables not recognized


I'm currently trying to set the sql_mode of my MySQL connection to NO_ENGINE_SUBSTITUTION (only) using the sessionVariables field in Data Sources and Drivers > Advanced. Hereby I'm using the following as value: sql_mode='NO_ENGINE_SUBSTITUTION'.

Unfortunately this setting doesn't seem to be recognized. In my particular test case I created a new procedure after saving the settings and reconnecting to my db, checking if the mysql.proc table is showing the correct value in column sql_mode for that procedure. 



Which I assume is the default, because it looked the same before my changes. In my case I want to explicitly turn off STRICT_TRANS_TABLES though.

I also tried 'sql_mode=NO_ENGINE_SUBSTITUTION' and sql_mode=NO_ENGINE_SUBSTITUTION (different quotations), but nothing worked.

Please help, this issue is rendering the software practically useless for me.

Comment actions Permalink

@Sebastian Haesener 
Could you specify your MySQL server and driver version?

I can't reproduce the issue:

According to documentation it works correctly.



As of MySQL 5.7.22, DB2 is deprecated. It will be removed in a future version of MySQL.

Make sure you have no errors in Event Log and there are no spaces in value.

Comment actions Permalink

Hi Vasily,

I'm using MySQL 5.7.22 and the latest MySQL Driver (jdbc 5.1.46).

Below you see a screenshot of my advanced connection settings:

Using a combination sql mode like you did with DB2 is not an option for me, especially since most of these - including DB2 - are deprecated as of MySQL 5.7.22.

The expected behaviour of the software based on above settings should be something like: 


Instead I see the following result:

On the right you can clearly see, that the software somehow added STRICT_TRANS_TABLES. In fact, no matter what I set the sql_mode to (via sessionVariables) Datagrip always adds STRICT_TRANS_TABLES.

And just to be 100% clear: I'm using the latest version of Datagrip and there's no errors in the event log nor any spaces in value.

Comment actions Permalink

@Sebastian Haesener 

According to MySQL Connector/J bugtracker it works as intended:

[17 Oct 2006 14:36] Mark Matthews
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.

If to look through MySQL documentation one can find the following:

Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings (MySQL 4.1.0 and newer)? This property has no effect if the server sql-mode includes STRICT_TRANS_TABLES.
Default: true
Since version: 3.1.2

So, one needs to configure Advanced options properly to get the desired result:


Comment actions Permalink

Hi Vasily,

thanks a lot for pointing that out that tiny but important detail, it resolved the issue for me.


Please sign in to leave a comment.