存储过程引发“与sql_mode = only_full_group_by不兼容"；尽管sql_mode为空 [英] Stored procedure raising "incompatible with sql_mode=only_full_group_by" despite sql_mode being blank

问题描述

我有一个在MySQL 5.6上运行良好的存储过程.在最近的服务器迁移中，我们升级到了MySQL 5.7.19.

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))

我已经通过/var/mysql/my.cnf文件将sql_mode设置为""，重新启动了mysql服务，并通过控制台登录以确认sql_mode为空白，通过SELECT @@sql_mode;

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?

推荐答案

根据文档，MySQL使用创建该过程时处于活动状态的sql模式:

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

MySQL存储创建或更改例程时有效的sql_mode系统变量设置，并且始终在执行此设置的情况下执行该例程，而与例程开始执行时当前的服务器SQL模式无关.

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.

因此，在激活另一种模式(或修复group by语法)的情况下，重新创建该过程(或所有过程，因为它可能不是唯一受影响的过程).尽管在文档中提到了更改的过程，但这仍然不够.您应该考虑不要为此永久更改sql模式(尽管您可能还会有其他不兼容的代码).

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).

这篇关于存储过程引发“与sql_mode = only_full_group_by不兼容"；尽管sql_mode为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！