SET GLOBAL sql_mode no longer works, and setting doesn't exist in my.cnf

Question

I am running MySQL 14.14 Distrib 5.7.40 for Linux (x86_64) on my BlueHost VPS. A few months ago I could run this command successfully:

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

I have to go rerun this every few months to avoid this error:

...this is incompatible with sql_mode=only_full_group_by

Today when I ran that it appeared successful, but the SQL errors remain in my web app:

Invalid query: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'MyDatabase.s.EventDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

My full query that is breaking:

SELECT g.GroupID,
       g.GroupName,
       count(s.EventDate) AS Total
FROM Groups g
LEFT JOIN Schedule s ON g.GroupID = s.GroupID
JOIN Settings se ON g.GroupID = se.GroupID
WHERE g.OrganizationID = 479
AND g.IsActive = 1
AND IFNULL(g.IsDeleted, 0) = 0
AND IFNULL(g.IsHidden, 0) = 0
AND se.SettingName = 'HideGroupNoGames'
AND (s.EventDate > DATE_ADD(NOW(), INTERVAL 0 HOUR)
    OR g.CreateDate > DATE_ADD(DATE_ADD(NOW(), INTERVAL -1 DAY), INTERVAL 0 HOUR)
    OR se.SettingValue = 'False')
GROUP BY g.GroupID, g.GroupName
ORDER BY s.EventDate, g.GroupName

And when I run SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));, the error doesn't go away.

I tried stopping and starting the mysqld service from terminal. Success, but SQL error remains.

When I run this: select @@sql_mode;, it still displays this:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Even though I already ran this:

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

...and this:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;

I would love to fix the SQL statement so the error goes away, but I don't know how. Whenever I tweak it I get different and incorrect results.

I learned from this SO post that SET GLOBAL is temporary and will reset when mysql restarts. But when I run cat my.cnf, I don't even see the sql_mode:

[mysqld]
performance-schema=0

default-storage-engine=MyISAM
interactive_timeout=300
key_cache_block_size=4096
max_heap_table_size=32M
max_join_size=1000000000
max_allowed_packet=268435456
open_files_limit=40000
query_cache_size=32M
thread_cache_size=100
tmp_table_size=32M
wait_timeout=7800
max_user_connections=50
myisam_recover_options=FORCE
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_purge_threads=1
innodb_support_xa=0
innodb_thread_concurrency=8
pid-file=/var/lib/mysql/mysqld.pid
innodb_buffer_pool_size=55574528

I think perhaps any of the following solutions would work for me:

• Why doesn't SET GLOBAL sql_mode work any more, and how can I make it work?
• Why doesn't my.cnf contain anything about sql_mode?
• How can I fix my query so the SQL mode doesn't matter, and doesn't error?

Answer 1

Okay, a couple of things are going on.

• Using SET GLOBAL doesn't affect the current session in which you ran that statement. It changes the global option, but the current session still uses session values copied from the global values when the session starts. If you start a new session after you use SET GLOBAL, it will inherit the change.

• You can change the current session's configuration with SET SESSION sql_mode=... or simply SET sql_mode=... which is equivalent.

• Restarting mysqld discards any changes you made with SET GLOBAL, and reverts to the options in my.cnf (or the default value compiled-in to the mysqld executable, if the option is not specified in my.cnf). Simply changing the global option is not written to my.cnf.

  In MySQL 8.0, you can use SET PERSIST to change the global value and write it to a file so it remains in effect if you restart mysqld. Read about this in more detail: https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

Both of these things have been the way it worked for years. It's likely that it's working exactly as documented.

It's recommended that you should NOT change the sql mode. Your query can be changed to comply with the default sql mode.

Since EventDate is not a column in your GROUP BY clause, it may have multiple values per group. So it's undefined what happens if you ORDER BY s.EventDate, .... Which value in the group should it use to sort?

You should resolve this by sorting by a specific value in the group. For example, these would be acceptable choices:

ORDER BY MAX(s.EventDate), g.GroupName

ORDER BY MIN(s.EventDate), g.GroupName