Shulou(Shulou.com)06/01 Report--

The specific exception information is as follows:

ERROR 1055 (42000): Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aaa.test.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Execute such a SQL statement to reproduce the exception select age, name from test group by name

Mysql5.1, no exception reported

Mysql5.5, no exception reported

Mysql5.6, no exception reported

Mysql5.7.22, report an exception (which version has not been determined yet since 5.7, some people on the Internet say 5.7.5)

Mysql has adjusted the default parameter sql_ mode since version 5.7 to:

Mysql > show variables like 'sql_mode' +- -- + | Variable_name | Value | +- -- +- -+ | sql_mode | ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | +-+- -- + 1 row in set (0.00 sec) mysql >

Among them, ONLY_FULL_GROUP_BY affects whether the above SQL statement reports an exception.

Solution:

1. Rewrite the SQL statement. After all, the above is not a standard SQL statement.

2. Log in to MySQL server, execute the following command, and modify it at both global and session level

Mysql > set global 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 > set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

3. Combined with the second method, specify the value of sql_mode in the my.cnf file:

[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: if you combine 2 with 3, you don't have to restart the server, and even the restart configuration is still valid.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.