A Xtrabackup backup taken from PS 5.6 with temporary table causes data dictionary inconsistencies when imported to PS 5.7

Bug #1701558 reported by Przemek
6
This bug affects 1 person
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-file=msb_percona5_6_35/my.sandbox.cnf --port=5635 --user=root --password=msandbox backups/
 $ innobackupex --apply-log backups/2017-06-30_13-31-15/
* copy the backup into empty datadir of 5.7 instance:
 $ cp -r backups/2017-06-30_13-31-15/* msb_percona5_7_18/data/
* start the 5.7 instance and this error will appear:
2017-06-30T11:39:56.603566Z 0 [ERROR] InnoDB: In file '/home/przemek/sandboxes/msb_percona5_6_35/tmp/#sql42bd_4_0.ibd', tablespace id
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://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-dat
adict.html for how to resolve the issue.
2017-06-30T11:39:56.603605Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.

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_schema.innodb_sys_tables where name like '%sql42%'\G
*************************** 1. row ***************************
     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

Tags: i192756
Revision history for this message
Przemek (pmalkowski) wrote :

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.

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Sergei, could you please review if this has to be handled in PS, PXB, or both?

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1102

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.