Proxysql: 9001 - Max connect timeout reached while reaching hostgroup 10 after 10000ms

[skyrim61]

The error log follow:

tail -f proxysql.log

2018-01-25 09:40:44 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:44 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:44 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:44 MySQL_HostGroups_Manager.cpp:292:connect_error(): [ERROR] Shunning server rm-1111111111111111.mysql.rds.aliyuncs.com:3306 with 5 errors/sec. Shunning for 10 seconds
2018-01-25 09:40:54 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:54 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:54 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:40:54 MySQL_HostGroups_Manager.cpp:292:connect_error(): [ERROR] Shunning server rm-1111111111111111.mysql.rds.aliyuncs.com:3306 with 5 errors/sec. Shunning for 10 seconds
2018-01-25 09:41:04 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:41:04 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:41:04 mysql_connection.cpp:608:handler(): [ERROR] Failed to mysql_real_connect() on rm-1111111111111111.mysql.rds.aliyuncs.com:3306 , FD (Conn:96 , MyDS:96) , 1449: The user specified as a definer ('10xurui'@'10.120.221.103') does not exist.
2018-01-25 09:41:04 MySQL_HostGroups_Manager.cpp:292:connect_error(): [ERROR] Shunning server rm-1111111111111111.mysql.rds.aliyuncs.com:3306 with 5 errors/sec. Shunning for 10 seconds

[pondix]

Please provide a clear description of your issue, the version of OS and ProxySQL, every step to reproduce the issue.

• Make sure that the user you have defined for mysql-monitor-xxx has been created in your MySQL database with the correct "host" defined https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#configure-monitoring.

• Regarding the error 1449 it looks like you've imported some routines and the user specified in DEFINER is not present on the new database.

[renecannao]

This issue is interesting.
Error 1149 is, as pointed by @pondix , related to some routines and the user specified in DEFINER is not present.
What makes it interesting is that this error should be triggered when executing the routine, while in your case the error is triggered during the connect phase.
Maybe this behaviour is strictly related to Alibaba's RDS ?

[skyrim61]

thank you relay,

1. 用户10xurui已经存在后端服务器上.
2. 在proxysql所在的服务器上,我可以通过mysql -u 10xurui -ppwd -h [background server's ip address] -p3306,连接上服务器, mysql > 执行任何命令都没有问题.
3. 在proxysql所在的服务器上,我可以通过mysql -u10xurui -ppwd -h [proxysql's servers's ip address] -p6033 ,可以连接上,但是在mysql > 命令行下, 无法执行任何命令.

[pondix]

Please make sure your have properly added the servers to ProxySQL and that you have executed "load mysql servers to runtime" / "save mysql servers to disk" (for persistence).

Detailed steps on configuring ProxySQL can be found in the Wiki's tutorial:

https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

In case you have any further questions related to configuration and require help please use the forum: https://groups.google.com/forum/#!forum/proxysql or alternatively check the ProxySQL website for support options (http://www.proxysql.com/support).

Thank you.

[zx1986]

@skyrim61 老哥，你也是跑 ProxySQL Cluster 嗎？
是否跟這位一樣的情況？ https://www.cnblogs.com/2woods/p/9537719.html

我也還在查錯 ...

[mikegriffin]

I believe that I can reproduce this by having few connections allowed to MySQL, many allowed to proxysql, and holding a table lock:

Create proxy:

admin proxysql> set mysql-max_connections=45000;
Query OK, 1 row affected (0.000 sec)

admin proxysql> insert into mysql_servers (hostgroup_id,hostname,port,max_connections,comment) values (10, '192.168.4.1', 3306, 64, 'master-db1');
Query OK, 1 row affected (0.000 sec)

admin proxysql> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('appuser', 'PETwW5aWgMNA', 10);
Query OK, 1 row affected (0.000 sec)

Run a script to SELECT count(*) from t1 a lot:

[root@pxl-web1 ~]# cat /root/select1-proxy.sh

for i in {1..8000}; do
mysql --defaults-file=/root/.my.cnf.proxysql -s <<< "select count(*) from t1" &
done
[root@pxl-web1 ~]# time /root/select1-proxy.sh | grep -c '^1$' ## table not locked
8000

real 0m25.287s
user 0m49.245s
sys 0m51.086s

Run the same but acquire a table lock on the master shortly after starting /root/select1-proxy.sh on pxl-web1:

[root@pxl-web1 ~]# time /root/select1-proxy.sh | grep -c '^1$' ## table locked for 12s but this started first
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10001ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10005ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10000ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10001ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10000ms

7355

real 0m25.553s
user 0m49.825s
sys 0m51.325s

Holding a lock on the master:

[root@pxl-db1 ~]# mysql -s <<< "lock tables t1 write; select sleep(12) into @x"

Afterward, on the MySQL server:

MariaDB [dbname]> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 99 |
+----------------------+-------+
1 row in set (0.001 sec)

MariaDB [dbname]> show create table t1\G
******** 1. row ********
Table: t1
Create Table: CREATE TABLE t1 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.000 sec)

Proxy node

[root@pxl-proxy1 ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)

[root@pxl-proxy1 ~]# rpm -qi proxysql
Name : proxysql
Version : 2.0.8
Release : 1
Architecture: x86_64
Install Date: Wed 15 Jan 2020 08:23:38 PM UTC
Group : Development/Tools
Size : 36184028
License : GPL+
Signature : RSA/SHA256, Tue 05 Nov 2019 02:14:30 PM UTC, Key ID 04a562fb79953b49
Source RPM : proxysql-2.0.8-1.src.rpm
Build Date : Tue 05 Nov 2019 11:57:35 AM UTC
Build Host : 7da6495e223d

Let me know if you need more details, I was trying to keep my comment concise.