MySQL Bugs: #67807: InnoDB table corrupted after import using ALTER TABLE ... IMPORT TABLESPACE

Bug #67807	InnoDB table corrupted after import using ALTER TABLE ... IMPORT TABLESPACE
Submitted:	4 Dec 2012 23:40	Modified:	7 Jan 2013 23:18
Reporter:	Dirk LANGE	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.8-rc	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
under certain circumstances an InnoDB table gets apparently corrupted after import using ALTER TABLE ... IMPORT TABLESPACE;

my sandbox:

* Solaris 11 on x64_64 resp. Solaris 10 on x64_64
* binaries: 5.6.8-rc (mysql-5.6.8-rc-solaris11-x86_64 resp. mysql-5.6.8-rc-solaris10-x86_64), 5.6.7-rc as reference (mysql-5.6.7-rc-solaris11-x86_64 resp. mysql-5.6.7-rc-solaris10-x86_64)

table-check sends error message:

Warning  : InnoDB: The B-tree of index "PRIMARY" is corrupted.
error    : Corrupt

if the table does not have a dedicated PRIMARY key, the error message changes naturally to:

Warning  : InnoDB: The B-tree of index "GEN_CLUST_INDEX" is corrupted.
error    : Corrupt

server log shows one or multiple lines of:

[ERROR] InnoDB: Page index id <m> != data dictionary index id <n>

having seemingly  m+1 == n

features:

* table content matches the original table despite the error
* effect seems to appear with a specific threshold of table content, in other words, it may not happen with tables having little number of data
* a table repair fixes the issue
* effect does not happen on an identically configured 5.6.7-rc for the table sizes tested

How to repeat:
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t (id INT(10) UNSIGNED NOT NULL, anytext VARCHAR(255) NULL DEFAULT NULL) COLLATE='latin1_swedish_ci' ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> -- insert reasonable number of records of any kind,
mysql> -- the reference table here has about 39000 records
mysql> INSERT INTO t SELECT @c:=@c+1, MD5(@c) FROM mysql.time_zone_transition JOIN (SELECT @c:=0) cnt;
Query OK, 39162 rows affected (0.73 sec)
Records: 39162  Duplicates: 0  Warnings: 0

mysql> CHECK TABLE t EXTENDED;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.07 sec)

mysql> FLUSH TABLES t FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

mysql> -- copy files to backup location: ibd, cfg

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

mysql> CHECK TABLE t EXTENDED;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.07 sec)

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t (id INT(10) UNSIGNED NOT NULL, anytext varchar(255) NULL DEFAULT NULL) COLLATE='latin1_swedish_ci' ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

mysql> -- copy backup files to target location: ibd, cfg

mysql> ALTER TABLE t IMPORT TABLESPACE;
Query OK, 0 rows affected (0.61 sec)

mysql> CHECK TABLE t EXTENDED;
+--------+-------+----------+-------------------------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                                    |
+--------+-------+----------+-------------------------------------------------------------+
| test.t | check | Warning  | InnoDB: The B-tree of index "GEN_CLUST_INDEX" is corrupted. |
| test.t | check | error    | Corrupt                                                     |
+--------+-------+----------+-------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> ALTER TABLE t ENGINE=INNODB;
Query OK, 39162 rows affected (0.80 sec)
Records: 39162  Duplicates: 0  Warnings: 0

mysql> CHECK TABLE t EXTENDED;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.14 sec)

server log:

