Session variables not recognized

Completed
Avatar
Sebastian Haesener
Created

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. 

Result:

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

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.

Votes

1

Share

4 comments
Avatar
vasily chernov
Created

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

I can't reproduce the issue:



According to documentation https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_db2 it works correctly.

DB2

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
Note

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.

0
Avatar
Sebastian Haesener
Created

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: 

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

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.

0
Avatar
vasily chernov
Created

@Sebastian Haesener 

According to MySQL Connector/J bugtracker https://bugs.mysql.com/bug.php?id=23371 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 https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html one can find the following: 

jdbcCompliantTruncation
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:

 

1
Avatar
Sebastian Haesener
Created

Hi Vasily,

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

1

Please sign in to leave a comment.