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.

Read More:

• [Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
• [Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP
• [MySQL] The principle of group by aggregation function and the reason for aggregation limitation SELECT list is not in GROUP BY clause and contains nonaggregated column
• [Solved] MySQL: Syntax error or access violation: 1055 Expression #1 of SELECT list is not …
• MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by
• [Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by
• How to Solve MySQL version 5.7+ Group by group error
• [Solved] MYSQL Start Project Error: this is incompatible with sql_mode=only_full_group_by
• postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a
• [Solved] MySQL Error: ERROR 1055 (42000)sql_mode=only_full_group_by
• View’s select contains a subquery in the from clause bug
• MySQL OrderBy Error: Expression #1 of ORDER BY
• mysql workbench Error Code: 1046. No database selected Select the default DB to be used by doubl
• [Solved] Error response from daemon: Conflict. The container name “/mysql is already in use by container
• SQL Server Group sort de duplication row_ number() over ( PARTITION BY t1.col_ 2 ORDER BY 1 )
• Mysql :error 1111. Invalid use of group function
• [Solved] MySQL5.6.44 [Err] 1067 – Invalid default value for create_date settlement programme
• MYSQL Create TIMESTAMP and Save Error: ERROR 1067 (42000): Invalid default value for ‘last_updated_on’
• [Two Methods] Ora-00904: invalid group by error identifier
• How to Solve mysql [Err] 1067-Invalid default value for