Uploaded image for project: 'Percona XtraBackup'
  1. Percona XtraBackup
  2. PXB-2239

Partitioned table is not restored correctly when partitions are changed during backup

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 2.4.20
    • 2.4.21
    • None
    • None
    • Yes
    • No

    Description

      Initialize and start MS5.7.31 as

      /home/mchawla/MS_5_7_31/bin/mysqld --no-defaults --core-file --basedir=/home/mchawla/MS_5_7_31 --tmpdir=/home/mchawla/MS_5_7_31/data --datadir=/home/mchawla/MS_5_7_31/data --socket=/home/mchawla/MS_5_7_31/socket.sock --port=14445 --log-error=/home/mchawla/MS_5_7_31/log/master.err --server-id=100 --log-bin=binlog

      Create 10 sysbench tables
      Create 3 partitioned tables and change the partitions

      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP TABLE sbtest1; DROP TABLE sbtest2; DROP TABLE sbtest3;" test   
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE sbtest1 (id int NOT NULL AUTO_INCREMENT, k int NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) PARTITION BY HASH(id) PARTITIONS 10;" test   
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE sbtest2 (id int NOT NULL AUTO_INCREMENT, k int NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (500), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN MAXVALUE);" test   
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE sbtest3 (id int NOT NULL AUTO_INCREMENT, k int NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) PARTITION BY KEY() PARTITIONS 5;" test
      sysbench /usr/share/sysbench/oltp_insert.lua --tables=3 --mysql-db=test --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=5 run >/dev/null 2>&1       
      ( for ((i=1; i<=10; i++)); do       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 COALESCE PARTITION 5;"       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 PARTITION BY HASH(id) PARTITIONS 10;"   
      done ) &    
      ( for ((i=1; i<=10; i++)); do       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 DROP PARTITION p2;"       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 ADD PARTITION (PARTITION p2 VALUES LESS THAN MAXVALUE);"   
      done ) &
      ( for ((i=1; i<=10; i++)); do       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest3 REBUILD PARTITION p0, p1;"       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest3 OPTIMIZE PARTITION p2;"       
      ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest3 ANALYZE PARTITION p3,p4;"   
      done ) &

      Take full backup with lock-ddl-per-table

      /home/mchawla/pxb_2_4_21_debug/bin/xtrabackup --user=root --password= --backup --target-dir=/home/mchawla/dbbackup_27_07_2020/full -S /home/mchawla/MS_5_7_31/socket.sock --datadir=/home/mchawla/MS_5_7_31/data --lock-ddl-per-table

      Run a load

      sysbench /usr/share/sysbench/oltp_insert.lua --tables=10 --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=/home/mchawla/MS_5_7_31/socket.sock --time=20 --rand-type=uniform run

      Take incremental backup with lock-ddl-per-table

      /home/mchawla/pxb_2_4_21_debug/bin/xtrabackup --user=root --password= --backup --target-dir=/home/mchawla/dbbackup_27_07_2020/inc --incremental-basedir=/home/mchawla/dbbackup_27_07_2020/full -S /home/mchawla/MS_5_7_31/socket.sock --datadir=/home/mchawla/MS_5_7_31/data --lock-ddl-per-table

      Prepare full backup

      /home/mchawla/pxb_2_4_21_debug/bin/xtrabackup --user=root --password= --prepare --apply-log-only --target_dir=/home/mchawla/dbbackup_27_07_2020/full

      Prepare incremental backup

      /home/mchawla/pxb_2_4_21_debug/bin/xtrabackup --user=root --password= --prepare --target_dir=/home/mchawla/dbbackup_27_07_2020/full --incremental-dir=/home/mchawla/dbbackup_27_07_2020/inc

      Stop mysql server and move the data directory

      Restore backup

      /home/mchawla/pxb_2_4_21_debug/bin/xtrabackup --user=root --password= --copy-back --target-dir=/home/mchawla/dbbackup_27_07_2020/full --datadir=/home/mchawla/MS_5_7_31/data

      Start the database
      After restore, check the sbtest1 table

      5.7.31>check table sbtest1;
      +--------------+-------+----------+------------------------------------+
      | Table | Op | Msg_type | Msg_text |
      +--------------+-------+----------+------------------------------------+
      | test.sbtest1 | check | Error | Table 'test.sbtest1' doesn't exist |
      | test.sbtest1 | check | status | Operation failed |
      +--------------+-------+----------+------------------------------------+

      In the data directory, the following files for sbtest1 

      -rw-r----- 1 mchawla mchawla 8632 Jul 27 10:58 data/test/sbtest10.frm
      -rw-r----- 1 mchawla mchawla 360448 Jul 27 10:58 data/test/sbtest10.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p4.ibd
      -rw-r----- 1 mchawla mchawla 114688 Jul 27 10:58 data/test/sbtest1#P#p3#TMP#.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p1.ibd
      -rw-r----- 1 mchawla mchawla 114688 Jul 27 10:58 data/test/sbtest1#P#p0#TMP#.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p8.ibd
      -rw-r----- 1 mchawla mchawla 114688 Jul 27 10:58 data/test/sbtest1#P#p2#TMP#.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p3.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p6.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p7.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p0.ibd
      -rw-r----- 1 mchawla mchawla 114688 Jul 27 10:58 data/test/sbtest1#P#p1#TMP#.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p2.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p5.ibd
      -rw-r----- 1 mchawla mchawla 147456 Jul 27 10:58 data/test/sbtest1#P#p9.ibd

      It seems that some partitions of sbtest1 are not restored correctly.
      Logs are attached.

      Note: The same issue exists with MS5.7.30 also.

      Attachments

        Issue Links

          Activity

            People

              marcelo.altmann Marcelo Altmann
              manish.chawla Manish Chawla
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 week, 7 hours
                  1w 7h

                  Smart Checklist