Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.36, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
Hi,
Setup is like this
Relay(MySQL5.5-MIXED-log_slave_updates)
->Relay(MDB10.1.36 MIXED-log_slave_updates )
-> Slave(MDB10.1.36 MIXED-log_slave_updates/optimistic)
-> Slave(MDB10.1.36 MIXED-log_slave_updates/conservative)
- We get deadlock on a slave in optimistic.
- Other slaves in conservative do not deadlock.
- Impossible to stop slave when deadlock
The scenario that trigger such deadlock was some optimize tables on the first relay and mysql restart after optimize
- the restart let relay master without events from master
|
Slave_IO_Running: Connecting
|
Slave_SQL_Running: Yes
|
Last_IO_Errno: 2003
|
Last_IO_Error: error reconnecting to master 'replication@x.x.x.x:3306' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on 'x.x.x.x' (111 "Connection refused")
|
The deadlock:
+--------+--------------+----------------------+-------------+---------+--------+-----------------------------------------------+---------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+--------+--------------+----------------------+-------------+---------+--------+-----------------------------------------------+---------------------------+----------+
|
| 190042 | system user | | NULL | Connect | 196362 | Waiting for master to send event | NULL | 0.000 |
|
| 190043 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190044 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190045 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190046 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190047 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190048 | system user | | NULL | Connect | 196362 | Waiting for work from SQL thread | NULL | 0.000 |
|
| 190049 | system user | | tsce_unedic | Connect | 171856 | Waiting for table metadata lock | OPTIMIZE TABLE `requetes` | 0.000 |
|
| 190050 | system user | | NULL | Connect | 171856 | Waiting for prior transaction to commit | NULL | 0.000 |
|
| 190051 | system user | | NULL | Connect | 177872 | Waiting for room in worker thread event queue | NULL | 0.000 |
|
| 307368 | root | localhost | NULL | Killed | 116880 | Killing slave | stop slave | 0.000 |
|
| 434731 | root | localhost | NULL | Killed | 30480 | Killing slave | stop slave | 0.000 |
|
| 474959 | root | localhost | NULL | Killed | 3167 | Killing slave | stop slave | 0.000 |
|
| 479253 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
|
| 479422 | proxysql | ?:38626 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 479451 | proxysql | ?:42168 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 479490 | netdata | localhost | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 479518 | repl_manager | 192.168.185.44:33222 | NULL | Sleep | 1 | | NULL | 0.000 |
|
+--------+--------------+----------------------+-------------+---------+--------+-----------------------------------------------+---------------------------+----------+
|
18 rows in set (0.00 sec)
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: x.x.x.x
|
Master_User: repl
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.000035
|
Read_Master_Log_Pos: 800365460
|
Relay_Log_File: relay-bin.000004
|
Relay_Log_Pos: 100745890
|
Relay_Master_Log_File: mysql-bin.000032
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 100745602
|
Relay_Log_Space: 4809515065
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 21
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 0-21-28343538
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: optimistic
|
|