PXB is crashing when truncate of partition is executed during a backup without locking ddl.
The workaround is taking a backup using --lock-ddl.
Steps to reproduce:
- Create a test schema:
DROP DATABASE test01; CREATE DATABASE test01; CREATE TABLE test01.test01 (id int auto_increment primary key, a varchar(255), b varchar(255)) PARTITION BY HASH(id) PARTITIONS 42;INSERT INTO test01.test01 SET a='Infinite Improbability Drive', b='Infinite Improbability Drive'; INSERT INTO test01.test01 SET a='Infinite Improbability Drive', b='Infinite Improbability Drive'; INSERT INTO test01.test01 SET a='Infinite Improbability Drive', b='Infinite Improbability Drive'; INSERT INTO test01.test01 SET a='Phargilor Kangaroo Relocation Drive', b='Phargilor Kangaroo Relocation Drive'; INSERT INTO test01.test01 SET a='Phargilor Kangaroo Relocation Drive', b='Phargilor Kangaroo Relocation Drive'; INSERT INTO test01.test01 SET a='Phargilor Kangaroo Relocation Drive', b='Phargilor Kangaroo Relocation Drive'; INSERT INTO test01.test01 SET a='Bistromathic drive', b='Bistromathic drive'; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01; INSERT INTO test01.test01 (a,b) SELECT a,b FROM test01.test01;
2. Take a backup without locking ddl (--lock-ddl) and execute ALTER TABLE test01.test01 TRUNCATE partition p16 when PXB is copying that partition file:
xtrabackup --throttle=1 --backup 2>&1 | while read line; do echo $line; if [ $( echo $line | grep -c './test01/test01#P#p16.ibd') -eq 1 ]; then mysql -e "ALTER TABLE test01.test01 TRUNCATE PARTITION p16;"; fi; done;
3. PXB will crash with the following message:
InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: 9437184 bytes should have been read. Only 98304 bytes read. Retrying for the remaining bytes. InnoDB: Retry attempts for reading partial data failed. InnoDB: Tried to read 9437184 bytes at offset 0, but was only able to read 98304 [FATAL] InnoDB: Cannot read from file. OS error number 17. 2018-10-12 21:47:36 0x7fce4bdfa700 InnoDB: Assertion failure in thread 140524012939008 in file ut0ut.cc line 914 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 19:47:36 UTC - xtrabackup got signal 6 ; This could be because you hit a bug or data is corrupted. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail.Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x10000 xtrabackup(my_print_stacktrace+0x2c)[0x558cfd27dd0c] xtrabackup(handle_fatal_signal+0x281)[0x558cfd0f7fa1] /lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7fce5fee60c0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcf)[0x7fce5da34fff] /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fce5da3642a] xtrabackup(+0x55b9c9)[0x558cfc9e59c9] xtrabackup(_ZN2ib5fatalD1Ev+0x12d)[0x558cfccbd60d] 181012 21:47:36 >> log scanned up to (2086172773) xtrabackup(+0x7379cc)[0x558cfcbc19cc] xtrabackup(_Z17os_file_read_funcR9IORequestiPvmm+0x12)[0x558cfcbc1ad2] xtrabackup(_Z15xb_fil_cur_readP12xb_fil_cur_t+0x1e5)[0x558cfca18555] xtrabackup(+0x57a5f7)[0x558cfca045f7] xtrabackup(+0x57ef68)[0x558cfca08f68] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7494)[0x7fce5fedc494] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fce5daeaacf]Please report a bug at https://jira.percona.com/projects/PXB
Tested against xtrabackup version 2.4.12 and MySQL 5.7.23.