0

I have ran the following sql statement on my localhost numerous times:

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

per this post:

Disable ONLY_FULL_GROUP_BY

with results:

10:58:22    SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'only_full_group_by',''))    0 row(s) affected   0.00026 sec

which does not update any records.

And I continue to encounter:

[42000][1055] Expression #9 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

And checking server variables in Workbench shows no change:

enter image description here

I have restarted MySQL service after some executions - it does not make any difference if current session or current session reset.

So, how do I update only this setting?

MySQL v5.7.34

Following the suggestion from Akina and executing:

select @@sql_mode mode_before_replacing;
SET @@sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
select @@sql_mode mode_after_replacing;

Response:

09:50:15    select @@sql_mode mode_before_replacing LIMIT 0, 50000  1 row(s) returned   0.00025 sec / 0.000032 sec

09:50:15    SET @@sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')  0 row(s) affected   0.00024 sec

09:50:15    select @@sql_mode mode_after_replacing LIMIT 0, 50000   1 row(s) returned   0.00012 sec / 0.000010 sec

Error persists.

9
  • I recommend you don't disable the ONLY_FULL_GROU_BY mode. It's correct to enforce this. You should fix the logic of your SQL query instead. Jan 26, 2023 at 17:57
  • Instead of disabling, let's discuss the query and why/how to avoid the error message. Please provide at least the SELECT.
    – Rick James
    Jan 26, 2023 at 23:35
  • SELECT not needed. SET @@sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');. But the situation when you need to adjust GLOBAL setting is too rare, so I think that SESSION variable altering is enough. If you need to alter GLOBAL setting definitely then edit options file and restart sevice.
    – Akina
    Jan 27, 2023 at 5:56
  • @BillKarwin and others - Yes, this is highly improper, while the query should be altered to avoid this exception, I need to diagnose something before altering the query itself. So, my need to disable (at least temporary) still exists. Thank you for you guidance. Jan 27, 2023 at 16:15
  • @Akina - Not sure about omitting the select - that setting contains more than just the group by setting (as illustrated in the screenshot provided in post), and I will need to understand what removing the select means in that context before executing. Jan 27, 2023 at 16:17

1 Answer 1

2

When you use SET GLOBAL, it doesn't change the current session's variables. Session variables are copied from global variables when you connect, and in most cases they don't heed changes to global variables after that (with a few exceptions, for example read_only).

When you restart the MySQL Server, that resets any custom changes you had made to global variables. Global variables are taken from your persisted options file (typically my.cnf).

So there are three possible solutions:

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

This sets the current session variable, not the global variable. Then you can test. The change will only affect the current session, and when that session disconnects, the setting will go with it.

SET GLOBAL sql_mode=...

Then reconnect, starting a new session. That will copy the new value of the global variable.

SET PERSIST sql_mode=...

This is a new feature in MySQL 8.0, so you'd have to upgrade. It acts like SET GLOBAL (i.e. does not change the current session), but it saves the value in a file so if you restart MySQL Server, it still remembers that you changed it. Read https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html for details.

2
  • Testing the session theory - it does not make any difference if current session or current session reset. It still gives me the error. It just seems like the statement does nothing - always 0 rows affected. Jan 27, 2023 at 19:57
  • That's correct; changing variables doesn't affect any rows. Jan 27, 2023 at 20:50

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.