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

gtid_slave_pos duplicate key errors after restore

    Details

      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

            Activity

              People

              • Assignee:
                Elkin Andrei Elkin
                Reporter:
                hholzgra Hartmut Holzgraefe
              • Votes:
                3 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: