MySQL - 出现 1140 错误...这与 sql_mode=only_full_group_by 不兼容，但已禁用 [英] MySQL - Getting 1140 error ... this is incompatible with sql_mode=only_full_group_by, but its disabled

问题描述

我对此感到有些困惑.我以前使用过 mysql 5.7x，我总是能够通过从 mysql 配置中的 sql_modes 中删除 ONLY_GROUP_BY 来解决这个问题.但是，今天我似乎无法这样做，即使从 sql_modes 设置中删除它也不阻止我收到此错误.

I'm a bit stumped by this. I've used mysql 5.7x before and I've always been able to fix this issue by removing ONLY_GROUP_BY from the sql_modes in the mysql config. However, today I appear to be unable to do so, even removing it from the sql_modes setting doesn't stop me from recieving this error.

我知道导致错误的原因，并且我知道您可以将解决方法添加到 SQL 中，但是我没有时间修复我们应用程序中导致此错误的数百个查询.

I know what causes the error and I know there is a work around that you can add to the SQL, however I do not have time to fix the litteral hundreds of queries in our application which cause this.

mysql.cnf中的SQLMode设置:

SQLMode setting in mysql.cnf:

sql_mode = " ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后通过运行查询来确认这一点:

This is then confirmed by running the query:

SELECT @@SQL_MODE;

结果:

'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

但是，在运行存储过程时，它仍然会产生错误:

However, when running the stored proc, it still produces the error:

SQLSTATE[42000]:语法错误或访问冲突:1140 在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #1 包含非聚合列schema.emp.company_id"；这与 sql_mode=only_full_group_by 不兼容

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'schema.emp.company_id'; this is incompatible with sql_mode=only_full_group_by

有谁知道为什么 mySQL 似乎没有识别出该选项已被禁用?

Does anyone know why mySQL appears not to recognise that the option has been disabled?

我正在运行 5.7.21-0ubuntu0.17.10.1(在 Ubuntu 17.10 上，毫不奇怪！)

I'm running 5.7.21-0ubuntu0.17.10.1 (on Ubuntu 17.10, not surprisingly!)

推荐答案

好吧，只是为了使整个问题的奇异性加倍，看来如果您删除并重新创建存储的 proc(没有代码更改，它正是相同)，问题就迎刃而解了.

Well, just to double the bizarre-ness of the entire issue, it appears that if you drop and recreate the stored proc (with no code changes, its exactly the same), the issue goes away.

此架构不是现有架构，并且 mySQL 对其进行了升级，几周前我在我的机器上全新安装了 Ubuntu 17.10，并将备份中的所有内容都恢复到它上面.

This schema wasn't an existing one and mySQL upgraded over it, I did a fresh install of Ubuntu 17.10 on my machine only a few weeks ago and restored everything from backups onto it.

我无法解释上述内容，我只能假设 mySQL 在编译(?)存储过程时缓存 sql 模式.我不知道当你创建它们时它是否会编译，但它显然在缓存一些东西.

I'm at a loss to explain the above, I can only presume mySQL caches sql modes when it compiles(?) stored procs. I don't know if it does compile then when you create them, but it's clearly caching something.

这篇关于MySQL - 出现 1140 错误...这与 sql_mode=only_full_group_by 不兼容，但已禁用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！