Shulou(Shulou.com)05/31 Report--

This article mainly introduces the Mysql5.7 and above version of ONLY_FULL_GROUP_BY error report how to do, the article is very detailed, has a certain reference value, interested friends must read it!

In the recent development process, because the mysql database connected to the project development environment is Aliyun's database, while Aliyun's database version is 5.6. The mysql of the test environment is installed by yourself. Therefore, in the development process, there are small partners who do not pay attention to writing sql statements about group by. It is normal to run in the development environment, but exceptions are found in the test environment.

Cause analysis: the mysql sql_mode = only_full_group_by attribute is set by default in the MySQL5.7 version, resulting in an error.

ONLY_FULL_GROUP_BY is the chief culprit for this error. For group by aggregation operations, if the column in select does not appear in group by, then the SQL is illegal. Because the column is not in the group by clause, the database with sql_mode=only_full_group_by is set up and will report an error when using group by.

The test environment downloads and installs the latest version of mysql5.7.x. Only_full_group_by mode is turned on by default, but when this mode is turned on, the original group by statement reports an error and then removes it.

Once only_full_group_by is enabled, it feels that group by will become the same as distinct, which can only obtain the information of the fields affected by it and cannot coexist with other fields that are not affected by it. In this way, the function of group by will become very narrow.

It is better to turn on only_full_group_by mode. Because there is a function in mysql: any_value (field) allows the presence of non-grouped fields (which has the same effect as turning off only_full_group_by mode).

1. View sql_mode

SELECT @ @ sql_mode

The values queried are:

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

2. Remove the ONLY_FULL_GROUP_BY and reset the value.

SET @ @ global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

3. The above changes the global sql_mode, which is valid for the newly built database. For existing databases, it needs to be executed under the corresponding data

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'

The above methods are still invalid after mysql database restart, and the following methods are still valid after restart.

Find the MySQL configuration file, check the / etc/my.cnf file on the linux system, and query the sql_mode field. I didn't find this keyword in the configuration file, so I added it manually:

Sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

One thing to note is that it must be added to the [mysqld] configuration, so that the restart mysql will not take effect until it is added. Exit the database: exit, restart the command:

Service mysqld restart

The error message of the refresh page disappears successfully. Connect to the database again to view the sql_mode configuration select @ @ sql_mode:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION above is all the contents of this article entitled "how to report errors in Mysql5.7 and above ONLY_FULL_GROUP_BY". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.