MySQL: Two solutions for incompatibility with only_full_group_by mode

Summary: MySQL: Two solutions for incompatibility with only_full_group_by mode

【MySQL: solutions for incompatibility with only_full_group_by mode】1. Abnormal phenomenon


There is a problem with executing SQL after the database is migrated, and the error message is as follows
SELECT list is not in GROUP BY clause and contains nonaggregated column
'cloud.exam_train_user.user_id' which is not functionally
dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

It can be seen that it is caused by the setting of only_full_group_by mode in sql_mode
In this mode, when we use grouping query, the only grouping fields that appear after the select field can be the grouping fields behind group by , or the fields wrapped by the aggregation function.
Oracled do not support columns with ambiguous semantics in the select target list . Such statements will be reported incorrectly in these databases, so this semantics has been corrected since MySQL version 5.7, which is the so-called ONLY_FULL_GROUP_BY semantics.

【MySQL: solutions for incompatibility with only_full_group_by mode】MySQL official explanation




MySQL 5.7.5 and later versions enable ONLY_FULL_GROUP_BY SQL mode by default

【MySQL: solutions for incompatibility with only_full_group_by mode】Solution


Method 1: Modify sql_mode
You can find out sql_mode by selecting @@sql_mode, remove ONLY_FULL_GROUP_BY and copy it over
Query the sql_mode setting and find ONLY_FULL_GROUP_BY
mysql> select version(),@@sql_mode;
+------------+------------------------------------------------ ----------------------------+
| version() | @@sql_mode |
+------------+------------------------------------------------ ----------------------------+
| 5.7.29-log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------+------------------------------------------------ ----------------------------+
1 row in set (0.00 sec)

Modify sql_mode settings
Temporary modification
set sql_mode=' ' //Change the existing database sql_mode
set @@global.sql_mode=' ' //Change the global configuration sql_mode
The above configuration fails after restarting the service
Change configuration file (recommended)
•Linux system changes /etc/my.cnf file
•The configuration file under windows is the my.ini file in the installation directory
If there is a comment with sql_mode= ... open the comment, if not, add sql_mode=...
sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL5.7: my.cnf parameter quick check booklet
Method 2: select fields that are not group by
Directly modify and modify the sql_mode setting. In many cases, the developer does not have permission, and the DBA also needs to modify it carefully. Therefore, for the developer, we can optimize it at the SQL level.
From the above, we know that,
If only_full_group_by mode is set in sql_mode
In this mode, when we use grouping query, the only grouping fields that appear after the select field can be the grouping fields behind group by , or the fields wrapped by the aggregation function.
Therefore, in the query statement containing the Group by clause, the following rules exist for the target column after the select keyword
1.When using group by, the columns involved in the select are either the columns involved in the grouping, or the columns are included in the aggregate function
2.where will filter all data before grouping. having will do things to a group of data after grouping.
E.g:
select a,b,avg(c),sum(d) from table group by a,b

select fields that are not group by
•You can treat the result set of group by as a table , and then fetch numbers from this table.
•For example: query, order information with repeated order numbers in the order table, the grouping field is orderno, and the select information not only includes orderno, but also other information
SELECT
p.id,
p.orgcode,
p.idserial,
p.username,
p.orderno,
p.createtime,
p.payflag,
p.businessorderno,
p.paytime
FROM
pay_order_trade p,
(
SELECT
orderno,
count(*) number of repetitions
FROM
pay_order_trade
GROUP BY
orderno
HAVING
count(1) > 1
) s
WHERE
s.orderno = p.orderno
ORDER BY
p.orderno DESC;

MySQL 5.7: sql_mode
The schema affects the SQL syntax that MySQL supports and the data validation checks it performs.
This makes it easier to use MySQL in different environments and with other database servers.
Set SQL mode
The default SQL modes in MySQL 5.7 include the following modes: ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES , NO_ZERO_IN_DATE , NO_ZERO_DATE , ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER , and NO_ENGINE_SUBSTITUTION .
These modes were added to the default SQL mode in MySQL 5.7: The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO , NO_ZERO_DATE and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion of these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7 .
sql_mode parameter description
parameterillustrate
ONLY_FULL_GROUP_BYFor GROUP BY aggregation operations, if the column in the SELECT does not appear in the GROUP BY, then this SQL is not valid, because the column is not in the GROUP BY clause
NO_AUTO_VALUE_ON_ZEROThis value affects the insertion of self-growing columns. By default, inserting 0 or NULL generates the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.
STRICT_TRANS_TABLESIn this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, and there is no restriction on non-transactional tables.
NO_ZERO_IN_DATEIn strict mode, the day and month are not allowed to be zero
NO_ZERO_DATESet this value, the MySQL database does not allow zero dates to be inserted, inserting zero dates will throw an error instead of a warning.
ERROR_FOR_DIVISION_BY_ZERODuring an INSERT or UPDATE, if data is divided by zero, an error is generated instead of a warning. If this mode is not given, MySQL returns NULL when the data is divided by zero
NO_AUTO_CREATE_USERDisable GRANT from creating users with empty passwords
NO_ENGINE_SUBSTITUTIONThrows an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is used instead and an exception is thrown
PIPES_AS_CONCATTreat || as a string concatenation operator instead of an OR operator, which is the same as the Oracle database and similar to the string concatenation function Concat
ANSI_QUOTESWhen ANSI_QUOTES is enabled, double quotes cannot be used to quote a string as it is interpreted as an identifier
For more information refer to: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Reference link
https://dev.mysql.com/doc/refman/5.7/en/dynindex-sqlmode.html
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Copyright statement: The content of this article is contributed by Alibaba Cloud real-name registered users. The copyright belongs to the original author. The Alibaba Cloud developer community does not own the copyright and does not assume the corresponding legal responsibility. For specific rules, please refer to the " Alibaba Cloud Developer Community User Service Agreement " and " Alibaba Cloud Developer Community Intellectual Property Protection Guidelines ". If you find any content suspected of plagiarism in this community, fill out the infringement complaint form to report it. Once verified, this community will delete the allegedly infringing content immediately.

Related Articles

Explore More Special Offers

  1. Short Message Service(SMS) & Mail Service

    50,000 email package starts as low as USD 1.99, 120 short messages start at only USD 1.00