MySQL 5.7.9 Version sql_mode=only_full_group_by issue

MySQL 5.7 Version sql_mode=only_full_group_by issue

1, in the MySQL environment to execute packet SQL, as follows

Mysql> Select Db_server_name,login_user,count (db_server_name) from the ' Mysql_audit_log ' Group by Login_user;

Tips

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP by clause and contains nonaggregated column ' COLLECT_MYSQ L_audit_log.mysql_audit_log.db_server_name ' which is not functionally dependent on columns in GROUP by clause; This was incompatible with sql_mode=only_full_group_by

2, Solve:
Executes select @ @GLOBAL. Sql_mode View

Mysql> SELECT @ @GLOBAL. sql_mode;+---------------------------------------------------------------------------- ---------------------------------------------------------------+| @ @GLOBAL. 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.06 sec)
Reset Sql_mode, disable only_full_group_by. The following settings are temporary, and if you want to make them permanent, add the configuration to the configuration file

mysql> SET sql_mode = ' strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_ Create_user,no_engine_substitution '; Query OK, 0 rows Affected (0.00 sec)

Configuration file to add the configuration

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 5.7.9 Version sql_mode=only_full_group_by issue