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.