1, Error reporting page

2, Error reporting reason

This error occurs in MySQL version 5.7 and above:
The default sql configuration for MySQL version 5.7 is sql_mode = "ONLY_FULL_GROUP_BY", this configuration strictly implements the "SQL92 standard".
When upgrading from 5.6 to 5.7, most of them choose to adjust SQL for syntax compatibility_ Mode to keep it consistent with 5.6 in order to be as compatible with the program as possible.
This reason occurs during sql execution:
Simply put, the output result is called target list, which is the field followed by select. There is also a place called group by column
Group by followed by the field. Because only is turned on_ FULL_ GROUP_ For the by setting, if a field does not appear in the target list and group by fields at the same time, or the value of the aggregate function, the sql query is considered illegal by mysql and an error will be reported.

3, Solution

1. View current database version

You can also enter a command query

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

2. View sql_mode

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_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can see that only is enabled by default_ full_ group_ By mode, but when this mode is enabled, the original group by statement will report an error.

3. Close only_full_group_by mode

mysql> set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

The above is the change of global sql_mode, which is valid for the newly created database. For an existing database, it needs to be executed under the corresponding database.

mysql> use dirak;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

Remember to reload the mysql permission table

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)

Run the project again and find that the problem has been solved.

3, Problems needing attention and thorough solutions

The above method only needs to change the configuration file temporarily. After restarting the mysql database service, ONLY_FULL_GROUP_BY will also appear.

To permanently solve the problem, you need to modify the mysql configuration file and add SQL manually_ Mode is mandatory. Only is not required_ FULL_ GROUP_ By property.

my.cnf is located in the etc folder. Move the cursor under vim to the end and add the following configuration (as above, query your own database sql_mode and remove ONLY_FULL_GROUP_BY):

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION