Mysql – session sql mode + default sql mode

MySQLmysql-5.7

I am testing my application in mysql 5.7 and have some code that doesn't work with ONLY_FULL_GROUP_BY ON. I will fix this in a later release. I found a way to turn this off for just my application by executing 'SET SESSION sql_mode=""' before running any queries.

I have 2 questions:

  1. Will this work on shared host services that don't have root access to database? It appears to have worked on my testing.

  2. Since the mode is set to '' what does this actually mean? To me it seems like it is setting it back to nothing and making at non restrictive. (My other thought is that it set it to nothing and lets mysql decide what to do).

Based on my reading of the manual empty mode use to be default in mysql 5.1 but has become more restrictive with each version. For mysql 5.7 it is ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

So based on this it would seem that mode is a list of rules and if it is empty non the rules apply?

Also if I set the mode to "" will this always disable the modes set by server?

Best Answer

  • SET SESSION variables apply only to the current connection.
  • SET GLOBAL variables apply as the default when a connection is made.
  • SESSION does not need root access; GLOBAL does.
  • Be careful what you set it to:

Let's say you find that the default is

mysql> SELECT @@sql_mode;
+--------------------------------+
| @@sql_mode                     |
+--------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_DATE |
+--------------------------------+

Blindly setting it will turn of those two while setting the one you want:

mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY';
mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+

So, build the full string:

mysql> SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY';

mysql> SELECT @@sql_mode;
+---------------------------------------------------+
| @@sql_mode                                        |
+---------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_DATE |
+---------------------------------------------------+

Addenda

If you want to add/subtract a mode without knowing what mode(s) are already set, this seems to work:

Turn on (for session only):

SET @@sql_mode = CONCAT(@@sql_mode, ',', 'ONLY_FULL_GROUP_BY');

Turn off (for session only):

SET @@sql_mode =
        REPLACE(
        REPLACE(
        REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY,', ''),
                           ',ONLY_FULL_GROUP_BY', ''),
                            'ONLY_FULL_GROUP_BY', '');

Note carefully the commas.

Related Question