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

Enabling gtid_ignore_duplicates without GTID causes rows to be skipped when stop/starting slave

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0
    • Fix Version/s: N/A
    • Component/s: Replication
    • Labels:
      None

      Description

      Stopping and starting the slave configured to use file/position replication and having gtid_ignore_duplicates enabled, will cause events not to be applied.

      GTID replication does not seem to be affected.

      Test case:

      CREATE TABLE `tbl` (
      `Id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
      `InsertDate` datetime DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`Id`)
      ) ;
       
      DROP PROCEDURE IF EXISTS usp_insert_data;
      DELIMITER //
      create PROCEDURE usp_insert_data()
      begin
      declare cnt int;
      set cnt=1;
       
      SET @@auto_increment_increment=1;
      SET @@auto_increment_offset=1;
       
      truncate table test.tbl;
       
      while cnt < 100000
      do
      insert into test.tbl(name) select 'aaa';
      set cnt=cnt+1;
      end while;
      end//
      DELIMITER ;
       
      CALL usp_insert_data;
      

      On the slave, while the procedure is running on the master, we can see this:

      MariaDB [test]> SELECT SLEEP(5) INTO @z; SELECT COUNT(*) AS "Starting..." FROM tbl; SELECT SLEEP(5) INTO @z; SELECT COUNT(*) AS "After 5 seconds" FROM tbl; STOP SLAVE; SELECT COUNT(*) AS "After STOPSLAVE" FROM tbl; SELECT SLEEP(15) INTO @z; START SLAVE; SELECT COUNT(*) AS "After STARTSLAVE" FROM tbl; 
      Query OK, 1 row affected (5.00 sec)
       
      +-------------+
      | Starting... |
      +-------------+
      |       12024 |
      +-------------+
      1 row in set (0.01 sec)
       
      Query OK, 1 row affected (5.00 sec)
       
      +-----------------+
      | After 5 seconds |
      +-----------------+
      |           20575 |
      +-----------------+
      1 row in set (0.00 sec)
       
      Query OK, 0 rows affected (0.03 sec)
       
      +-----------------+
      | After STOPSLAVE |
      +-----------------+
      |           20581 |
      +-----------------+
      1 row in set (0.00 sec)
       
      Query OK, 1 row affected (15.00 sec)
       
      Query OK, 0 rows affected (0.00 sec)
       
      +------------------+
      | After STARTSLAVE |
      +------------------+
      |            20581 |
      +------------------+
      1 row in set (0.01 sec)
       
      MariaDB [test]> SELECT COUNT(*) AS totalMissing, MIN(missing.seq) AS start, MAX(missing.seq) AS end FROM seq_1_to_99999 AS missing LEFT JOIN tbl ON missing.seq = tbl.id WHERE tbl.id IS NULL;
       
      +--------------+-------+-------+
      | totalMissing | start | end   |
      +--------------+-------+-------+
      |         7889 | 22423 | 30311 |
      +--------------+-------+-------+
      1 row in set (0.09 sec)
       
      MariaDB [test]> 
      

      The relay logs seem to have all the events:

      [root@fileposrepl slave]# mysqlbinlog relay.000004 | grep "SET INSERT_ID" | wc -l
      30311
      [root@fileposrepl slave]# mysqlbinlog relay.000005 | grep "SET INSERT_ID" | wc -l
      69688
      [root@fileposrepl slave]#
      

      The workaround seems to just disable gtid_ignore_duplicates:

      MariaDB [test]> SELECT @@gtid_ignore_duplicates;                                                                                                                                      
      +--------------------------+
      | @@gtid_ignore_duplicates |
      +--------------------------+
      |                        0 |
      +--------------------------+
      1 row in set (0.00 sec)
      MariaDB [test]> SELECT COUNT(*) FROM tbl;
      +----------+
      | COUNT(*) |
      +----------+
      |    99999 |
      +----------+
      1 row in set (0.03 sec)
       
      MariaDB [test]> 
      

      Thanks!

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              plinux Lixun Peng
              Reporter:
              wfong Will Fong
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: