Bug #85722 Apparent InnoDB corruption after import using ALTER TABLE ... IMPORT TABLESPACE
Submitted: 30 Mar 2017 23:41 Modified: 19 May 2017 18:44
Reporter: Daniel Bakken Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.35 OS:Debian (Jessie)
Assigned to: CPU Architecture:Any

[30 Mar 2017 23:41] Daniel Bakken
Description:
CHECK TABLE reports apparent corruption after importing a large InnoDB table (37 million rows, 8GB size) using ALTER TABLE ... IMPORT TABLESPACE. However, data appears intact, SELECT queries succeed, and rebuilding with OPTIMIZE TABLE fixes the apparent corruption. This behavior resembles https://bugs.mysql.com/bug.php?id=67807, fixed in MySQL 5.6.10.

How to repeat:
# On src server, create table and insert 37 million rows:
CREATE TABLE `clicks` (
  `ClickID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PageVisitID` int(11) DEFAULT NULL,
  `Target` varchar(250) DEFAULT NULL,
  `Type` varchar(45) DEFAULT NULL,
  `Href` varchar(100) DEFAULT NULL,
  `PosX` int(10) DEFAULT NULL,
  `PosY` int(10) DEFAULT NULL,
  `Time` datetime NOT NULL,
  PRIMARY KEY (`ClickID`),
  KEY `Index 2` (`PageVisitID`)
) ENGINE=InnoDB AUTO_INCREMENT=112010592 DEFAULT CHARSET=latin1

# Create empty table with identical structure on dest server:

mysqldump --no-data -h src test clicks | mysql test

mysql> ALTER TABLE clicks DISCARD TABLESPACE;

# Flush table on src server and copy to dest server

mysql> FLUSH TABLES test.clicks FOR EXPORT;

rsync -av /data/mysql/test/clicks.ibd dst:/data/mysql/test/clicks.ibd

mysql> UNLOCK TABLES;

# Import table on dest server and query:

mysql> ALTER TABLE clicks IMPORT TABLESPACE; ANALYZE TABLE clicks;

mysql> SELECT count(*) FROM test.clicks;
+----------+
| count(*) |
+----------+
| 37351483 |
+----------+
1 row in set (33.68 sec)

# CHECK TABLE on dest server does not complete after 3 hours and fills the MySQL error log with InnoDB debug messages:

2017-03-30 14:15:37 7f2eaedb7700 InnoDB: uncompressed page, stored checksum in field1 3474018396, calculated checksums for field1: crc32 3756757376, innodb 3474018396, none 3735928559, stored checksum in field2 4143326443, calculated checksums for field2: crc32 3756757376, innodb 4143326443, none 3735928559, page LSN 2342 3023028873, low 4 bytes of LSN at page end 3023028873, page number (if stored to page already) 56360, space id (if created with >= MySQL-4.1.1 and stored already) 45368
InnoDB: Page may be an index page where index id is 64532
InnoDB: (index "PRIMARY" of table "test"."clicks")
InnoDB: Error in page 56360 of index "PRIMARY" of table "test"."clicks"
InnoDB: Summed data size 14763, returned by func 15030
InnoDB: Apparent corruption in space 45368 page 56361 index PRIMARY
2017-03-30 14:15:37 7f2eaedb7700 InnoDB: Page dump in ascii and hex (16384 bytes):

# Repair table fixes corruption and results in OK status from CHECK TABLE in 1 minute.

mysql> OPTIMIZE TABLE clicks;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| test.clicks | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.clicks | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+

mysql> CHECK TABLE clicks;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.clicks | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (1 min 2.01 sec)
[31 Mar 2017 15:21] Daniel Bakken
After 5 hours, CHECK TABLE reports corruption:

mysql> check table clicks;
+-------------+-------+----------+------------------------------------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                                                           |
+-------------+-------+----------+------------------------------------------------------------------------------------+
| test.clicks | check | Warning  | InnoDB: The B-tree of index "PRIMARY" is corrupted.                                |
| test.clicks | check | Warning  | InnoDB: Index 'Index 2' contains 37356115 entries, should be 18446744073709551615. |
| test.clicks | check | error    | Corrupt                                                                            |
+-------------+-------+----------+------------------------------------------------------------------------------------+
3 rows in set (5 hours 19 min 3.00 sec)

Debug messages from CHECK TABLE have grown the MySQL error log to 45GB.
[31 Mar 2017 17:29] Daniel Bakken
After adding the clicks.cfg file to the transfer process, I received the following error on the destination server:

ERROR 1808 (HY000) at line 1: Schema mismatch (Column Time precise type mismatch.)

And the server log reports:

2017-03-31 10:18:13 28366 [Note] InnoDB: Importing tablespace for table 'cc_log/clicks' that was exported from host 'hqbackup'
2017-03-31 10:18:13 28366 [Note] InnoDB: Discarding tablespace of table "test"."clicks_scty": Generic error

Which causes ALTER TABLE ... IMPORT TABLESPACE to fail. Below is the output of SHOW TABLE STATUS:

mysql> show table status like 'clicks';
+--------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+--------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| clicks | InnoDB |      10 | Compact    | 35696047 |            186 |  6652166144 |               0 |   1204584448 |   5242880 |      112106946 | 2017-03-30 16:57:43 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+--------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
[31 Mar 2017 18:44] Daniel Bakken
It appears that ALTER TABLE ... IMPORT TABLESPACE on tables with pre-5.6 datetime column format causes apparent corruption. After enabling show_old_temporals, SHOW CREATE TABLE reveals the pre-5.6 datetime binary format.

CREATE TABLE `clicks` (
  `ClickID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PageVisitID` int(11) DEFAULT NULL,
  `Target` varchar(250) DEFAULT NULL,
  `Type` varchar(45) DEFAULT NULL,
  `Href` varchar(100) DEFAULT NULL,
  `PosX` int(10) DEFAULT NULL,
  `PosY` int(10) DEFAULT NULL,
  `Time` datetime /* 5.5 binary format */ NOT NULL,
  PRIMARY KEY (`ClickID`),
  KEY `Index 2` (`PageVisitID`)
) ENGINE=InnoDB AUTO_INCREMENT=112111149 DEFAULT CHARSET=latin1

Perhaps it could be noted in documentation that pre-5.6 datetime fields break the InnoDB transportable tablespace feature.
[19 May 2017 16:10] MySQL Verification Team
Hi!

I truly fail to understand your request. What you describe is so thoroughly describe in 5.6 manual and, to somewhat less extend, to later manuals, so I do not see the reason to document it in 5.5 manual, because this problem can never appear in 5.5 or earlier versions.
[19 May 2017 18:44] Daniel Bakken
This behavior is mentioned in the documentation at https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html:
Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables (using ALTER TABLE ... IMPORT TABLESPACE) that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) fails. Importing a MySQL 5.5 table with these temporal types into MySQL 5.6.4 (or later) is the mostly likely scenario for this problem to occur.

However, when the .cfg file omitted, the import does not fail cleanly, but causes MySQL to report table corruption. A database administrator can properly upgrade to MySQL 5.6 using mysql_upgrade, and still encounter apparent data corruption. This may lead them to erroneously assume data on the source server is corrupt, resulting in unecessary restore from backup and data loss. This could be noted in documentation for the transportable tablespace feature. It would be even better if IMPORT TABLESPACE accurately reported the old temporal column format instead of falsely reporting table corruption when a .cfg file is not present.