Hi,

I have a question.

We have an instance of proxysql 1.3.7. when we login database per 6033 and try to run any command, it's failed and show the error information:

ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 0 after 10000ms

the proxysql.log show the following warning message:

2018-04-27 14:11:35 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Dead lock found when trying to get lock; try restarting transaction 2018-04-27 14:11:35 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Dead lock found when trying to get lock; try restarting transaction 2018-04-27 14:11:36 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Dead lock found when trying to get lock; try restarting transaction 2018-04-27 14:11:37 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Dead lock found when trying to get lock; try restarting transaction 2018-04-27 14:11:38 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Dead lock found when trying to get lock; try restarting transaction 2018-04-27 14:12:03 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Deadlock found when trying to get lock; try restarting transaction 2018-04-27 14:12:13 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Deadlock found when trying to get lock; try restarting transaction 2018-04-27 14:12:13 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Deadlock found when trying to get lock; try restarting transaction 2018-04-27 14:12:13 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Deadlock found when trying to get lock; try restarting transaction 2018-04-27 14:12:58 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,192.168.25.4,3306): 1213, Deadlock found when trying to get lock; try restarting transaction

Then we found actually have a dead lock but has been killed and mysql can normally run command but proxysql still stuck:

`------------------------

LATEST DETECTED DEADLOCK

2018-04-27 16:07:21 0x7f2769615700

*** (1) TRANSACTION:

TRANSACTION 3698443560, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 119766644, OS thread handle 139761761388288, query id 2213943399 192.168.25.4 mbd-mysql update

REPLACE INTO crawler_phrases_status(last_status, phrase_id, id, last_scan_time, crawler_name) values('没有得到数据下一时间段重试(有个别天抓取998错误):采集失败', 107605, 21335455, '2018-04-27 16:06:27', 'crawl_records_movie_searchd_and_idx_v2')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 101557 page no 411 n bits 336 index phrase_id of table online_yuqing_data.crawler_phrases_status trx id 3698443560 lock_mode X waiting

*** (2) TRANSACTION:

TRANSACTION 3698443559, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4998

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

MySQL thread id 119766452, OS thread handle 139807248439040, query id 2213943398 192.168.25.4 mbd-mysql update

REPLACE INTO crawler_phrases_status(last_status, phrase_id, id, last_scan_time, crawler_name) values('没有得到数据下一时间段重试(有个别天抓取998错误):采集失败', 107605, 21335458, '2018-04-27 16:06:27', 'crawl_records_movie_searchd_and_idx_v2')

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 101557 page no 411 n bits 336 index phrase_id of table online_yuqing_data.crawler_phrases_status trx id 3698443559 lock_mode X

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 101557 page no 411 n bits 336 index phrase_id of table online_yuqing_data.crawler_phrases_status trx id 3698443559 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (1)`

Until we manually restart proxysql, the service is back to normal.

Best regards,

Marya Zhao