插件connection_control 引发 Mysql hang （SHOW PROCESSLIST出现大量 unauthenticated user进程）详尽测试

一、数据库mysql 新近升级到5.7.41，启用connection_control，出现数据库HANG

connection_control启用后，设置最小延时等待 connection_control_min_connection_delay为1分钟， 数据库SHOW PROCESSLIST出现大量 unauthenticated user进程，按照Oracle 官方文档 1023847.1检查，以及常规的跟踪无果。

What does “unauthenticated user” mean in SHOW PROCESSLIST output? (文档 ID 1023847.1)
TROUBLESHOOTING STEPS
If the result of running the SHOW PROCESSLIST SQL statement shows that the server has connections in the state of unauthenticated user, this means that a connection to the server has been made but the client login process is not yet complete. It is normal to see this in SHOW PROCESSLIST snapshots sometimes, particularly on very busy and highly loaded servers or for clients with slow connections. It is only likely to be a security concern if it is an unusually frequent result, or possibly from an unknown client address. The output may look like this

| 2453054 | unauthenticated user | 192.168.XXX.1:60472 | NULL | Connect | NULL | login | NULL |
| 2453055 | unauthenticated user | 192.168.XXX.10:61530 | NULL | Connect | NULL | login | NULL |
| 2453056 | unauthenticated user | 192.168.XXX.10:61534 | NULL | Connect | NULL | login | NULL |
| 2453058 | unauthenticated user | 192.168.XXX.1:41209 | NULL | Connect | NULL | login | NULL |

Solution
MySQL uses a combination of hostname or IP address and user name for authentication. The hostname has to be looked up from a DNS server. In versions 4.1.12a or 5.0.7 and earlier this exposure to DNS server performance is made worse because the hostname cache that reduces the number of lookups is broken. It is fixed in 4.1.13 and 5.0.8 later. There are three broad classes of solution.

DNS delays
Seeing unauthenticated users is almost invariably due to slow DNS lookups when trying to look up the IP address of the connecting client to get the hostname to use for checking against the grants tables. High performance users typically should grant using IP addresses instead of hostnames to avoid this vulnerability to DNS server performance and for faster connections by skipping the lookup process. You can check the lookup time from the command line of the server. Try to resolve an address and see if it times out or takes a long time.

[mysql@magicbox bin]$ nslookup oracle.com
Server: 10.XXX.XXX.100
Address: 10.XXX.XXX.100#53

Non-authoritative answer:
Name: oracle.com
Address:

If it does not resolve quickly then consult your local network administrator.

There are three steps you can take to improve slow DNS response time or remove the exposure to DNS server performance:

Switch to using IP addresses and IP ranges in your grants and use skip-name-resolve.
Upgrade to at least 4.1.13 or 5.0.8 to get a working hostname cache.
For hosts that change rarely or are local you could add an entry for their IP into your local hosts file on the server so that entry will be used instead of making a DNS server lookup.

Using IP authentication, access not granted for that IP for the user
Alternatively, you may have started the server with --skip-name-resolve and have some IP addresses where you have not yet granted permission to connect from the IP address in the processlist for the user trying to connect. If this is intentional then simply add the IP to the “host” column as follows.

grant on DB.* to user1@‘192.168.XXX.1’;

SSL performance
A final possiblity is that if you have recently enabled SSL connections to the DB and are seeing a mixed number of successful and unsuccessful connections to the DB it could be related to the overhead in processing the SSL. You may wish to reduce the number of SSL connections or invest in higher spec’d hardware. Running top ( for linux / task manager for windows ) will show the load average of your server.

二、 转机，有数据库出现自身IP的 unauthenticated user进程

事情发生转机，在检查时发现有数据库出现自身IP的 unauthenticated user进程，遂用telenet 链接数据库端口进行测试，复现unauthenticated user进程。
初步验证测试，数据库为win10下mysql 5.7.40。

1、启用 connection_control，设置最小延时等待 为1分钟。

## 2、telenet测试，复现unauthenticated user进程。

3、暂停了数据库的探测后，数据库情况好转

 基本可以确定是监控进程的探测引起的该问题。
 暂停了监控工具对数据库的探测后，数据库情况有好转。

三、 详尽测试,循环程序发起攻击模拟

1、win 10 下安装 Mysql 5.7.40，配置 connection_control

2、telenet 测试，需要等待60秒后，telenet 进程才退出

3、tnsping 测试，瞬间可以退出，循环程序攻击模拟

实际测试发现，Oracle数据库的命令tnsping探测Mysql数据库的端口，可以瞬间返回，无需等待60秒。
win10 CMD 命令行执行for循环，执行100次mysql数据库探测：

for /l %i in (1 1 100) do (tnsping 192.168.100.200:1601)

mysql数据库执行show processlist 出现100个unauthenticated user进程。
在mysql版本Mysql 5.7.40和Mysql 5.7.41下，使用tnsping攻击模拟时，mysql在processlist

4、win 10 下安装 Mysql 8.0.33，配置 connection_control

mysql数据库执行show processlist 没有unauthenticated user进程，不受影响。

四、数据库mysql 升级到最新版

