Stored procedure raising "incompatible with sql_mode=only_full_group_by" despite sql_mode being blank

Question

I have a stored procedure that ran fine on MySQL 5.6. During a recent server migration we upgraded to MySQL 5.7.19.

My stored procedure now throws the error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'utility-monitor.daily_readings.building_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: CALL monthly_readings(2017, 1, NULL, 1, 1))

I've set the sql_mode to "" via the /var/mysql/my.cnf file, restarted the mysql service and logged in via console to confirm that sql_mode is blank via SELECT @@sql_mode;

Despite all that, I continue to receive the above error when I try to run my stored procedure.

What can I do next to continue troubleshooting where this error is coming from?

Casimir

ONLY_FULL_GROUP_BY is the default in MySQL 5.7. – Portauprince

https://mcmap.net/q/28657/-disable-only_full_group_by check this – Burney

Answer 1

According to the documentation, MySQL uses the sql mode that was active when you created the procedure:

MySQL stores the sql_mode system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing.

So recreate the procedure (or all, as it might not be the only one affected) with a different mode activated (or fix the group by syntax). Altering the procedure, though mentioned in the documentation, does not suffice. You should consider not to change the sql mode permanently for that (although you might have other incompatible code too).

Dipsomaniac

This is very helpful, thanks Solarflare. The MySql guys designed it to help with working objects imported from another db, at first it is really convenient, but this is a very painful process to debug problems when you need to make changes to that object in its new schema. – Roslynrosmarin