一、 问题描述
开发反馈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