When setting up a blog, it is collapsed by only full group by in SQL mode of mysql

1, background

Two days ago, I set up my own blog on Alibaba cloud server. Everything went well. Today, when I clicked the archive button, I found that it was a newspaper 404. So I run the solo code locally, and use the local mysql database to see if there is the same problem, and the result can be accessed normally. Look at the solo logs on the server, and the following errors are found:

Caused by: org.b3log.latke.repository.RepositoryException: java.sql.SQLSyntaxErrorException: Expression #20 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'solo.aa.oId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Originally, this problem occurred in MySQL 5.7 and later. The default SQL mode value is as follows:

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

What are the configurations of SQL mode? What do you mean?

2. SQL mode configuration analysis

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operation, if the column in SELECT does not appear in GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause. In short, the column following SELECT must be contained by the column following GROUP BY. Such as:

select a,b from table group by a,b,c; (Correct)
select a,b,c from table group by a,b; (error)

This configuration will make the GROUP BY statement environment very narrow, so this configuration is generally not added

  • NO_AUTO_VALUE_ON_ZERO

This value affects the insertion of self growing columns. By default, insert 0 or null to generate the next self growing value. (if you don't believe it, try the default SQL mode. If you set the auto increment primary key column to 0, the field will automatically change to the latest auto increment. The effect is the same as null). If the user wants to insert a value of 0 (no change), the column will grow again, then this option is useful.

  • STRICT_TRANS_TABLES

In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted and the non transaction table will not be restricted. (InnoDB default transaction table, MyISAM default non transaction table; MySQL transaction table supports batch processing as a complete task for unified submission or rollback, that is, multiple statements contained in the transaction are either fully executed or not executed. This operation is not supported for non transaction tables. If the statements in batch process encounter errors, the statements before the errors are executed successfully, and the statements after the errors are not executed; if MySQL transaction tables have table locks and row locks, the non transaction tables only have table locks.)

  • NO_ZERO_IN_DATE

Zero day and month are not allowed in strict mode

  • NO_ZERO_DATE

Setting this value, mysql database does not allow to insert zero date. Inserting 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. If this mode is not given, MySQL returns NULL when the data is divided by zero

  • NO_AUTO_CREATE_USER

Prevent GRANT from creating users with blank passwords

  • NO_ENGINE_SUBSTITUTION

If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, replace it with the default storage engine and throw an exception

  • PIPES_AS_CONCAT

Consider "|" as the concatenation operator rather than or operator of string, which is the same as Oracle database and similar to Concat, the concatenation function of string

  • ANSI_QUOTES

When ANSI_QUOTES is enabled, a string cannot be referenced in double quotes because it is interpreted as an ID

3, test

Start a local database, and check the SQL mode first:

mysql> select @@global.sql_mode;                
+--------------------------------------------+  
| @@global.sql_mode                          |  
+--------------------------------------------+  
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |  
+--------------------------------------------+  
1 row in set (0.00 sec)                         
                                                
mysql> select @@session.sql_mode;               
+--------------------------------------------+  
| @@session.sql_mode                         |  
+--------------------------------------------+  
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |  
+--------------------------------------------+  
1 row in set (0.00 sec)                         

Create a test table:

mysql> CREATE TABLE IF NOT EXISTS `demo`(                       
    ->    `id` INT UNSIGNED AUTO_INCREMENT,                     
    ->    `rank` VARCHAR(100) NOT NULL,                         
    ->    `name` VARCHAR(40) NOT NULL,                          
    ->    `gender` TINYINT NOT NULL,                            
    ->    PRIMARY KEY ( `id` )                                  
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;                     
Query OK, 0 rows affected (0.02 sec)                            
                                                                
mysql>                                                          
mysql> show tables;                                             
+----------------+                                              
| Tables_in_test |                                              
+----------------+                                              
| demo           |                                              
+----------------+                                              
1 row in set (0.00 sec)                                         
                                                                
mysql> desc demo;                                               
+--------+------------------+------+-----+---------+------------
| Field  | Type             | Null | Key | Default | Extra      
+--------+------------------+------+-----+---------+------------
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increm
| rank   | varchar(100)     | NO   |     | NULL    |            
| name   | varchar(40)      | NO   |     | NULL    |            
| gender | tinyint(4)       | NO   |     | NULL    |            
+--------+------------------+------+-----+---------+------------
4 rows in set (0.01 sec)                                        

Insert test data:

mysql> insert into demo values(1, 'A', 'coderaction1', '20');   
Query OK, 1 row affected (0.01 sec)                             
                                                                
mysql> insert into demo values(2, 'B', 'coderaction2', '21');   
Query OK, 1 row affected (0.00 sec)                             
                                                                
mysql> insert into demo values(3, 'A', 'coderaction3', '22');   
Query OK, 1 row affected (0.00 sec)                             
                                                                
mysql> insert into demo values(4, 'C', 'coderaction4', '23');   
Query OK, 1 row affected (0.00 sec)                             
                                                                
mysql> insert into demo values(5, 'A', 'coderaction5', '21');   
Query OK, 1 row affected (0.00 sec)                             
                                                                
mysql> insert into demo values(6, 'C', 'coderaction6', '28');   
Query OK, 1 row affected (0.01 sec)                             
                                                                
mysql>                                                          
mysql> select * from demo;                                      
+----+------+--------------+--------+                           
| id | rank | name         | gender |                           
+----+------+--------------+--------+                           
|  1 | A    | coderaction1 |     20 |                           
|  2 | B    | coderaction2 |     21 |                           
|  3 | A    | coderaction3 |     22 |                           
|  4 | C    | coderaction4 |     23 |                           
|  5 | A    | coderaction5 |     21 |                           
|  6 | C    | coderaction6 |     28 |                           
+----+------+--------------+--------+                           
6 rows in set (0.00 sec)                                        

Execute the following sql commands respectively:

mysql> select count(id) from demo order by rank;                
+-----------+                                                   
| count(id) |                                                   
+-----------+                                                   
|         6 |                                                   
+-----------+                                                   
1 row in set (0.01 sec)                                         
                                                                
mysql> select count(id) from demo group by rank;                
+-----------+                                                   
| count(id) |                                                   
+-----------+                                                   
|         3 |                                                   
|         1 |                                                   
|         2 |                                                   
+-----------+                                                   
3 rows in set (0.00 sec)                                        
                                                                
mysql> select count(rank),id from demo group by rank;           
+-------------+----+                                            
| count(rank) | id |                                            
+-------------+----+                                            
|           3 |  1 |                                            
|           1 |  2 |                                            
|           2 |  4 |                                            
+-------------+----+                                            
3 rows in set (0.00 sec)                                        
                                                                     
mysql> select count(rank),id from demo group by id;             
+-------------+----+                                            
| count(rank) | id |                                            
+-------------+----+                                            
|           1 |  1 |                                            
|           1 |  2 |                                            
|           1 |  3 |                                            
|           1 |  4 |                                            
|           1 |  5 |                                            
|           1 |  6 |                                            
+-------------+----+                                            
6 rows in set (0.00 sec)                                        
                                                                
mysql>                                                          

You can see that the above four sql statements are executed successfully.

There are two ways to modify SQL mode: one is to set the session level SQL mode of the current session connection, and the other is the global level SQL mode.

session level

First, let's look at the session level SQL mode. There are two settings:

mysql> set 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';
Query OK, 0 rows affected (0.00 sec)

mysql> set @@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';
Query OK, 0 rows affected (0.00 sec)

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

Set the session level SQL mode, query the current session level to a new one, and it will fail after the next reconnection.

global level

Look at the global level SQL mode. There are two ways to set it:

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';
Query OK, 0 rows affected (0.00 sec)

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';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Set the global level SQL mode. If the current session level is found to be old, the command will be executed according to the old configuration. Use the new configuration after the next reconnection.

After setting the sql mode of the above session level and adding only full group by, execute the test sql statement to report an error:

mysql> select count(rank),id from demo group by rank;                                                                                                         
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.demo.id' which is not functionally dependen
t on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by                                                                        
mysql> select count(rank),id from demo group by id;                                                                                                           
+-------------+----+                                                                                                                                          
| count(rank) | id |                                                                                                                                          
+-------------+----+                                                                                                                                          
|           1 |  1 |                                                                                                                                          
|           1 |  2 |                                                                                                                                          
|           1 |  3 |                                                                                                                                          
|           1 |  4 |                                                                                                                                          
|           1 |  5 |                                                                                                                                          
|           1 |  6 |                                                                                                                                          
+-------------+----+                                                                                                                                          
6 rows in set (0.00 sec)                                                                                                                                      

This also verifies that the column following SELECT must be contained by the column following GROUP BY.

Note: the settings temporarily take effect through session and global, that is, when mysql is restarted, it will fail. It needs to be set by default in the mysql startup configuration file.

4. Solutions

In addition to the two temporary solutions used in the above tests. In order for my SQL to take effect after restart, you need to add SQL mode configuration under [mysqld] in my.cnf. Because I use mysql deployed in k8s, there is a certain difference between image installation and package installation on the host. But in the end, it's my.cnf.

kubectl exec -ti mysql-75797cf796-84rdl bash
root@mysql-75797cf796-84rdl:/# 
root@mysql-75797cf796-84rdl:/# cat /etc/mysql/my.cnf
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
# .....
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

You can see that there are two files in the directory. Look at mysql.conf.d and find the files we need to change

cat /etc/mysql/mysql.conf.d/mysqld.cnf

View and copy the file to the host with the command of kubectl cp. after modification, the file will be finally mounted in the pod.

kubectl cp default/mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d/mysqld.cnf /data/blog-solo/mysql-config/mysqld.cnf

The modified file is as follows, focusing on SQL mode

root@mysql-75797cf796-84rdl:/# cd /etc/mysql/mysql.conf.d/
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d# ls -l
total 4
-rw-r--r-- 1 root root 1671 Oct 26 11:40 mysqld.cnf
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d# cat mysqld.cnf 
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
# ...
[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
sql_mode        = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d#

Finally, modify MySQL deployment:

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 1
  template: 
    metadata:
      labels:
        name: mysql
    spec:
      containers:
      - name: mysql 
        image: mysql:5.7.28 
        imagePullPolicy: IfNotPresent
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "password"
        volumeMounts:
        - name: mysql-config
          mountPath: /etc/mysql/mysql.conf.d
        - name: mysql-data
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-config
        hostPath:
          path: /data/blog-solo/mysql-config/
      - name: mysql-data
        hostPath:
          path: /data/blog-solo/mysql-data/

Pay attention to mount the configuration file and data on the host, otherwise the configuration and data will be lost after the pod is restarted.

4, reference

Modify MySQL SQL mode and configuration file under docker

Remember only full Group by error and subsequent

This public number provides free csdn download service, massive IT learning resources, if you are ready to enter IT pit, inspiring to become an excellent program ape, then these resources are very suitable for you, including but not limited to java, go, python, springcloud, elk, embedded, big data, interview data, front-end and other resources. At the same time, we set up a technical exchange group, there are many big guys, will not regularly share technical articles, if you want to study together to improve, you can reply to the public background [2], free invitation and technology exchange group to learn from each other, will not share the programming IT resources.

Scan the code to pay attention to the wonderful content and push it to you at the first time

Tags: Programming MySQL SQL Session Database

Posted on Sun, 03 Nov 2019 00:15:42 -0700 by MDanz