Force the MySQL SQL_MODE on connection?
Answered
Does DataGrip have any way to allow me to set the SQL_MODE automatically for my connection to a MySQL database?
For example, MySQL Workbench has a specific field for the connection that would set the SQL_MODE. Whilst HeidiSQL provide a way to execute a Startup Script.
Please sign in to leave a comment.
I'm interested in this ability as well. I can't find a way to "SET SESSION sql_mode = 'mode'" when connecting to MySQL.
Hi,
You can specify your SQL_MODE in URL for Data Source settings, e.g.:
jdbc:mysql://<host>:<port>/<db_name>?user=<username>&password=<pwd>&&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT'
Thank you.
The syntax provided by vasily does not seem to apply to DataGrip (perhaps it does for other IntelliJ products.)
But it did reveal the use of sessionVariables which is key to getting this working.
In DataGrip, go to File > Data Sources and select your connection from the list of Project Data Source. Then go to the Advanced tab which list many variables. Find the one named sessionVariables and set it's value with the SQL mode. For example::
sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'
@Courtney Miles, @Jonathan Marzinke Hi,
To provide SQL_MODE in URL you need to switch to URL only mode:
Also, sessionVariables property is available in Advanced tab of data source:
Thank you.