121204 14:08:30 [Note] InnoDB: Sync to disk of '"test"."t"' started.
121204 14:08:30 [Note] InnoDB: Stopping purge
121204 14:08:30 [Note] InnoDB: Writing table metadata to './test/t.cfg'
121204 14:08:30 [Note] InnoDB: Table '"test"."t"' flushed to disk
121204 14:09:07 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
121204 14:09:07 [Note] InnoDB: Resuming purge
121204 14:10:09 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'my_host'
121204 14:10:09 [Note] InnoDB: Phase I - Update all pages
121204 14:10:09 [Note] InnoDB: Sync to disk
121204 14:10:09 [Note] InnoDB: Sync to disk - done!
121204 14:10:09 [Note] InnoDB: Phase III - Flush changes to disk
121204 14:10:09 [Note] InnoDB: Phase IV - Flush complete
121204 14:10:26 [ERROR] InnoDB: Page index id 184 != data dictionary index id 185
121204 14:10:26 [ERROR] InnoDB: Page index id 184 != data dictionary index id 185
.
.
.
121204 14:10:26 [ERROR] InnoDB: Page index id 184 != data dictionary index id 185
121204 14:10:26 [ERROR] InnoDB: Page index id 184 != data dictionary index id 185

Thank you for the report.

Verified as described.

We now have a fix for this that will probably make the 5.6.10 release.

Added to changelog for 5.6.10: 

Under certain circumstances, an InnoDB table was reported as
corrupted after import using ALTER TABLE ... IMPORT TABLESPACE. The
problem was accompanied by one of these messages:

Warning : InnoDB: The B-tree of index "PRIMARY" is corrupted.
error : Corrupt

or:

Warning : InnoDB: The B-tree of index "GEN_CLUST_INDEX" is corrupted.
error : Corrupt

This issue occurred intermittently, and primarily
affected large tables. The REPAIR TABLE statement would fix the
problem reported by the error message.

I am using the following version -
mysql> show variables like '%version%'
    -> ;
+-------------------------+--------------------------------------------------------------+
| Variable_name           | Value                                                        |
+-------------------------+--------------------------------------------------------------+
| innodb_version          | 5.6.15-rel63.0                                               |
| protocol_version        | 10                                                           |
| slave_type_conversions  |                                                              |
| version                 | 5.6.15-63.0                                                  |
| version_comment         | Percona XtraDB Cluster (GPL), Release 25.5, wsrep_25.5.r4061 |
| version_compile_machine | x86_64                                                       |
| version_compile_os      | Linux                                                        |
+-------------------------+--------------------------------------------------------------+
7 rows in set (0.00 sec)

After I import table space I get the error - 
mysql> check table chunk_map;
+-----------------------------------------------+-------+----------+-----------------------------------------------------+
| Table                                         | Op    | Msg_type | Msg_text                                            |
+-----------------------------------------------+-------+----------+-----------------------------------------------------+
| dbsnap_Tue_Oct__6_13_58_45_PDT_2015.chunk_map | check | Warning  | InnoDB: The B-tree of index "PRIMARY" is corrupted. |
| dbsnap_Tue_Oct__6_13_58_45_PDT_2015.chunk_map | check | error    | Corrupt                                             |
+-----------------------------------------------+-------+----------+-----------------------------------------------------+
2 rows in set (0.00 sec)

If I run repair table -
mysql> repair table chunk_map;
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
| Table                                         | Op     | Msg_type | Msg_text                                                |
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
| dbsnap_Tue_Oct__6_13_58_45_PDT_2015.chunk_map | repair | note     | The storage engine for the table doesn't support repair |
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

If I run alter table and then check table - 
mysql> alter table chunk_map engine=innodb;
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> check table chunk_map;
+-----------------------------------------------+-------+----------+----------+
| Table                                         | Op    | Msg_type | Msg_text |
+-----------------------------------------------+-------+----------+----------+
| dbsnap_Tue_Oct__6_13_58_45_PDT_2015.chunk_map | check | status   | OK       |
+-----------------------------------------------+-------+----------+----------+
1 row in set (0.00 sec)

I understand that this was fixed in 5.6.10. But I still see it in 5.6.15 . 

What is the solution to this defect ? What should I do so that I do not get this corruption error ?
After import if I get corruption should I run 'repair table' or 'alter table' like I have ? Repair table is not working for me as mentioned above.

Since you are not using Oracle/InnoDB, it is difficult for us to make any comment. The corruption could be due to downstream changes and incompatibilities.