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

xtrabackup_binlog_info (10.6) or mariadb_backup_binlog_info (11.4) gets the sequence number incorrect when active table is ENGINE=ARIA

Details

    Description

      Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (consistent)

      • You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
      • The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
      • If inserts on the master are slowed down to one per second, this failure does not occur and checksum table shows that tables are perfect copies.
      • If the table is ENGINE=INNODB the failure will not occur.

      On master, create a table:

      Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
      		 
      DROP TABLE IF EXISTS `aria_table`;
      		 
      CREATE TABLE `aria_table` (
      		 
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
      		 
        `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
      		 
      ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;

      On master, run a bash script that quickly inserts into the aria_table:

      #!/bin/bash
      		 
      touch t.txt
      		 
      mariadb -Ae "truncate table Rpa.aria_table;"
      		 
      ii=0
      		 
      while [ -f t.txt ]; do
      		 
       ii=$(( $ii + 1 ))
      		 
        mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
      		 
      echo $ii
      		 
      done
      		 
      echo "finished"

      Verify that db2 is a slave of db1 and that the slave is running.

      On db3, stop server, destroy the data:

      systemctl stop mariadb
      		 
      datadir=/var/lib/mysql
      		 
      logdir=/var/log/mysql 
      rm -fr $datadir/*  $logdir/*

      On db2, use mariabackup to stream a backup into the datadir of db3:

      datadir=/var/lib/mysql  
      replica=192.168.8.113
      		 
      mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream  -x"

      On db3, set the replication to db1

      mhost=db1.edw.ee 
      datadir=/var/lib/mysql
      		 
      logdir=/var/log/mysql
      		 
      mport=3306
      		 
      mpw=password
      		 
      muser=repl
      		 
       
      		 
      # Test the connectivity from this replica node to the primary:
      		 
      mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"
      		 
       
      		 
      cd $datadir
      		 
       
      		 
      gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
      		 
      echo $gtid $mhost $mport $muser $mpw
      		 
       
      		 
      mariabackup --prepare --target-dir=$datadir
      		 
       
      		 
      chown -R mysql:mysql $datadir
      		 
      chown -R mysql:mysql $logdir
      		 
       
      		 
      systemctl start mariadb;
      		 
       
      		 
      mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"
      		 
       
      		 
      mariadb -Ae "show replica status\G"

      ERROR OCCURS!

      Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          edward Edward Stoever created issue -
          edward Edward Stoever made changes -
          Field Original Value New Value
          Affects Version/s 11.4.5 [ 29956 ]
          Affects Version/s 10.6.19 [ 29833 ]
          edward Edward Stoever made changes -
          Description Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (only occured one out of 6 attempts)
          * The chance of reproducing this with 10.6.19 is very high, whereas the chance of reproducing this with 11.4 is low but possible.
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and `checksum table` shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=InnoDB AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          		Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (only occured one out of 6 attempts)
          * The chance of reproducing this with 10.6.19 is very high, whereas the chance of reproducing this with 11.4 is low but possible.
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=InnoDB AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          edward Edward Stoever made changes -
          Description Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (only occured one out of 6 attempts)
          * The chance of reproducing this with 10.6.19 is very high, whereas the chance of reproducing this with 11.4 is low but possible.
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=InnoDB AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          		Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (only occured one out of 6 attempts)
          * The chance of reproducing this with 10.6.19 is very high, whereas the chance of reproducing this with 11.4 is low but possible.
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          elenst Elena Stepanova made changes -
          Component/s mariabackup [ 14500 ]
          Component/s Replication [ 10100 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 11.4 [ 29301 ]
          Assignee Susil Behera [ JIRAUSER40751 ]
          edward Edward Stoever made changes -
          Description Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (only occured one out of 6 attempts)
          * The chance of reproducing this with 10.6.19 is very high, whereas the chance of reproducing this with 11.4 is low but possible.
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          		Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (consistent)
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          edward Edward Stoever made changes -
          Description Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (consistent)
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this error does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          		Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (consistent)
          * You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
          * The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
          * If inserts on the master are slowed down to one per second, this failure does not occur and *checksum table* shows that tables are perfect copies.
          * If the table is ENGINE=INNODB the failure will not occur.

          On master, create a table:
          {NOFORMAT}
          Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
          DROP TABLE IF EXISTS `aria_table`;
          CREATE TABLE `aria_table` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
            `processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
          ) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
          {NOFORMAT}
          On master, run a bash script that quickly inserts into the aria_table:
          {NOFORMAT}
          #!/bin/bash
          touch t.txt
          mariadb -Ae "truncate table Rpa.aria_table;"
          ii=0
          while [ -f t.txt ]; do
           ii=$(( $ii + 1 ))
            mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
          echo $ii
          done
          echo "finished"
          {NOFORMAT}
          Verify that db2 is a slave of db1 and that the slave is running.

          On db3, stop server, destroy the data:
          {NOFORMAT}
          systemctl stop mariadb
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          rm -fr $datadir/* $logdir/*
          {NOFORMAT}
          On db2, use mariabackup to stream a backup into the datadir of db3:
          {NOFORMAT}
          datadir=/var/lib/mysql
          replica=192.168.8.113
          mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
          {NOFORMAT}
          On db3, set the replication to db1
          {NOFORMAT}
          mhost=db1.edw.ee
          datadir=/var/lib/mysql
          logdir=/var/log/mysql
          mport=3306
          mpw=password
          muser=repl

          # Test the connectivity from this replica node to the primary:
          mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"

          cd $datadir

          gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
          echo $gtid $mhost $mport $muser $mpw

          mariabackup --prepare --target-dir=$datadir

          chown -R mysql:mysql $datadir
          chown -R mysql:mysql $logdir

          systemctl start mariadb;

          mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"

          mariadb -Ae "show replica status\G"
          {NOFORMAT}
          ERROR OCCURS!

          {NOFORMAT}
          Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
          {NOFORMAT}
          julien.fritsch Julien Fritsch made changes -
          julien.fritsch Julien Fritsch made changes -
          susil.behera Susil Behera made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          susil.behera Susil Behera made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          susil.behera Susil Behera made changes -
          Assignee Susil Behera [ JIRAUSER40751 ] Julien Fritsch [ julien.fritsch ]
          susil.behera Susil Behera made changes -
          Assignee Julien Fritsch [ julien.fritsch ] Brandon Nesterenko [ JIRAUSER48702 ]
          julien.fritsch Julien Fritsch made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          bnestere Brandon Nesterenko made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          bnestere Brandon Nesterenko made changes -
          Labels Sprint

          People

            bnestere Brandon Nesterenko
            edward Edward Stoever
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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