A Xtrabackup backup taken from PS 5.6 with temporary table causes data dictionary inconsistencies when imported to PS 5.7
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.7 |
Triaged
|
High
|
Sergei Glushchenko |
Bug Description
When an explicit temporary table is present on PS 5.6 and we make Xtrabackup from it, it will cause problem when the backup is used to provision 5.7 instance. Due to a different handling of temporary tables in 5.7, data dictionary gets inconsistent and no way to clean it up.
Test case:
* create sandbox PS 5.6 instance
* create temporary table:
mysql [localhost] {msandbox} (test) > CREATE TEMPORARY TABLE test (id int);
Query OK, 0 rows affected (0.07 sec)
* while session above is open, take backup using Xtrabackup (tested on 2.3.8 and 2.4.7):
$ innobackupex --defaults-
$ innobackupex --apply-log backups/
* copy the backup into empty datadir of 5.7 instance:
$ cp -r backups/
* start the 5.7 instance and this error will appear:
2017-06-
and flags are 8 and 0, but in the InnoDB data dictionary they are 8 and 4096. Have you moved InnoDB .ibd files around without using th
e commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://
adict.html for how to resolve the issue.
2017-06-
This error won't disappear on next restarts, also, we can see the entry in innodb_sys_tables, which normally does not exist for temporary tables created in 5.7:
mysql [localhost] {msandbox} ((none)) > select * from information_
*******
TABLE_ID: 24
NAME: tmp/#sql42bd_4_0
FLAG: 1
N_COLS: 4
SPACE: 8
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)
When a similar backup is restored onto 5.6 instance, after initial complain, it is gone on next restarts, as well as entry in innodb_sys_tables is removed.
This scenario may hurt when provisioning 5.7 slaves off of 5.6 master. A workaround for it that worked for me (if we can't make sure no temp tables exists when we make backup):
* create & prepare backup on 5.6
* import the backup on another 5.6 instance, restart it and stop
* copy the datadir from another 5.6 to destination 5.7 instance - no more errors
I am not sure if the potential fix should be done on Xtrabackup side or PS side, but the orphaned temporary table dictionary entry should be cleaned up.