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 aboutsql_mode
? - How can I fix my query so the SQL mode doesn’t matter, and doesn’t error?
Advertisement
Answer
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 useSET GLOBAL
, it will inherit the change.You can change the current session’s configuration with
SET SESSION sql_mode=...
or simplySET sql_mode=...
which is equivalent.Restarting mysqld discards any changes you made with
SET GLOBAL
, and reverts to the options inmy.cnf
(or the default value compiled-in to the mysqld executable, if the option is not specified inmy.cnf
). Simply changing the global option is not written tomy.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