Project scene:

Recently, after deploying the project, an error occurred when running:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.order_id’ 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

Problem Description

Using the GROUP BY statement violates sql_mode=only_full_group_by. Because the default mode after mysql version 5.7 is ONLY_FULL_GROUP_BY.

Cause Analysis:

Check the official documentation and find that starting from MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL did not detect functional dependencies, and ONLY_FULL_GROUP_BY was not enabled by default.) This may cause some sql statements to fail.

solution:

Execute the command vim /etc/mysql/conf.d/mysql.cnfto modify the configuration file

If there is sql_mode configuration in my.cnf, remove ONLY_FULL_GROUP_BY.

If not, put the following content in the corresponding place

[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

After saving, execute the command to service mysql restartrestart mysql.