MySQL案例：一次诡异的Aborted connection错误排查

偏执的太偏执、 2022-11-07 04:24 446阅读 0赞

# 简介 #

前段时间，研究怎么去提升数据库安全，例如禁止执行不带条件的update操作，于是就想到了去启用sql\_safe\_updates参数，这个参数Mysql默认是不启用的，而且还不能加入到my.cnf配置里。因此就想到了用init\_connect参数，将sql\_safe\_updates=1放到init\_connect参数里，这样每个用户会话连接的时候，就会启用sql\_safe\_updates参数了。

可是用普通连接数据库之后，使用某个库之后，就会报错

mysql> use information_schema;
    No connection. Trying to reconnect...
    Connection id:    16
    Current database: *** NONE ***
    
    ERROR 1184 (08S01): Aborted connection 16 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

**报错分析**

分析报错之前，先复盘一下操作步骤  
1.创建普通用户

2.使用root用户登录数据库，并设置init\_connect参数

mysql> set global init_connect='sql_safe_updates=1';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'init_connect';
    +---------------+--------------------+
    | Variable_name | Value              |
    +---------------+--------------------+
    | init_connect  | sql_safe_updates=1 |
    +---------------+--------------------+
    1 row in set (0.00 sec)

3.使用普通用户jim连接测试

root@18374a493e56:~# mysql -ujim -pjim
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 18
    Server version: 8.0.21
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use information_schema
    No connection. Trying to reconnect...
    Connection id:    19
    Current database: *** NONE ***
    
    ERROR 1184 (08S01): Aborted connection 19 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

4.使用root用户连接测试

root@18374a493e56:~# mysql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 20
    Server version: 8.0.21 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use information_schema;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed

从上面的报错信息也能很快的判断出，是由于init\_connect设置不合理导致的，可是这里很奇怪的是，普通用户会报错，root用户操作没有报错。弄不清楚为什么，于是就去看官方文档，看看官方文档是怎么描述的。

init\_connect参数描述

For users that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege), the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.

这段话的大概意思是，当用户具有CONNECTION\_ADMIN，SUPER权限用户登录时，是不需要执行init\_connect参数的内容的，而不具备这些权限的用户登录时，需要执行init\_connect参数的内容，当init\_connect参数的内容语句有问题时，就会报错了，这就解释了为什么root用户没有问题，而普通用户发生了问题。

了解报错原因之后，需要修改init\_connect的内容了，init\_connect里的内容复制出来，如果在mysql command命令行里执行没有问题就可以了。

5.重新设置init\_connect参数值

mysql> show variables like 'init_connect';
    +---------------+--------------------+
    | Variable_name | Value              |
    +---------------+--------------------+
    | init_connect  | sql_safe_updates=1 |
    +---------------+--------------------+
    1 row in set (0.01 sec)
    
    mysql> set session sql_safe_updates=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global init_connect='set session sql_safe_updates=1';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'init_connect';
    +---------------+--------------------------------+
    | Variable_name | Value                          |
    +---------------+--------------------------------+
    | init_connect  | set session sql_safe_updates=1 |
    +---------------+--------------------------------+
    1 row in set (0.00 sec)

6.使用普通用户jim再次连接测试

root@18374a493e56:~# mysql -ujim -pjim
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 21
    Server version: 8.0.21 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use information_schema;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> 
    mysql> show variables like 'sql_safe_updates';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | sql_safe_updates | ON    |
    +------------------+-------+
    1 row in set (0.01 sec)

从测试结果可以看到，已经可以正常使用Mysql数据库了，而且参数sql\_safe\_updates也设置正确了。

# 总结 #

总之，生产操作无小事，大家在生产上执行任何操作时，一定要在测试环境充分验证之后，了解影响范围之后，方可上线操作，如文中操作，很可能会导致一次线上故障。

https://mp.weixin.qq.com/s/LAcSlcWPzGipZv6dgldOxw