Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17515

GTID Replication in optimistic mode deadlock

    XMLWordPrintable

    Details

      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
      
      

        Attachments

          Activity

            People

            Assignee:
            sachin.setiya.007 Sachin Setiya
            Reporter:
            stephane@skysql.com VAROQUI Stephane
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated: