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: | |
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
[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.