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

[Przemek]

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

[Przemek (pmalkowski)]

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.

[Laurynas Biveinis (laurynas-biveinis)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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