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

gtid_slave_pos duplicate key errors after mysqldump restore




      *** 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.
      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
      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
      /*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */;
      -- 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
      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:

                      , 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


      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


        Issue Links



              Elkin Andrei Elkin
              hholzgra Hartmut Holzgraefe
              5 Vote for this issue
              14 Start watching this issue



                Git Integration

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