Navicate报错[Err] 1055

问题描述

使用Navicate运行完insert语句，最后报错[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

解决思路

1、查看错误信息，提示报错only_full_group_by，这个报错在执行查询时会出现；

2、查看sql_mode（一组语法校验规则）对应的值；

• 命令：select @@GLOBAL.sql_mode或者select @@SESSION.sql_mode；
• 对应值：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

• ONLY_FULL_GROUP_BY为MySQL 5.7.4版本的新增的参数：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中；

3、查看Mysql日志，在通用查询日志文件中，发现在新增的时候有查询INFORMATION_SCHEMA.PROFILING的操作;

• 查看通用查询日志状态，show variables like ‘%gen%’；
  
• 启用通用查询日志，set global general_log=ON;默认情况下，除了 Windows 上的错误日志外，不启用任何日志；
• 启用后运行sql文件，再次查看通用日志文件hostname.log，可以看到有查询INFORMATION_SCHEMA.PROFILING的操作；

SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;

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

• 这个查询不符合ONLY_FULL_GROUP_BY参数限制，所以才会出现报错；
• 定位出问题后，关闭通用查询日志，否则产生大量的日志信息；set global general_log=OFF；

解决方法：关闭only_full_group_by规则；

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

延申

• Mysql日志类型

日志类型	日志信息
错误日志	left-aligned 启动、运行或停止mysqld时遇到的问题
通用查询日志	Established client connections and statements received from clients
二进制日志	Statements that change data (also used for replication)
中继日志	Data changes received from a replication source server
慢查询日志	Queries that took more than long_query_time seconds to execute
DDL日志 (元数据日志)	Metadata operations performed by DDL statements

• information_schema 数据库是 MySQL 自带的信息数据库。用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。
• INFORMATION_SCHEMA.PROFILING表提供了语句分析信息，可以用来分析每一条SQL在它执行的各个阶段的用时。