From the development of a project to the test and online operation, the team’s immature management of the project will affect the development efficiency of the project. Since I just took over the project and set up the PHP environment in Centos alone, I considered using the higher version, choosing Mysql5.7, the local development environment or the Windows inheritance environment, the PHP environment and Centos are unified 7.0, but the Mysql version is 5.3 , The local development went well, and some problems appeared under Centos, which was caused by the inconsistency between the development environment and the test|online environment.

mysql command groop by error this is incompatible with sql_mode=only_full_group_by

Take a look at the meaning of ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal, because the column is not in the GROUP BY clause, that is to say, the column found It must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

Next, let’s just talk about the process of solving the problem.

First enter the Mysql client to execute the command

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 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+

See the first paragraph of the above repentant command line —> ONLY_FULL_GROUP_BY;

There are about three solutions given on the Internet. One is to add aggregate functions to the query condition column in the statement of the program code. If you do this, it will be very troublesome, and you can use group by in the development process. You have to work hard on the sentence, and this method is fine.

It is recommended to turn off this configuration item directly.

So how to turn off this item, can it be turned off directly by executing the command on the client side, set @@global.sql_mode = ……., the execution is successful, and then execute the command to check it out. One item is gone, and then we restart the mysql service again, query again, still… haha. .

So, let’s play a trick next, let’s start from the Mysql configuration file.

Find my.cnf, edit it~Put the following commands in the right place, restart the mysql service, OK~the error is correct~

Note: There is not extra set in front.

[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

Read More:

• MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by
• [Solved] MYSQL Start Project Error: this is incompatible with sql_mode=only_full_group_by
• [Solved] MySQL Error: ERROR 1055 (42000)sql_mode=only_full_group_by
• How to Solve MySQL version 5.7+ Group by group error
• [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 Error: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
• SQL Server Group sort de duplication row_ number() over ( PARTITION BY t1.col_ 2 ORDER BY 1 )
• [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
• [Two Methods] Ora-00904: invalid group by error identifier
• postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a
• Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 30005ms.
• MySQL OrderBy Error: Expression #1 of ORDER BY
• [Solved] Error response from daemon: Conflict. The container name “/mysql is already in use by container
• java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space
• SQL server converts multiple lines into one line, separated by characters
• [Solved] Caused by: java.sql.SQLException: Access denied for user ‘root‘@‘hadoop102‘ (using password: YES)
• MySQL 8.0.12 runs SQL error #1055 [How to Solve]
• Mysql :error 1111. Invalid use of group function
• [Solved] MySQL Error: Client does not support authentication protocol requested by server