SQL_MODE not working on MySQL 5.7.18

Question

I'm getting the following error from my PHP application:

Incorrect SQL query (Database) In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db_20172201.cm_order.buyer'; this is incompatible with sql_mode=only_full_group_by

After looking around for a while, and after trying to identify the query that gave this error I decided that I should change the SQL_MODE option in my MySQL server.

I did 2 things to accomplish this:

• executed this query

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

• Added the following in my.cnf which is the configuration file for MySQL

  [mysqld]
  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 thought that these to would be enough but the error never goes away from my application. When I check for the value of SQL_MODE in my environment by this query

SELECT @@GLOBAL.sql_mode

it shows the values inserted above.

I don't know where else to look. I also tried to set the sql_mode variable from my application before executing a query but doesn't solve.

--- UPDATE

I just printed in my application the result of the query SELECT @@SESSION.SQL_MODE and it turns out to be as I set it:

array(1) { [0]=> object(stdClass)#5 (1) 
    { 
        ["@@SESSION.sql_mode"] => string(118) "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    }
}

But I still get the error

PS. MySQL version is 5.7.18

Answer 1

Stored programs -- procedures, functions, triggers, and events -- have some special mechanisms that many users never notice, because they usually "just work."

When a stored program is created, certain aspects of the environment are captured and stored along with the code, and used each time the program is invoked.

SHOW CREATE PROCEDURE ... shows you these values.

One of them is @@SQL_MODE.

This allows the definer of the procedure to ensure that the environment in place when the procedure is running always has these attributes.

The official answer, then, would be to drop and recreate the procedures with the correct environment in place.

But... the attributes here are actually stored in the mysql.proc table. So you have a potential "quick fix" here, as long as you keep the following in mind:

The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table.

https://dev.mysql.com/doc/refman/5.7/en/stored-routines-privileges.html

If this system is not critical and you have backups, you can carefully manipulate the mysql.proc table rows to change the sql_mode, then restart the MySQL server process so that changes to the table are taken into account.

Note that another seemingly viable option -- making a dump file with mysqldump and reloading it -- will have no effect, since mysqldump adds statements to the dump file to ensure that the procedures will be restored with the same settings that existed on the procedures when the dump file was created -- so changes to global server config will have no effect.

You could of course modify the dump files to change these values, but this is a somewhat advanced operation.

If you loaded this schema from a file that contained SQL statements but that did not set sql_mode explicitly, then reloading the file should also have the desired effect.

Finally, note that ONLY_FULL_GROUP_BY is trying to protect you from queries that use the MySQL group by extensions in a way that isn't consistent with that feature's purpose. Starting in 5.7.x, a query that is rejected by ONLY_FULL_GROUP_BY may in fact be producing non-deterministic results or may be taking advantage of side effects or results that are correct by accident or coincidence -- rather than by design -- and such behavior could disappear in a future release (for example, due to changes in the query optimizer) and thus shouldn't be relied on.

The best solution is to make the queries theoretically correct.