MySQL exception error 1055 (42000) description

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 was incompatible with sql_mode=only_full_group_by

Execute such an 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, reported abnormal ( specific from 5.7 which version has not been determined, online someone said 5.7.5)

MySQL has adjusted the default parameter Sql_mode value from a version of 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>

Where only_full_group_by is the effect of the above SQL statement is not reported exception

Workaround:

1, rewrite the SQL statement, after all, the above writing is not a standard SQL statement

2. Log in to MySQL server and execute the following commands, both at 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 2nd method, in the My.cnf file, specify the value of Sql_mode:

[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: The combination of 2 and 3 does not restart the server and is still valid even if the restart configuration is in effect.

MySQL exception error 1055 (42000) description