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

gtid_ignore_duplicates incorrectly ignores statements even when GTID replication is not enabled

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.0.31
    • Replication
    • None

    Description

      Hi!

      gtid_ignore_duplicates ignores statements even when GTID replication is not enabled. I can reproduce it and I can give you access to the VM's where I can reproduce it.

      It seams that duplicate STATEMENTS always get ignored and unique STATEMENTS only get ignored when the slave gets restarted.

      master config:

      server_id=1
      log-bin
      replicate_ignore_db=mysql
      binlog_format=STATEMENT
      

      Slave config:

      [mysqld]
      server_id=2
      gtid_ignore_duplicates=1
      log-slave-updates
      log-bin
      

      Steps to reproduce:
      1. Setup replication between two nodes

      2. Create 10 test tables and insert data into them:

      CREATE TABLE `test1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `b` int(10) unsigned NOT NULL,
        `c` char(50) NOT NULL,
        UNIQUE KEY `id` (`id`)
      ) ENGINE=MyISAM AUTO_INCREMENT=3580584 DEFAULT CHARSET=latin1;
      CREATE TABLE test2 LIKE test1;
      CREATE TABLE test3 LIKE test1;
      CREATE TABLE test4 LIKE test1;
      CREATE TABLE test5 LIKE test1;
      CREATE TABLE test6 LIKE test1;
      CREATE TABLE test7 LIKE test1;
      CREATE TABLE test8 LIKE test1;
      CREATE TABLE test9 LIKE test1;
      CREATE TABLE test10 LIKE test1;
      insert into test1 (b) values(1);
      insert into test2 (b) values(1);
      insert into test3 (b) values(1);
      insert into test4 (b) values(1);
      insert into test5 (b) values(1);
      insert into test6 (b) values(1);
      insert into test7 (b) values(1);
      insert into test8 (b) values(1);
      insert into test9 (b) values(1);
      insert into test10 (b) values(1);
      

      3. Put script.sh and start.sh

      [root@test1 ~]# cat script.sh 
      #!/bin/bash
       
      DATE=`date`
      while true; do mysql mariadb -e "INSERT INTO mariadb.$1 (b, c) select RAND()*1000, \"$RANDOM $RANDOM $RANDOM $RANDOM $DATE $RANDOM\" FROM mariadb.$1 LIMIT 10000"; done
      

      #!/bin/bash
       
      ./script.sh test1&
      ./script.sh test1&
      ./script.sh test1&
      ./script.sh test1&
      ./script.sh test1&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test2&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test3&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test4&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test5&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test6&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test7&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test8&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test9&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      ./script.sh test10&
      

      4. Run start.sh

      5. While this is running, start this script to restart the slave every minute:

      while true; do mysql  -e 'stop slave; set global slave_parallel_threads = 4 - @@slave_parallel_threads; start slave;'; date >> log3.log; for i in {0..10}; do ( echo -n $(date); mysql  -e 'show slave status\G' | grep Seconds ) >> log3.log; sleep 2; done; done&
      

      I am not sure if changing slave_parallel_threads has anything to do with it. I just realize it is still there after creating this report.

      6. Observer the replication gets out of sync:

      MariaDB [mariadb]> select id FROM test7 order by id desc limit 1;
      +----------+
      | id       |
      +----------+
      | 11616384 |
      +----------+
      1 row in set (0.00 sec)
      MariaDB [mariadb]> select count(*) FROM test7 where id <= 11616384 order by id desc limit 1;
      +----------+
      | count(*) |
      +----------+
      | 10606384 |
      +----------+
      1 row in set (7.02 sec)
      

      On master:

      MariaDB [mariadb]> select count(*) FROM test7 where id <= 11616384 order by id desc limit 1;
      +----------+
      | count(*) |
      +----------+
      | 11616384 |
      +----------+
      1 row in set (1 min 13.63 sec)
      

      Attachments

        Issue Links

          Activity

            People

              plinux Lixun Peng
              michaeldg Michaël de groot
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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