expand_import failure when importing multiple tablespaces

[Olivier Doucet]

Using Percona Server 5.5.17

I tried to import multiple tables at the same time.
I executed following queries :

CREATE DATABASE test;
use test;
CREATE TABLE a1 [...];
CREATE TABLE a2 [...];
CREATE TABLE a3 [...];
[...]

ALTER TABLE a1 DISCARD TABLESPACE;
ALTER TABLE a2 DISCARD TABLESPACE;
ALTER TABLE a3 DISCARD TABLESPACE;
[...]

then, I move .ibd and .exp files to folder ./test/

ALTER TABLE a1 IMPORT TABLESPACE; #working
ALTER TABLE a2 IMPORT TABLESPACE; #working
ALTER TABLE a3 IMPORT TABLESPACE; #fail

Log gives me following interesting lines :
InnoDB: Import: The extended import of test/a3 is being started.
InnoDB: Import: 3 indexes have been detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
InnoDB: Assertion failure in thread 140479148738832 in file rem0rec.c line 561
[...]
/usr/sbin/mysqld-5.5(my_print_stacktrace+0x33)[0x760453]
/usr/sbin/mysqld-5.5(handle_segfault+0x42b)[0x4f06cb]
/lib64/libpthread.so.0[0x7fc3d99a0ad0]
/lib64/libc.so.6(gsignal+0x3e)[0x7fc3d83af2de]
/lib64/libc.so.6(abort+0x19a)[0x7fc3d83b078a]
/usr/sbin/mysqld-5.5[0x869226]
/usr/sbin/mysqld-5.5[0x818ca7]
/usr/sbin/mysqld-5.5[0x789110]
/usr/sbin/mysqld-5.5[0x776745]
/usr/sbin/mysqld-5.5[0x5b0e8d]
/usr/sbin/mysqld-5.5(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x22b)[0x5b96fb]
/usr/sbin/mysqld-5.5(_ZN21Alter_table_statement7executeEP3THD+0x477)[0x745aa7]
/usr/sbin/mysqld-5.5(_Z21mysql_execute_commandP3THD+0x105f)[0x55e00f]
/usr/sbin/mysqld-5.5(_Z11mysql_parseP3THDPcjP12Parser_state+0x313)[0x5615e3]
/usr/sbin/mysqld-5.5(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1418)[0x5631d8]
/usr/sbin/mysqld-5.5(_Z24do_handle_one_connectionP3THD+0x1a6)[0x5f3296]
/usr/sbin/mysqld-5.5(handle_one_connection+0x4a)[0x5f32fa]
/lib64/libpthread.so.0[0x7fc3d99985e4]
/lib64/libc.so.6(clone+0x6d)[0x7fc3d844b45d]
/lib64/libc.so.6(clone+0x6d)[0x7fc3d844b45d]
[...]
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 155 of name './test/a3.ibd'
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 155 of name './mydb/a56.ibd' already exists in the tablespace
InnoDB: memory cache!

Last lines seems important : there may be a collision in InnoDB tablespace. Is this caused because I created / discard many tables before importing data ?
Can it be solved ?

Actual workaround seems to do the following :
CREATE TABLE a1 [...];
ALTER TABLE a1 DISCARD TABLESPACE;
# copy ibd/exp
ALTER TABLE a1 IMPORT TABLESPACE;
CREATE TABLE a2 [...];
ALTER TABLE a2 DISCARD TABLESPACE;
# copy ibd/exp
ALTER TABLE a2 IMPORT TABLESPACE;
[...]

[Alexey Kopytov (akopytov)]

Olivier,

The crash looks identical to the one reported in https://bugs.launchpad.net/percona-server/+bug/1000221

Was the table being imported created with MySQL 5.1 or earlier? If so, then unfortunately there's no way to avoid this assertion completely (bug #1000221 has some explanations why it's impossible), though the fix introduced in 5.5.25 a-27.1 will minimize the chances of hitting that assertion, so upgrading to the latest Percona Server release is recommended.

As to the tablespace id collisions on recovery, I think that's the result of the crash on import. It doesn't look related to the order of DISCARD/IMPORT operations.

[Olivier Doucet (odoucet)]

Alexey,

Thank you for your feedback.
Both servers are on Percona Server 5.5.17

I'll give a try with 5.5.25 in a few days/weeks.

[Olivier Doucet (odoucet)]

And you're right, bug happened even when importing one table, so this bug is probably a duplicate of #1000221
You can add additional comments to this bug report :
Bug also happened when source and destination server are both Percona Server 5.5 (here 5.5.17)
Xtrabackup used is 2.0.2

[Alexey Kopytov (akopytov)]

The important bit here is the version where the table was originally created, rather than just the source server version. The table might be created a long time ago with a pre-5.1.7 version, in which case some fields in the data files might be left uninitialized even after an upgrade to 5.5. InnoDB will ignore those values (and updates to correct ones when updating the corresponding pages), but the improved import procedure relies on those values to scan and update index pages.

[Launchpad Janitor (janitor)]

[Expired for Percona Server because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

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