2

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?

1 Answer 1

5
  • 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.

7
  • Thank you very much. Could you explain one more piece. sql_mode seems to be switches that can be turned on and off. If the sql mode is "" does that turn off all the switches? I just want to make sure if mysql gets upgraded that other modes aren't added if my mode is "" Aug 31, 2015 at 19:41
  • Added more code.
    – Rick James
    Sep 1, 2015 at 15:26
  • I am having a hard time explaining the question. What I am asking is if I change the mode to "" and mysql 5.8 comes along will the new options for mode be turned on automatically? Sep 1, 2015 at 15:49
  • Turn on (for session only): SET @@sql_mode = CONCAT(@@sql_mode, ',', 'ONLY_FULL_GROUP_BY');
    – Rick James
    Sep 2, 2015 at 5:46
  • What I am trying to ask is if mode is "" does that mean all the options are off? Sep 2, 2015 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.