MariaDB/MySQL replication error – Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos

When in aggressive parallel mode MariaDB/MySQL replication could fail with:

Last_Errno: 1942
Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-46158188501' for key 'PRIMARY'

This table is used for tracking the replication process and you might probably just do:

STOP/START SLAVE i.e. restart the replication and it would continue without errors.

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Optimistic or aggressive mode runs conflicting transactions in parallel and it sometimes happens to roll back. In our case probably something happened and the rollback failed and STOP/START saved the replication.

* Additional thoughts

If you try STOP/START and you get the same error, probably it worth trying truncating the table “mysql.gtid_slave_pos” if you do not use GTID Replication feature (the “show slave status” says “Using_Gtid: No”). And even if you use “Using_Gtid: No” you could probably always stop the replication, “change master” to use the old style and start again? Probably switching off the aggressive mode might help, too!

Here is the error log:

2019-02-02 12:45:02 140363398740736 [ERROR] Slave SQL: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-46158188501' for key 'PRIMARY', Gtid 0-104-86877003094, Internal MariaDB error code: 1942
2019-02-02 12:45:02 140363398740736 [ERROR] Slave (additional info): Duplicate entry '0-46158188501' for key 'PRIMARY' Error_code: 1062
2019-02-02 12:45:02 140363398740736 [Warning] Slave: Duplicate entry '0-46158188501' for key 'PRIMARY' Error_code: 1062
2019-02-02 12:45:02 140363398740736 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.1436028' position 19381551
2019-02-02 12:45:02 140376078732032 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376078732032 [Warning] Slave: Connection was killed Error_code: 1927
2019-02-02 12:45:02 140376078732032 [Warning] Slave: Deadlock found when trying to get lock; try restarting transaction Error_code: 1213
2019-02-02 12:45:02 140376078732032 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376078732032 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.1436028' position 19381551
2019-02-02 12:45:02 140376078428928 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376078428928 [Warning] Slave: Connection was killed Error_code: 1927
2019-02-02 12:45:02 140376078428928 [Warning] Slave: Deadlock found when trying to get lock; try restarting transaction Error_code: 1213
2019-02-02 12:45:02 140376078428928 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376078428928 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.1436028' position 19381551
2019-02-02 12:45:02 140376079944448 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376079944448 [Warning] Slave: Connection was killed Error_code: 1927
2019-02-02 12:45:02 140376079944448 [Warning] Slave: Deadlock found when trying to get lock; try restarting transaction Error_code: 1213
2019-02-02 12:45:02 140376079944448 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-02 12:45:02 140376079944448 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.1436028' position 19381551

What are these errors

myuser@srv ~ # perror 1942
MySQL error code 1942 (ER_CANNOT_UPDATE_GTID_STATE): Could not update replication slave gtid state
myuser@srv ~ # perror 1062
MySQL error code 1062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d

Our real output

You see the error and after the STOP/START it was gone and several hours after that it advanced and the “seconds behind master” were 0.

myuser@srv ~ # mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1081379
Server version: 10.1.23-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.10.10.10
                  Master_User: repusr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.1459337
          Read_Master_Log_Pos: 1333999
               Relay_Log_File: mysqld-relay-bin.020810
                Relay_Log_Pos: 5140592
        Relay_Master_Log_File: mysql-bin.1436028
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1942
                   Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-46158188501' for key 'PRIMARY'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19381551
              Relay_Log_Space: 1063565586153
              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: 1942
               Last_SQL_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-46158188501' for key 'PRIMARY'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: aggressive
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.10
                  Master_User: repusr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.1459337
          Read_Master_Log_Pos: 100176752
               Relay_Log_File: mysqld-relay-bin.020810
                Relay_Log_Pos: 10697273
        Relay_Master_Log_File: mysql-bin.1436028
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           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: 42204206
              Relay_Log_Space: 1063624181711
              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: 373785
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: 101
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: aggressive
1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *