Reason for error.
MySQL 5.7.5 and above implements detection of functional dependencies. If only_full_group_by SQL mode is enabled (which it is by default), then MySQL rejects queries referenced by select lists, conditions, or sequential lists that will refer to unnamed non-aggregated columns in the group, rather than depend on them functionally. (Prior to 5.7.5, MySQL did not detect functional dependencies, and only_full_group_by was not enabled by default. For a description of the pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
You can view the contents of sql_mode by executing the following command.
mysql> SHOW SESSION VARIABLES;
mysql> SHOW GLOBAL VARIABLES;
mysql> select @@sql_mode;
It can be seen that the value of sql_mode for both session and global is
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
only_full_group_by Description.
only_full_group_by : use this is to use the same group rules as oracle, select the columns to be in the group, or itself is an aggregated column (SUM, AVG, MAX, MIN) only, in fact, this configuration is currently a personal feeling and distinct similar, so remove the good
Solution:
Execute the following two commands.
mysql> 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’;
mysql> set session sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
These two commands, remove the sql_mode of ONLY_FULL_GROUP_BY
However, many users say to directly modify the mysql configuration file by adding sql_mode= to my.ini
[mysqld]
sql_mode=
Huang has also tried it himself. It’s really OK!
Read More:
- MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by
- [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’
- How to Solve mysql [Err] 1067-Invalid default value for
- How to Solve MySQL version 5.7+ Group by group error
- [Solved] MySQL Error: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
- [Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
- Mysql ERROR 1067: Invalid default value for ‘date’ [How to Solve]
- [Solved] MySQL Error: ERROR 1055 (42000)sql_mode=only_full_group_by
- [Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by
- [Solved]ERROR 1067 (42000): Invalid default value for ‘end_time‘ Mysql
- [Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP
- MySQL 8.0.12 runs SQL error #1055 [How to Solve]
- [Solved] MYSQL Start Project Error: this is incompatible with sql_mode=only_full_group_by
- Dbeaver Import SQL File Error: Error executing process Process failed (exit code = 1). See error log. Process failed (exit code = 1). See error log.
- MySQL OrderBy Error: Expression #1 of ORDER BY
- [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
- After installing mysql5.7 on centos7, an error 1045 (28000) is reported: access denied for user ‘root’ @’localhost ‘(using PAS)
- [Solved] Django configures MySQL Error: NameError: name ”_MySQL ‘is not defined
- Introduction to Relational Databases in SQL