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

Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

Details

    Description

      Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:

      MariaDB [(none)]> SHOW SLAVE STATUS\G
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 172.30.0.154
                        Master_User: repl
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mariadb-bin.000055
                Read_Master_Log_Pos: 593262418
                     Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                      Relay_Log_Pos: 33725828
              Relay_Master_Log_File: mariadb-bin.000039
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: No
                    Replicate_Do_DB:
                Replicate_Ignore_DB:
                 Replicate_Do_Table:
             Replicate_Ignore_Table:
            Replicate_Wild_Do_Table:
        Replicate_Wild_Ignore_Table:
                         Last_Errno: 1062
                         Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                       Skip_Counter: 0
                Exec_Master_Log_Pos: 705697132
                    Relay_Log_Space: 17101184558
                    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: NULL
      Master_SSL_Verify_Server_Cert: No
                      Last_IO_Errno: 0
                      Last_IO_Error:
                     Last_SQL_Errno: 1062
                     Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
        Replicate_Ignore_Server_Ids:
                   Master_Server_Id: 1
                     Master_SSL_Crl:
                 Master_SSL_Crlpath:
                         Using_Gtid: Slave_Pos
                        Gtid_IO_Pos: 0-1-42163518
            Replicate_Do_Domain_Ids:
        Replicate_Ignore_Domain_Ids:
                      Parallel_Mode: aggressive
                          SQL_Delay: 0
                SQL_Remaining_Delay: NULL
            Slave_SQL_Running_State:
      1 row in set (0.00 sec)
      

      However, when the problem is analyzed, it is clear that the duplicate key should not occur.

      The configuration:

      [mariadb]
      server_id=2
      log_bin=mariadb-bin
      log_slave_updates
      binlog_format=MIXED
      slave_parallel_threads=16
      slave_parallel_max_queued=1048576
      slave_parallel_mode=aggressive
      

      The configuration with slave_parallel_mode=optimistic was identical.

      Some observations:

      • When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.
      • The SQL script contains an UPDATE and a DELETE before the INSERT, so the row should have been deleted, which should have prevented the duplicate key error.
      • The row still exists, so it seems that the DELETE statement had no effect.
      • The values in the existing row in the table are the old non-updated values, so it seems that the UPDATE statement also had no effect.
      • The slave's binary log contains both the UPDATE and DELETE statements, which should indicate that the statements were successfully executed on the slave.
      • I haven't yet been able to reproduce the problem when the master has binlog_format=ROW set, so row-based replication may be immune to the problem.

      Attachments

        1. MDEV-20605.patch
          8 kB
        2. MDEV-20605-deadlock.test
          2 kB
        3. MDEV-20605-locking.test
          3 kB
        4. MDEV-20605-mysql.test
          3 kB
        5. MDEV-20605-restore-cursor.test
          3 kB
        6. MDEV-20605-restore-cursor-v2.test
          3 kB
        7. thiru_20605.yy
          0.9 kB

        Issue Links

          Activity

            GeoffMontee Geoff Montee (Inactive) created issue -
            GeoffMontee Geoff Montee (Inactive) made changes -
            Field Original Value New Value
            GeoffMontee Geoff Montee (Inactive) made changes -
            Description Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_mode=aggressive
            {noformat}

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.
            Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_max_queued=1048576
            slave_parallel_mode=aggressive
            {noformat}

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.
            GeoffMontee Geoff Montee (Inactive) made changes -
            Summary With aggressive mode of parallel replication, some replicated statements have no effect With optimistic and aggressive modes of parallel replication, some replicated statements have no effect
            GeoffMontee Geoff Montee (Inactive) made changes -
            Description Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_max_queued=1048576
            slave_parallel_mode=aggressive
            {noformat}

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.
            Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_max_queued=1048576
            slave_parallel_mode=aggressive
            {noformat}

            The configuration with {{slave_parallel_mode=optimistic}} was identical.

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.
            GeoffMontee Geoff Montee (Inactive) made changes -
            Affects Version/s 10.3 [ 22126 ]
            GeoffMontee Geoff Montee (Inactive) made changes -
            Elkin Andrei Elkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            GeoffMontee Geoff Montee (Inactive) made changes -
            Description Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_max_queued=1048576
            slave_parallel_mode=aggressive
            {noformat}

            The configuration with {{slave_parallel_mode=optimistic}} was identical.

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.
            Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:

            {noformat}
            MariaDB [(none)]> SHOW SLAVE STATUS\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.30.0.154
                              Master_User: repl
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mariadb-bin.000055
                      Read_Master_Log_Pos: 593262418
                           Relay_Log_File: ip-172-30-0-228-relay-bin.000002
                            Relay_Log_Pos: 33725828
                    Relay_Master_Log_File: mariadb-bin.000039
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: No
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 1062
                               Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 705697132
                          Relay_Log_Space: 17101184558
                          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: NULL
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 1062
                           Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 1
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                               Using_Gtid: Slave_Pos
                              Gtid_IO_Pos: 0-1-42163518
                  Replicate_Do_Domain_Ids:
              Replicate_Ignore_Domain_Ids:
                            Parallel_Mode: aggressive
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State:
            1 row in set (0.00 sec)
            {noformat}

            However, when the problem is analyzed, it is clear that the duplicate key should not occur.

            The configuration:

            {noformat}
            [mariadb]
            server_id=2
            log_bin=mariadb-bin
            log_slave_updates
            binlog_format=MIXED
            slave_parallel_threads=16
            slave_parallel_max_queued=1048576
            slave_parallel_mode=aggressive
            {noformat}

            The configuration with {{slave_parallel_mode=optimistic}} was identical.

            Some observations:

            * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.

            * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error.

            * The row still exists, so it seems that the {{DELETE}} statement had no effect.

            * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect.

            * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave.

            * I haven't yet been able to reproduce the problem when the master has {{binlog_format=ROW}} set, so row-based replication may be immune to the problem.
            marko Marko Mäkelä made changes -
            Attachment MDEV-20605.patch [ 49204 ]
            marko Marko Mäkelä made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Assignee Andrei Elkin [ elkin ] Matthias Leich [ mleich ]
            marko Marko Mäkelä made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Assignee Matthias Leich [ mleich ] Thirunarayanan Balathandayuthapani [ thiru ]
            mleich Matthias Leich made changes -
            Attachment thiru_20605.yy [ 49219 ]
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.4 [ 22408 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Lesin [ vlad.lesin ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Julien Fritsch [ julien.fritsch ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Julien Fritsch [ julien.fritsch ] Vladislav Lesin [ vlad.lesin ]
            julien.fritsch Julien Fritsch made changes -
            Rank Ranked higher
            vlad.lesin Vladislav Lesin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            julien.fritsch Julien Fritsch made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Julien Fritsch [ julien.fritsch ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Julien Fritsch [ julien.fritsch ] Vladislav Lesin [ vlad.lesin ]
            marko Marko Mäkelä made changes -
            manjot Manjot Singh (Inactive) made changes -
            Labels AssistToday
            GeoffMontee Geoff Montee (Inactive) made changes -
            GeoffMontee Geoff Montee (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Labels AssistToday
            Elkin Andrei Elkin made changes -
            marko Marko Mäkelä made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Marko Mäkelä [ marko ]
            julien.fritsch Julien Fritsch made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Lesin [ vlad.lesin ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.5 [ 23123 ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            Labels ServiceNow
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            Labels ServiceNow 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Labels 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            Elkin Andrei Elkin made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-restore-cursor.test [ 60381 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment row0upd.cc.diff [ 60406 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-deadlock.test [ 60407 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-deadlock.test [ 60407 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-deadlock.test [ 60412 ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-deadlock.test [ 60412 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-deadlock.test [ 60437 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment row0upd.cc.diff [ 60406 ]
            vlad.lesin Vladislav Lesin made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-restore-cursor-v2.test [ 60741 ]
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-locking.test [ 60742 ]
            serg Sergei Golubchik made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 99695 ] MariaDB v4 [ 144523 ]
            manjot Manjot Singh (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            julien.fritsch Julien Fritsch made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            vlad.lesin Vladislav Lesin made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            vlad.lesin Vladislav Lesin made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Matthias Leich [ mleich ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            mleich Matthias Leich made changes -
            Assignee Matthias Leich [ mleich ] Vladislav Lesin [ vlad.lesin ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            vlad.lesin Vladislav Lesin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            vlad.lesin Vladislav Lesin made changes -
            Summary With optimistic and aggressive modes of parallel replication, some replicated statements have no effect Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration
            vlad.lesin Vladislav Lesin made changes -
            Component/s Replication [ 10100 ]
            vlad.lesin Vladislav Lesin made changes -
            Fix Version/s 10.5.16 [ 27508 ]
            Fix Version/s 10.6.8 [ 27506 ]
            Fix Version/s 10.7.4 [ 27504 ]
            Fix Version/s 10.8.3 [ 27502 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            Fix Version/s 10.3.35 [ 27512 ]
            Fix Version/s 10.4.25 [ 27510 ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            alice Alice Sherepa made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            vlad.lesin Vladislav Lesin made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201658 136328 160775 151737
            Zendesk active tickets 201658
            vlad.lesin Vladislav Lesin made changes -
            Attachment MDEV-20605-mysql.test [ 74058 ]
            midenok Aleksey Midenkov made changes -

            People

              vlad.lesin Vladislav Lesin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              5 Vote for this issue
              Watchers:
              21 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.