MySQL cluster hang: primary has many process "waiting for handler commit"
Today, we first noticed that the MySQL innodb cluster hangs.
1. Show full processlist, we can see many processes are on "waiting for handler commit" state, following are some of them
530400 user1 db03:43736 test_db02 Query 5230 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='5187608d-3145-5c15-de6f-5c3dfae1b09e' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329681185 user1 db03:35106 test_db02 Query 5175 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='4c5392f1-5cfe-fbf3-0d79-ee6c98a44b69' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329709428 user1 db03:58476 test_db02 Query 5176 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='edb7cfa3-89e0-96e6-d534-58e7f25db8af' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329867929 user1 db03:51294 test_db02 Query 5221 waiting for handler commit commit
332720693 user1 db03:33494 test_db03 Query 4391 waiting for handler commit commit
333021837 user1 db03:36458 test_db02 Query 31 updating UPDATE ....
333021838 user1 db03:36462 test_db02 Query 5216 waiting for handler commit commit
2. On two slaves logs, they are full of message as below. And mysqld.log takes more than 5 Gb within two hours
2024-03-07T01:03:12.440902Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451309Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451333Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451343Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451363Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451370Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451378Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451386Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451393Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
3. We tried to stop mysql server on slaves, stopping hangs. We have to kill them
4. We tried to stop mysql server on primary db, and stopping hangs as well. We have to kill them
5. We tried to start primary db, it succeed.
6. We tried to start slaves, the group_replication fails.
2024-03-07T03:40:27.230448Z 0 [ERROR] [MY-013467] [Repl] Plugin
group_replication reported: 'No valid or ONLINE members exist to get the
missing data from the group. For cloning check if donors of the same version
and with clone plugin installed exist. For incremental recovery check if you
have donors where the required data was not purged from the binary logs.'
2024-03-07T03:40:27.230542Z 0 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
7. Then, we installed clone plugin on primary db and one of the slave dbs. Slaves can join to the cluster. (NOTE: I don't know why one slave has clone plugin installed, but another slave and primary db doesn't)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
In the past 3 years, we used mysql5.7 innodb cluster, and we haven't encounter such issue before. Last month, we steup this new MySQL8 cluster environment, and meet this issue for the first time.
Not sure if it's one bug. More important, if we meet this issue again, how could we workaround it ASAP.