mysql - 十月 CMS - SQL 请求与 sql_mode=only_full_group_by 不兼容

Question

我在本地环境中遇到一个奇怪的问题:wamp64MySQL 5.7.14十月CMS 382

这在几个插件中都遇到过，例如这个试图在后端显示幻灯片列表的插件

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dbname.flosch_slideshow_slides.sort_order' which is not functionally dependent on columns in GROUP BY clause;

this is incompatible with sql_mode=only_full_group_by

(SQL:

select flosch_slideshow_slides.slideshow_id, count(*) as count

from flosch_slideshow_slides

where flosch_slideshow_slides.slideshow_id in (2, 1)

group by flosch_slideshow_slides.slideshow_id

order by sort_order asc

)

其他例子略有不同。这个尝试在前端显示产品列表

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #51 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.jiri_jkshop_products_categories.category_id' which is not functionally dependent on columns in GROUP BY clause;

this is incompatible with sql_mode=only_full_group_by

(SQL:

select * from jiri_jkshop_products inner join jiri_jkshop_products_categories on id = jiri_jkshop_products_categories.product_id where active = 1 and category_id in (8) and visibility = 1 group by id order by title asc

)

这些请求在这里触发:

后端\行为\ListController->listRender()

所以我没有手

我指定它在远程服务器和我伙伴的本地环境中都能完美运行

另一个有趣的地方是，由于我应用了系统的最新更新(从版本 365 到 382)并且安装了所有插件。然后，为了测试，我在远程服务器上做了同样的事情，它仍然有效

因为我主要在本地工作，所以这很烦人

你有什么想法来解决它吗？理想情况下不修改 mysql/sql_mode

感谢和问候，帕特里克

Answer 1

很长一段时间以来，MySQL 都包含一个臭名昭著的 GROUP BY 非标准扩展，它允许以效率的名义进行古怪的行为。此扩展使全局无数开发人员能够在生产代码中使用 GROUP BY，而无需了解他们在做什么。

特别是，在 GROUP BY 查询中使用 SELECT * 是愚蠢的，因为标准的 GROUP BY 子句需要枚举列。很多开发人员都曾这样犯傻。

读这个。 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

MySQL 团队一直在尝试在不破坏生产代码的情况下修复此错误功能。他们在 5.7.5 中添加了一个名为 ONLY_FULL_GROUP_BY 的 sql_mode 标志强制标准行为。在最近的版本中，他们默认打开该标志。当您将本地 MySQL 升级到 5.7.14 时，该标志被打开并且您的生产代码(依赖于旧扩展)停止工作。

你的选择？

1. 修复有问题的 SQL 查询，或让插件作者这样做。
2. 回滚到与您使用的应用软件兼容的开箱即用的 MySQL 版本。
3. 更改服务器的sql_mode摆脱新设置的 ONLY_FULL_GROUP_BY 模式。

您可以通过执行 SET 命令来更改模式。

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

如果您在应用程序连接到 MySQL 后立即执行此操作，应该可以做到这一点。

或者，您可以找到 the init file in your MySQL installation ，找到 sql_mode= 行，将其更改为省略 ONLY_FULL_GROUP_BY，然后重新启动服务器。

第一个有问题的查询？将 sort_order 添加到 GROUP BY 子句，如下所示。

select flosch_slideshow_slides.slideshow_id, count(*) as count
  from flosch_slideshow_slides
 where flosch_slideshow_slides.slideshow_id in (2, 1)
 group by flosch_slideshow_slides.slideshow_id, sort_order
 order by sort_order asc

这会破坏什么吗？可能不是。

您的第二个违规查询？它包含 SELECT *，因此在修复 GROUP BY 子句之前，您必须分析查询周围的代码以查明实际使用了哪些列。如果这是一个插件，那么这项工作最好由其作者完成。