0

@@SESSION.sql_mode and @@GLOBAL.sql_mode is showing different options.

@@SESSION.sql_mode

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@GLOBAL.sql_mode

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

It seems like GLOBAL.sql_mode is taking precedence, is that right?

Why wouldn't a session setting overwrite a global setting?

Query ran:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

Result:

enter image description here

5
  • It is extremely strange. Session variable value must override global one. Initially global values are copied into session ones, and the last are used. While overriding new session values uses. I have never seen global settings priority... can you demonstrate this by some way? Show console output, for example...
    – Akina
    Sep 3, 2019 at 6:37
  • Which version of MySQL? (There were changes. at least in 5.7.5.)
    – Rick James
    Sep 3, 2019 at 14:57
  • The first thing you do is to enable ONLY_FULL_GROUP_BY - otherwise you will get inconsistent (i.e. wrong) results using GROUP BY. Do you have any settings in your my.cnf (or my.ini) that might change these settings?
    – Vérace
    Sep 4, 2019 at 21:51
  • @RickJames it's v5.6.26
    – hackerl33t
    Sep 9, 2019 at 8:58
  • Is the @@SESSION.sql_mode pertaining to my client that I use to run the query? Does that mean I might get a different value if I run the same query from my application? I mean, there's my app, and then there's my DB client that I use to run queries etc. Are the sessions different?
    – hackerl33t
    Sep 9, 2019 at 9:14

1 Answer 1

0

"Session" variables apply to the connection. (Usually, there is exactly one connection per client.) Two different clients (or different connections in one client) may have different values in the variables.

"Global" variables are defaults that are copied into "session" when you connect. Then you, or your client code, or the 3rd party application, or whatever, could change the variables.

Note: Some global variables are really global -- the "session" value always tracks the global copy.

If you display the session value from the same connection, then that is what the connection is using. If you make a separate connection to read the value, then what you see may not be what is in effect.

There are hundreds of 3rd party packages out there; who knows what each of them does. Furthermore, things change from version to version of that package.

sql_mode can be changed in a session.

In your example, something in the connection added STRICT_TRANS_TABLES to SQL_MODE. And it should be in effect for the connection until it is changed again.

Your Answer

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

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