Navicat执行sql报错 [Err] 1055 - Expression # 1 of ORDER BY clause is not in GROUP BY

一、 问题描述

 

开发反馈Navicat无论执行什么语句都会有以下报错，不过实际能执行成功。

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、 问题分析

打开 general_log，在Navicat执行语句，然后关闭

Show Variables like '% Gen%';
SET Global general_log =. 1;
Update XXXXX SET XXXX; -- navicat中执行
SET Global general_log = 0;
Show Variables like '% Gen%';

会发现以下结果：

2020-01-08T15:20:47.934694+08:00 2605922 Query SHOW GLOBAL STATUS
2020-01-08T15:20:48.837771+08:00 2608143 Query SET PROFILING=1
2020-01-08T15:20:48.840037+08:00 2608143 Query SHOW STATUS
2020-01-08T15:20:48.845610+08:00 2608143 Query SHOW STATUS
2020-01-08T15:20:48.853304+08:00 2608143 Query UPDATE shx_xxx.user_xxx SET roxx_id='xxxxxxxxxxxxxxxxxx0001' WHERE usxx_id='xxxxxxxxxxxxxxxxxx35';
2020-01-08T15:20:48.857803+08:00 2608143 Query SHOW STATUS
2020-01-08T15:20:48.863344+08:00 2608143 Query SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
2020-01-08T15:20:48.867054+08:00 2608143 Query SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.000846*100,3), '%') AS `百分比`
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=39 GROUP BY STATE ORDER BY SEQ

会执行 SET PROFILING=1 是因为navicat默认自动把profiling功能打开了。

三、 问题原因

navicat默认自动开启profiling，为了展示profiling内容，在执行sql后，低版本navicat会执行以下语句做一个统计（如果用中文版Navicat看到的字段名就是以下三个，英文版就如上图）：

SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.000846*100,3), '%') AS `百分比`
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=39 
GROUP BY STATE 
ORDER BY SEQ

这个语句是不符合sql 92规范的，only_full_group_by选项要求：order by的内容，应该是group by中其中一项的内容。比如：

• group by column_a,column_b order by column_a 是正确的。
• group by column_a,column_b order by column_c 是错误的。

5.5的时候，如果不符合sql 92规范的语句，是不会报错的；5.6之后，sql_mode 中默认包含了 only_full_group_by，所以不符合规范的就报错出来了。

高版本的navicat就不会有这个报错了，估计就是修复了做profiling统计的内置sql语句，做到了order by 和group by 一致。

四、 问题处理

主要有以下方法：

• 置之不理。该报错实际不影响sql执行，只是一个提示，可以忽略
• 升级Navicat版本，例如使用 Navicat 12.1.8
• 使用其他客户端工具，例如 datagrip、workbench 等等
• 在执行sql前手动执行  SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

参考

https://www.codetd.com/en/article/8461455