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

Encrypted table is not restored when ADD/DROP INDEX is run on the table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.6, 2.4.15
    • Fix Version/s: 2.4.16, 8.0.8
    • Component/s: None
    • Labels:
      None

      Description

      Start PS 8.0.16-6 as:

      /home/mchawla/PS230719_8_0_16_6_debug/bin/mysqld --no-defaults --core-file --basedir=/home/mchawla/PS230719_8_0_16_6_debug --tmpdir=/home/mchawla/PS230719_8_0_16_6_debug/data --datadir=/home/mchawla/PS230719_8_0_16_6_debug/data --plugin-load-add=tokudb=ha_tokudb.so --tokudb-check-jemalloc=0 --plugin-load-add=rocksdb=ha_rocksdb.so --socket=/home/mchawla/PS230719_8_0_16_6_debug/socket.sock --port=12915 --log-error=/home/mchawla/PS230719_8_0_16_6_debug/log/master.err --server-id=100 --log-bin=binlog --early-plugin-load=keyring_file.so --keyring_file_data=/home/mchawla/PS230719_8_0_16_6_debug/keyring --innodb-undo-log-encrypt --default-table-encryption=OFF --innodb_encrypt_online_alter_logs=ON --innodb_temp_tablespace_encrypt=ON --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --master_verify_checksum=ON --binlog_checksum=CRC32 --encrypt-tmp-files --innodb_sys_tablespace_encrypt --innodb_parallel_dblwr_encrypt --binlog-rotate-encryption-master-key-at-startup --table-encryption-privilege-check=ON --innodb-default-encryption-key-id=4294967295 --innodb-encryption-threads=10

      Create innodb encrypted tables(10) and rocksdb tables(10). Add some data.

      for ((i=1; i<=${num_tables}; i++)); do
       echo "Creating the table sbtest$i..."
       ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test.sbtest$i (id int(11) NOT NULL AUTO_INCREMENT, k int(11) NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y';"
       done
      echo "Adding data in tables..."
       sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=30 run >/dev/null 2>&1
       fi
      echo "Creating rocksdb data in database"
       ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE DATABASE IF NOT EXISTS test_rocksdb;"
       sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --table-size=${table_size} --mysql-db=test_rocksdb --mysql-user=root --threads=100 --db-driver=mysql --mysql-storage-engine=ROCKSDB --mysql-socket=${mysqldir}/socket.sock prepare

      Run the loop continuously:   

          echo "Add and drop an index in the test.sbtest1 table"
          ( for ((i=1; i<=10; i++)); do
              # Check if database is up otherwise exit the loop
              ${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
              if [ "$?" -ne 0 ]; then
                  break
              fi
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE INDEX kc on test.sbtest1 (k,c);" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ADD INDEX kc2 (k,c);" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc2 on test.sbtest1;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test.sbtest1;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ADD INDEX kc (k,c), ALGORITHM=COPY, LOCK=EXCLUSIVE;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test.sbtest1;" >/dev/null 2>&1
          done ) &    
      
      echo "Add and drop an index in the test_rocksdb.sbtest1 table"
          ( for ((i=1; i<=10; i++)); do
              # Check if database is up otherwise exit the loop
              ${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
              if [ "$?" -ne 0 ]; then
                  break
              fi
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE INDEX kc on test_rocksdb.sbtest1 (k,c);" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 ADD INDEX kc2 (k,c);" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc2 on test_rocksdb.sbtest1;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test_rocksdb.sbtest1;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 ADD INDEX kc (k,c), ALGORITHM=COPY, LOCK=EXCLUSIVE;" >/dev/null 2>&1
              ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test_rocksdb.sbtest1;" >/dev/null 2>&1
          done ) &

      Take full backup with options:

      --keyring_file_data=${mysqldir}/keyring --xtrabackup-plugin-dir=${xtrabackup_dir}/../lib/plugin --lock-ddl

      Run a small load with sysbench:

      # Innodb data
       sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=20 run >/dev/null 2>&1 &
      # Rocksdb data
       sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test_rocksdb --mysql-user=root --threads=50 --db-driver=mysql --mysql-storage-engine=ROCKSDB --mysql-socket=${mysqldir}/socket.sock --time=20 run >/dev/null 2>&1 &
       sleep 10

      Take incremental backup with options:

      --keyring_file_data=${mysqldir}/keyring --xtrabackup-plugin-dir=${xtrabackup_dir}/../lib/plugin --lock-ddl

      Incremental logs display:

      Encryption information in datafile: ./test/sbtest1.ibd can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded.

      Prepare the full backup
      Prepare the incremental backup
      Prepare logs display:

      my_realpath(./test/sbtest1.ibd) failed!
      my_realpath(./test/#sql2-3f9c-1ac.ibd) failed!
      Rename failed. Cannot find './test/sbtest1.ibd'!
      my_realpath(./test/#sql-3f9c_1ac.ibd) failed!
      my_realpath(./test/sbtest1.ibd) failed!
      Rename failed. Cannot find './test/#sql-3f9c_1ac.ibd'!

      Restore the full backup and start the PS server
      Mysql logs display:

      2019-07-26T05:17:42.259737Z 0 [System] [MY-010931] [Server] /home/mchawla/PS230719_8_0_16_6_debug/bin/mysqld: ready for connections. Version: '8.0.16-6-debug' socket: '/home/mchawla/PS230719_8_0_16_6_debug/socket.sock' port: 12915 Source distribution.
      2019-07-26T05:17:42.372852Z 9 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
      2019-07-26T05:17:42.372885Z 9 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
      2019-07-26T05:17:42.380318Z 9 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './test/sbtest1.ibd' OS error: 71

      Cli displays:

      8.0.16>check table sbtest1;
      +--------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------+
      | Table | Op | Msg_type | Msg_text |
      +--------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------+
      | test.sbtest1 | check | Error | Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. |
      | test.sbtest1 | check | Error | Operation cannot be performed. The table 'test.sbtest1' is missing, corrupt or contains bad data. |
      | test.sbtest1 | check | error | Corrupt |
      +--------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------+

      Result: The mysql data dir does not contain the sbtest1.ibd file.

      Note: I was able to reproduce the same issue when row format is changed and a table is updated/truncated.

      Logs are attached.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              sergei.glushchenko Sergei Glushchenko (Inactive)
              Reporter:
              manish.chawla Manish Chawla
              Votes:
              0 Vote for this issue
              Watchers:
              2 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, 6 hours, 41 minutes
                  1w 6h 41m