Details
Description
*** This is a refined description, the original one follows it ***
When mysqldump run on mysql system database it generates inserts sql commands
into mysql.gtid_slave_pos.
There're a couple of issues with that fact when the script is going to provision
a new slave instance. That is when mysqldump runs with --dump-slave
1. without --gtid option the script misses out
SET @@global.gtid_slave_pos = <value>
In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl.
rpl_global_gtid_slave_state.last_sub_id
When replication begins this value may start off 1 or be derived from a
pre-existing record of the table to eventually reach
a possibly greater inserted value of
mysql.gtid_slave_pos.sub_id
column which is a reason of a duplicate key error described specifically in a scenario of the original description below.
2. even with --gtid the SET statement is placed in the script before the mysql.gtid_slave_pos inserts block, so an accumulative effect of replying
the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record).
While both effects can also be observed if a "genuine" strongly unrecommended manual insert is done into the table, to my view that merits a separate work in MDEV-34564.
Clearly a mere relocating of
SET @@global.gtid_slave_pos = <value>
onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one apparently requires more efforts that it deemed and thus is deferred to MDEV-34564 Specifically It is not sufficient to chose <value> to be @@global.gtid_slave_pos itself as such measure would purge the old content of table that is to risk losing a pre-existing state.
The aimed output therefore should be like in the following block:
LOCK TABLES `gtid_slave_pos` WRITE;
|
/*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; |
INSERT INTO `gtid_slave_pos` VALUES |
(0,66,1,1),
|
...
|
(0,85,1,20)
|
/*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; |
UNLOCK TABLES;
|
|
|
-- without ---gtid
|
-- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos;
|
SET GLOBAL gtid_slave_pos='0-1-20'; |
The last SET statement ensures that the mysqldump script sets
rpl_global_gtid_slave_state.last_sub_id
to the max of mysql.gtid_slave_pos.sub_id in the table and by that rules out duplicate key error possibility.
*** The original description ***
When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error.
How to reproduce:
- set up a master, insert a "fake" entry into gtid_slave_pos:
INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23);
|
- take a master backup
mysqldump --all-databases --single-transaction --master-data > dump.sql
|
- set up slave, set up replication basics:
CHANGE MASTER TO MASTER_HOST='...'
|
, MASTER_USER='...'
|
, MASTER_PASSWORD='...';
|
- restore dump on slave
- start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there:
+-----------+--------+-----------+--------+
|
| domain_id | sub_id | server_id | seq_no |
|
+-----------+--------+-----------+--------+
|
| 0 | 1 | 2 | 5 |
|
| 0 | 5 | 1 | 23 |
|
+-----------+--------+-----------+--------+
|
- perform some DDL or DML statements on the master
- check slave status:
Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY'
|
The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on
CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
|
Proposed fixes:
TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output
Attachments
Issue Links
- causes
-
MDEV-35098 rpl.rpl_mysqldump_gtid_slave_pos fails in buildbot
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-34615 mysqldump wipes off pre-existing gtid slave state
-
- Stalled
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Andrei Elkin [ elkin ] |
| Link |
This issue is blocked by |
| Link |
This issue is blocked by |
| Link |
This issue relates to |
Sandy It's difficult to say what happened to your slave. Make sure to upload its configuration, global var:s and status reports as well. Also to your report we've analyzed MDEV-19157 which has some similarity with your error log snippet.
| Labels | backup gtid slave |
| Workflow | MariaDB v3 [ 85713 ] | MariaDB v4 [ 140665 ] |
| Zendesk Related Tickets | 117005 |
| Status | Open [ 1 ] | In Progress [ 3 ] |
vigneswara.bandi thanks for an expanded report. I could reproduce the issue. I am fixing its cause at the moment.
The table would not need to be dumped at all if mysqldump were invoked with --gtid. So that's a workaround. Another one is
to compute SELECT max(seq_no) from mysql.gtid_slave_pos and use it to compose and declare the server's slave state.
Afterward the table can be truncate-d (or its content delete-d) prior to START SLAVE.
In any case just START SLAVE after the error is sufficient to leap over it.
| Description |
When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error.
How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_This is a refined description, the original one follows it up._
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the mysql.gtid_slave_pos inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert| ] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_This is a refined description, the original one follows it up._
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the mysql.gtid_slave_pos inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert| ] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _***The original description***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _***The original description***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described in a scenario below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (the role of SET would indeed involve a pre-existing record creation). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
bnestere, knielsen - fyi the description is edited with a detailed analysis and how-to-fix suggestion. Could you please review that before the coding starts.
It sounds fine to put the SET GLOBAL gtid_slave_pos to the end of the mysqldump --gtid output.
It doesn't seem correct to output a SET GLOBAL gtid_slave_pos statement if --gtid is not specified by the user, as that will destroy the contents of @@GLOBAL.gtid_slave_pos, which would be very bad. It would be like including a table/database into the dump that the user did not ask for.
One could think about including the SET GLOBAL gtid_slave_pos if mysql.gtid_slave_pos is included in the dump. Note that there can be multiple different mysql.gtid_slave_pos* tables in the server, so a match would be needed. Maybe then it's as simple, and more robust, to handle the issue in the MDEV-34564.
I guess at the root of the problem is that the internal in-memory copy of the table data in the rpl_global_gtid_slave_state hash become out-of-sync with the sql table contents. Maybe at slave start, the code could check the table contents against the hash contents, and resync (with a warning in the error log) in case of differences? But I guess that's the idea in MDEV-34564.
Thanks, knielsen!
I have to agree with that when --gtid not specified, but --dump-slave is and mysql.gtid_slave_pos is in the dump a chance still exists that the pre-existing on the new slave @@global.gtid_slave_pos should remain from some user's point of view. So I am adjusting the description in this part.
To make it clear the multiple mysql.gtid_slave_pos* is not a concern now. The SET is going to be placed by mysqldump --dump-slave --gtid after all databases/tables are put out. So it and ways to discover desynchronization is left to MDEV-34564.
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, it must be also apparent that the only reasonable solution is to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, -it must be also apparent that the only reasonable solution is- it might be possible to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}, but that would risk wiping off a pre-existing {{@@global.gtid_slave_pos}} that a user might opt for to stay. Thus p.1 is not going to be fixed here and the user would have to execute {{SET @@global.gtid_slave_pos = <value>}} manually after the dump restoration prior to turn ON replication. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it up ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one, -it must be also apparent that the only reasonable solution is- it might be possible to enforce {{SET @@global.gtid_slave_pos = <value>}} into the script regardless of {{--gtid}}, but that would risk wiping off a pre-existing {{@@global.gtid_slave_pos}} that a user might opt for to stay. Thus p.1 is not going to be fixed here and the user would have to execute {{SET @@global.gtid_slave_pos = <value>}} manually after the dump restoration prior to turn ON replication. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one it is sufficient to chose {{<value>} to be {{@@global.gtid_slave_pos}} itself. That would synchronize the maximum {{sub_id}} while keep intact the current {{gtid_slave_pos}} value. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; -- without ---gtid -- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Description |
_*** This is a refined description, the original one follows it ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one it is sufficient to chose {{<value>} to be {{@@global.gtid_slave_pos}} itself. That would synchronize the maximum {{sub_id}} while keep intact the current {{gtid_slave_pos}} value. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; -- without ---gtid -- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one it is sufficient to chose {{<value>}} to be {{@@global.gtid_slave_pos}} itself. That would synchronize the maximum {{sub_id}} while keep intact the current {{gtid_slave_pos}} value. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; -- without ---gtid -- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Summary | gtid_slave_pos duplicate key errors after restore | gtid_slave_pos duplicate key errors after mysqldump restore |
| Link | This issue relates to MDEV-34615 [ MDEV-34615 ] |
Howdy Brandon!
Could you please check bundled fixes to this and MDEV-34615, currently in bb-10.5-andrei.
The patch only attends --gtid mode mysqldump as discussed with knielsen.
| Assignee | Andrei Elkin [ elkin ] | Brandon Nesterenko [ JIRAUSER48702 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Description |
_*** This is a refined description, the original one follows it ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one it is sufficient to chose {{<value>}} to be {{@@global.gtid_slave_pos}} itself. That would synchronize the maximum {{sub_id}} while keep intact the current {{gtid_slave_pos}} value. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; -- without ---gtid -- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
_*** This is a refined description, the original one follows it ***_
When mysqldump run on mysql system database it generates inserts sql commands into {{mysql.gtid_slave_pos}}. There're a couple of issues with that fact when the script is going to provision a new slave instance. That is when mysqldump runs with {{--dump-slave}} 1. without {{--gtid}} option the script misses out {{SET @@global.gtid_slave_pos = <value>}} In effect the script, that is to run after the new slave server has been initialized, can not update internal state of slave server objects incl. {{rpl_global_gtid_slave_state.last_sub_id}} When replication begins this value may start off 1 or be derived from a pre-existing record of the table to eventually reach a possibly greater *inserted* value of {{mysql.gtid_slave_pos.sub_id}} column which is a reason of a duplicate key error described specifically in a scenario of the original description below. 2. even with {{--gtid}} the SET statement is placed in the script before the {{mysql.gtid_slave_pos}} inserts block, so an accumulative effect of replying the two on the new slave is similar to p.1 (SET would indeed act as to create a pre-existing record). While both effects can also be observed if a "genuine" [strongly unrecommended manual insert|https://mariadb.com/kb/en/mysqlgtid_slave_pos-table] is done into the table, to my view that merits a separate work in MDEV-34564. Clearly a mere relocating of {{SET @@global.gtid_slave_pos = <value>}} onto a position after the insert block sorts out the 2nd mysqldump issue. To tackle the 1st one apparently requires more efforts that it deemed and thus is deferred to MDEV-34564 ^Specifically It is not sufficient to chose {{<value>}} to be {{@@global.gtid_slave_pos}} itself as such measure would purge the old content of table that is to risk losing a pre-existing state^. The aimed output therefore should be like in the following block: {code:sql} LOCK TABLES `gtid_slave_pos` WRITE; /*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */; INSERT INTO `gtid_slave_pos` VALUES (0,66,1,1), ... (0,85,1,20) /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */; UNLOCK TABLES; -- without ---gtid -- SET GLOBAL gtid_slave_pos = @@global.gtid_slave_pos; SET GLOBAL gtid_slave_pos='0-1-20'; {code} The last SET statement ensures that the mysqldump script sets {{rpl_global_gtid_slave_state.last_sub_id}} to the max of {{mysql.gtid_slave_pos.sub_id}} in the table and by that rules out duplicate key error possibility. _*** The original description ***_ When restoring a master backup where, for whatever reason, gtid_slave_pos contains entries (master<->master setup, intermediate slave, former slave promoted to master), and using classic master_log_file/pos to set up replication, a newly set up slave will run for a while, counting up its on "sub_id" from one, until reaching the masters "sub_id" values still present in the restored gtid_slave_pos. At that point replication will stop with a duplicate key error. How to reproduce: * set up a master, insert a "fake" entry into gtid_slave_pos: {noformat} INSERT INTO mysql.gtid_slave_pos VALUES(0,5,1,23); {noformat} * take a master backup {noformat} mysqldump --all-databases --single-transaction --master-data > dump.sql {noformat} * set up slave, set up replication basics: {noformat} CHANGE MASTER TO MASTER_HOST='...' , MASTER_USER='...' , MASTER_PASSWORD='...'; {noformat} * restore dump on slave * start slave, check slave status to see that things are running, check mysql.gtid_slave_pos contents. Note the first line inserted by the slave, and the 2nd one originating from the master still being there: {noformat} +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 1 | 2 | 5 | | 0 | 5 | 1 | 23 | +-----------+--------+-----------+--------+ {noformat} * perform some DDL or DML statements on the master * check slave status: {noformat} Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-5' for key 'PRIMARY' {noformat} The gtid_slave_pos table is truncated when setting the gtid_slave_pos variable with SET GLOBAL, but is left as is on {noformat} CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; {noformat} Proposed fixes: TRUNCATE the table on explicit CHANGE MASTER TO MASTER_LOG_FILE, and move the CHANGE MASTER TO command from beginning to end of mysqldump output |
| Fix Version/s | 10.5.26 [ 29832 ] | |
| Fix Version/s | 10.6.19 [ 29833 ] | |
| Fix Version/s | 10.11.9 [ 29834 ] | |
| Fix Version/s | 11.1.6 [ 29835 ] | |
| Fix Version/s | 11.2.5 [ 29836 ] | |
| Fix Version/s | 11.4.3 [ 29837 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Assignee | Brandon Nesterenko [ JIRAUSER48702 ] | Andrei Elkin [ elkin ] |
vigneswara.bandi, this bug is fixed for the case of mysqlbinlog --gtid ... more options .... No --gtid coverage can a part of more general MDEV-34564 Saying that with acknowledgement of the requirement of --gtid is somewhat unnatural for a master_use_gtid = no replication setup.
| Link |
This issue causes |
same error, but in a different situation.
we use version 10.1.22, when this error happened, we run stop slave and start slave after a while. the error will be healed. But we don't know how to reproduce this.
This is the error log .
2019-02-21 12:50:18 139769347844864 [ERROR] Slave SQL: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-194031977'
for key 'PRIMARY', Gtid 0-16253440-388064147, Internal MariaDB error code: 1942
2019-02-21 12:50:18 139769347844864 [ERROR] Slave (additional info): Duplicate entry '0-194031977' for key 'PRIMARY' Error_code: 1062
2019-02-21 12:50:18 139769347844864 [Warning] Slave: Duplicate entry '0-194031977' for key 'PRIMARY' Error_code: 1062
2019-02-21 12:50:18 139769347844864 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stop
ped at log 'mysql-bin.000085' position 202964878
2019-02-21 12:50:18 139769349663488 [Note] Error reading relay log event: slave SQL thread was killed
2019-02-21 12:50:18 139769349663488 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.000085' at position 202964878
2019-02-22 18:02:37 139769349360384 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000085', position 461759256
2019-02-22 18:04:13 139769349663488 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000085' at position 202964878, relay log '/mnt/storage00
/mysql/3440/bin_log/mysql-relay-log.000106' position: 202965166
2019-02-22 18:04:13 139769347844864 [ERROR] Slave SQL: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-194031978'
for key 'PRIMARY', Gtid 0-16253440-388064147, Internal MariaDB error code: 1942
2019-02-22 18:04:13 139769347844864 [ERROR] Slave (additional info): Duplicate entry '0-194031978' for key 'PRIMARY' Error_code: 1062
2019-02-22 18:04:13 139769347844864 [Warning] Slave: Duplicate entry '0-194031978' for key 'PRIMARY' Error_code: 1062
2019-02-22 18:04:13 139769347844864 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stop
ped at log 'mysql-bin.000085' position 202964878
2019-02-22 18:04:13 139769348754176 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-22 18:04:13 139769348754176 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-22 18:04:13 139769348754176 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stop
ped at log 'mysql-bin.000085' position 202964878
2019-02-22 18:04:13 139769347238656 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-22 18:04:13 139769347238656 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2019-02-22 18:04:13 139769347238656 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stop
ped at log 'mysql-bin.000085' position 202964878
2019-02-22 18:04:13 139769349663488 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.000085' at position 202964878
2019-02-22 18:04:13 139769349360384 [Note] Slave I/O thread: connected to master 'repl@172.22.16.39:3440',replication started in log 'mysql-bin.000085' at position 4617
59256
2019-02-22 18:04:19 139769349360384 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000085', position 462118255
2019-02-22 18:04:26 139769347844864 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000085' at position 202964878, relay log '/3440/bin_log/mysql-relay-log.000106' position: 202965166
2019-02-22 18:04:26 139769349360384 [Note] Slave I/O thread: connected to master 'repl@IP:3440',replication started in log 'mysql-bin.000085' at position 4621
18255