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)