Tag Archives: Database/HA

[Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

The MySQL table structure is as follows:

mysql> select * from fruits ;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

According to s_ ID groups the table data, and the error message is as follows

mysql> select * from fruits group by s_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
 'exercise.fruits.f_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible 
 with sql_mode=only_full_group_by

Query SQL_Mode mode

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Reason: only is added after MySQL 5.7_full_group_By roughly means that the column you select must be included in group by (for example, select a, B from table, group by a, B;), or you are an aggregate column, such as sum(), avg(), max(). The
official website says that SQL mode affects the syntax supported by MySQL and the data verification checks it performs, This makes it easier to use MySQL in different environments and other database servers

Official website reference link: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

Solution:

1. It is found that the set global setting in MySQL does not take effect and cannot be set under my.cnf

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

2. Query again is normal

mysql> select * from fruits group by s_id,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| c0   |  101 | cherry     |    3.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| bs1  |  102 | orange     |   11.20 |
| a2   |  103 | apricot    |    2.20 |
| o2   |  103 | coconut    |    9.20 |
| b2   |  104 | berry      |    7.60 |
| l2   |  104 | lemon      |    6.40 |
| bs2  |  105 | melon      |    8.20 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| m1   |  106 | mango      |   15.70 |
| t4   |  107 | xbababa    |    3.60 |
| b5   |  107 | xxxx       |    3.60 |
+------+------+------------+---------+

sql_Mode interpretation:

ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT is not in the GROUP
Appears in BY, then this SQL is illegal, because the column is not in the GROUP BY clause
 
NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of self-growing columns. By default, inserting 0 or NULL means that the next self-increasing value is generated. If the user
Hope that the inserted value is 0, and the column is self-increasing, then this option is useful.
 
STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and there is no restriction on non-transactional tables
NO_ZERO_IN_DATE: In strict mode, the date and month are not allowed to be zero
 
NO_ZERO_DATE: Set this value. The mysql database does not allow the insertion of a zero date. Inserting a zero date will throw an error instead of a warning.
 
ERROR_FOR_DIVISION_BY_ZERO: During INSERT or UPDATE, if the data is divided by zero, an error is generated instead of a warning. like
If the mode is not given, then MySQL returns NULL when the data is divided by zero
 
NO_AUTO_CREATE_USER: prohibit GRANT from creating users with empty passwords
 
NO_ENGINE_SUBSTITUTION:
If the required storage engine is disabled or not compiled, then an error is thrown. When this value is not set, replace with the default storage engine and throw an exception
 
PIPES_AS_CONCAT:
Treat "||" 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_QUOTES: When ANSI_QUOTES is enabled, double quotes cannot be used to quote a string because it is interpreted as an identifier

ERROR 1075 (42000) Incorrect table definition; there can be only one auto column and it must be defi

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
The table is created as follows:

mysql> create table tb_emp5(
    -> id int(11) not null auto_increment,
    -> name varchar(11),
    -> deptid int(11) not null auto_increment,
    -> salary float,
    -> primary key(id,deptid)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

when creating a MySQL table, if you want to set auto_increment, you must set the primary key, not null, and only one

After the change is as follows:

mysql> create table tb_emp5(
    -> id int(11) not null auto_increment,
    -> name varchar(11),
    -> deptid int(11) not null,
    -> salary float,
    -> primary key(id)
    -> );

mysql> desc tb_emp5;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(11) | YES  |     | NULL    |                |
| deptid | int(11)     | NO   |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+