1、测试使用mysql installer 升级到最新版mysql 5.7.42

2、tnsping 测试，循环程序攻击模拟

在该版本下，mysql数据库执行show processlist 没有unauthenticated user进程，对于tnsping模拟的探测包，mysql没有创建process，mysql进程不增加。

CMD执行netstat -an 可以看到有大量的等待，之后会消失。

五、 总结

Mysql 5.7.41（2023-1-17发布）及小于该补丁版本，启用 connection_control，设置最小延时等待 为1分钟，针对该库的探测包，会导致 数据库SHOW PROCESSLIST出现大量 unauthenticated user进程，数据库HANG。
Mysql 5.7.42（2023-4-18发布）的补丁版本可以解决该问题。

附加 ：mysql 5.7与8.0 补丁发布时间列表：
Update done on 2023-04-24. Added or changed packages were:

• MySQL Server 8.0.33 Architecture: X64 Published: 2023-04-18
• MySQL Server 8.0.32 Architecture: X64 Published: 2023-01-17
• MySQL Server 8.0.31 Architecture: X64 Published: 2022-10-11
• MySQL Server 8.0.30 Architecture: X64 Published: 2022-07-26
• MySQL Server 8.0.29 Architecture: X64 Published: 2022-04-26
• MySQL Server 8.0.28 Architecture: X64 Published: 2022-01-18
• MySQL Server 8.0.27 Architecture: X64 Published: 2021-11-12
• MySQL Server 8.0.26 Architecture: X64 Published: 2021-07-20
• MySQL Server 8.0.25 Architecture: X64 Published: 2021-05-11
• MySQL Server 8.0.24 Architecture: X64 Published: 2021-04-20
• MySQL Server 8.0.23 Architecture: X64 Published: 2021-01-20
• MySQL Server 8.0.22 Architecture: X64 Published: 2020-10-19
• MySQL Server 8.0.21 Architecture: X64 Published: 2020-07-13
• MySQL Server 8.0.20 Architecture: X64 Published: 2020-04-27
• MySQL Server 8.0.19 Architecture: X64 Published: 2020-01-13
• MySQL Server 8.0.18 Architecture: X64 Published: 2019-10-14
• MySQL Server 8.0.17 Architecture: X64 Published: 2019-07-22
• MySQL Server 8.0.16 Architecture: X64 Published: 2019-04-25
• MySQL Server 8.0.15 Architecture: X64 Published: 2019-02-04
• MySQL Server 8.0.14 Architecture: X64 Published: 2019-01-21
• MySQL Server 8.0.13 Architecture: X64 Published: 2018-10-22
• MySQL Server 8.0.12 Architecture: X64 Published: 2018-07-27
• MySQL Server 8.0.11 Architecture: X64 Published: 2018-04-19
• MySQL Server 8.0.4 Architecture: X64 Published: 2018-01-23
• MySQL Server 8.0.3 Architecture: X64 Published: 2017-09-21
• MySQL Server 8.0.2 Architecture: X64 Published: 2017-07-18
• MySQL Server 8.0.1 Architecture: X64 Published: 2017-04-10
• MySQL Server 8.0.0 Architecture: X64 Published: 2016-09-12
• MySQL Server 5.7.42 Architecture: X64 Published: 2023-04-18
• MySQL Server 5.7.42 Architecture: X86 Published: 2023-04-18
• MySQL Server 5.7.41 Architecture: X64 Published: 2023-01-17
• MySQL Server 5.7.41 Architecture: X86 Published: 2023-01-17
• MySQL Server 5.7.40 Architecture: X64 Published: 2022-10-11
• MySQL Server 5.7.40 Architecture: X86 Published: 2022-10-11
• MySQL Server 5.7.39 Architecture: X64 Published: 2022-07-26
• MySQL Server 5.7.39 Architecture: X86 Published: 2022-07-26
• MySQL Server 5.7.38 Architecture: X64 Published: 2022-04-26
• MySQL Server 5.7.38 Architecture: X86 Published: 2022-04-26
• MySQL Server 5.7.37 Architecture: X64 Published: 2022-01-18
• MySQL Server 5.7.37 Architecture: X86 Published: 2022-01-18
• MySQL Server 5.7.36 Architecture: X64 Published: 2021-11-12
• MySQL Server 5.7.36 Architecture: X86 Published: 2021-11-12
• MySQL Server 5.7.35 Architecture: X64 Published: 2021-07-20
• MySQL Server 5.7.35 Architecture: X86 Published: 2021-07-20
• MySQL Server 5.7.34 Architecture: X64 Published: 2021-04-20
• MySQL Server 5.7.34 Architecture: X86 Published: 2021-04-20
• MySQL Server 5.7.33 Architecture: X64 Published: 2021-01-20
• MySQL Server 5.7.33 Architecture: X86 Published: 2021-01-20
• MySQL Server 5.7.32 Architecture: X86 Published: 2020-10-19
• MySQL Server 5.7.32 Architecture: X64 Published: 2020-10-19
• MySQL Server 5.7.31 Architecture: X86 Published: 2020-07-13
• MySQL Server 5.7.31 Architecture: X64 Published: 2020-07-13
• MySQL Server 5.7.30 Architecture: X86 Published: 2020-04-27
• MySQL Server 5.7.30 Architecture: X64 Published: 2020-04-27
• MySQL Server 5.7.29 Architecture: X86 Published: 2020-01-13
• MySQL Server 5.7.29 Architecture: X64 Published: 2020-01-13
• MySQL Server 5.7.28 Architecture: X86 Published: 2019-10-14
• MySQL Server 5.7.28 Architecture: X64 Published: 2019-10-14
• MySQL Server 5.7.27 Architecture: X86 Published: 2019-07-22
• MySQL Server 5.7.27 Architecture: X64 Published: 2019-07-22
• MySQL Server 5.7.26 Architecture: X86 Published: 2019-04-25
• MySQL Server 5.7.26 Architecture: X64 Published: 2019-04-25
• MySQL Server 5.7.25 Architecture: X86 Published: 2019-01-21
• MySQL Server 5.7.25 Architecture: X64 Published: 2019-01-21
• MySQL Server 5.7.24 Architecture: X86 Published: 2018-10-22
• MySQL Server 5.7.24 Architecture: X64 Published: 2018-10-22
• MySQL Server 5.7.23 Architecture: X86 Published: 2018-07-27
• MySQL Server 5.7.23 Architecture: X64 Published: 2018-07-27
• MySQL Server 5.7.22 Architecture: X86 Published: 2018-04-19
• MySQL Server 5.7.22 Architecture: X64 Published: 2018-04-19
• MySQL Server 5.7.21 Architecture: X86 Published: 2018-01-15
• MySQL Server 5.7.21 Architecture: X64 Published: 2018-01-15
• MySQL Server 5.7.20 Architecture: X86 Published: 2017-10-16
• MySQL Server 5.7.20 Architecture: X64 Published: 2017-10-16
• MySQL Server 5.7.19 Architecture: X86 Published: 2017-07-17
• MySQL Server 5.7.19 Architecture: X64 Published: 2017-07-17
• MySQL Server 5.7.18 Architecture: X86 Published: 2017-04-10
• MySQL Server 5.7.18 Architecture: X64 Published: 2017-04-10
• MySQL Server 5.7.17 Architecture: X86 Published: 2016-12-12
• MySQL Server 5.7.17 Architecture: X64 Published: 2016-12-12
• MySQL Server 5.7.16 Architecture: X86 Published: 2016-10-12
• MySQL Server 5.7.16 Architecture: X64 Published: 2016-10-12
• MySQL Server 5.7.15 Architecture: X86 Published: 2016-09-06
• MySQL Server 5.7.15 Architecture: X64 Published: 2016-09-06
• MySQL Server 5.7.14 Architecture: X86 Published: 2016-07-29
• MySQL Server 5.7.14 Architecture: X64 Published: 2016-07-29
• MySQL Server 5.7.13 Architecture: X86 Published: 2016-06-02
• MySQL Server 5.7.13 Architecture: X64 Published: 2016-06-02
• MySQL Server 5.7.12 Architecture: X86 Published: 2016-04-12
• MySQL Server 5.7.12 Architecture: X64 Published: 2016-04-12
• MySQL Server 5.7.11 Architecture: X86 Published: 2016-02-05
• MySQL Server 5.7.11 Architecture: X64 Published: 2016-02-05
• MySQL Server 5.7.10 Architecture: X86 Published: 2015-12-07
• MySQL Server 5.7.10 Architecture: X64 Published: 2015-12-07
• MySQL Server 5.7.9 Architecture: X86 Published: 2015-10-21
• MySQL Server 5.7.9 Architecture: X64 Published: 2015-10-21
• MySQL Server 5.7.8 Architecture: X86 Published: 2015-08-03
• MySQL Server 5.7.8 Architecture: X64 Published: 2015-08-03
• MySQL Server 5.7.7 Architecture: X86 Published: 2015-04-09
• MySQL Server 5.7.7 Architecture: X64 Published: 2015-04-09
• MySQL Server 5.7.6 Architecture: X86 Published: 2015-03-10
• MySQL Server 5.7.6 Architecture: X64 Published: 2015-03-10
• MySQL Server 5.7.5 Architecture: X86 Published: 2014-09-26
• MySQL Server 5.7.5 Architecture: X64 Published: 2014-09-26
• MySQL Server 5.7.4 Architecture: X86 Published: 2014-03-31
• MySQL Server 5.7.4 Architecture: X64 Published: 2014-03-31
• MySQL Server 5.7.3 Architecture: X86 Published: 2013-12-04
• MySQL Server 5.7.3 Architecture: X64 Published: 2013-12-04
• MySQL Server 5.7.2 Architecture: X86 Published: 2013-09-11
• MySQL Server 5.7.2 Architecture: X64 Published: 2013-09-11
• MySQL Server 5.7.1 Architecture: X86 Published: 2013-04-23
• MySQL Server 5.7.1 Architecture: X86 Published: 2015-02-09
• MySQL Server 5.7.1 Architecture: X64 Published: 2015-02-09
• MySQL Server 5.7.1 Architecture: X64 Published: 2013-04-03