Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.33a
-
Fix Version/s: 5.5.39
-
Component/s: Data Definition - Alter Table
-
Labels:None
-
Environment:debian squeeze
Description
After doing an alter table on a partitionned innodb table without PK
alter table t_price_multi drop index date, add primary key (date,id), engine=TokuDB;
Kill that query , after the rollback
A show create table still tell it's tokudb
Table Create Table t_price_multi CREATE TABLE `t_price_multi` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `tyre_id` mediumint(8) unsigned NOT NULL, `website_id` smallint(5) unsigned NOT NULL, `price_type_id` tinyint(3) unsigned NOT NULL, `price` float(8,2) NOT NULL, `wsprice` float(8,2) DEFAULT NULL, `dot` smallint(5) unsigned DEFAULT NULL, `date` date NOT NULL, `url_hash` char(32) DEFAULT NULL, `labelling_id` smallint(5) unsigned DEFAULT NULL, `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `in_stock` tinyint(3) unsigned DEFAULT NULL, `quantity` smallint(5) unsigned DEFAULT NULL, `filename` varchar(50) DEFAULT NULL, KEY `idx_id` (`id`), KEY `tyre_id` (`tyre_id`), KEY `website_id` (`website_id`), KEY `price_type_id` (`price_type_id`), KEY `date` (`date`) ) ENGINE=TokuDB AUTO_INCREMENT=873864067 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (TO_DAYS(DATE)) (PARTITION p1 VALUES LESS THAN (733773) ENGINE = TokuDB, PARTITION p2 VALUES LESS THAN (733804) ENGINE = TokuDB, PARTITION p3 VALUES LESS THAN (733832) ENGINE = TokuDB, PARTITION p4 VALUES LESS THAN (733863) ENGINE = TokuDB, ..... TokuDB, PARTITION p1000 VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */
The disk still have the idb files and droped the tokudb partitions
-rw-rw---- 1 mysql mysql 17K Oct 1 16:42 t_price_multi.frm -rw-rw---- 1 mysql mysql 912 Aug 28 19:18 t_price_multi.par -rw-rw---- 1 mysql mysql 176K Aug 28 19:20 t_price_multi#P#p1000.ibd -rw-rw---- 1 mysql mysql 176K Aug 28 19:19 t_price_multi#P#p10.ibd -rw-rw---- 1 mysql mysql 176K Aug 28 19:19 t_price_multi#P#p11.ibd -rw-rw---- 1 mysql mysql 176K Aug 28 19:19 t_price_multi#P#p12.ibd -rw-rw---- 1 mysql mysql 176K Aug 28 19:19 t_price_multi#P#p13.ibd -rw-rw---- 1 mysql mysql 176K Aug 28 19:19 t_price_multi#P#p14.ibd
It was possible to read from the table
But
After restarting the innodb tablespace was corrupted
Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: for how to resolve the issue. Oct 3 18:26:09 tyrebase-galera02-pp mysqld: 131003 18:26:09 InnoDB: Operating system error number 2 in a file operation. Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: The error means the system cannot find the path specified. Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: If you are installing InnoDB, remember that you must create Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: directories yourself, InnoDB does not create them. Oct 3 18:26:09 tyrebase-galera02-pp mysqld: 131003 18:26:09 InnoDB: Error: trying to open a table, but could not Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: open the tablespace file './tyrebase4W/t_price_historic#P#p98.ibd'! Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: Have you moved InnoDB .ibd files around without using the Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: It is also possible that this is a temporary table #sql..., Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: and MySQL removed the .ibd file for this. Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: Please refer to Oct 3 18:26:09 tyrebase-galera02-pp mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It looks like the bug was fixed in 5.5.39 by revno 4261 (the merge of MySQL 5.5.39). I could easily reproduce it by low-tech manual test before (create InnoDB table, populate with some 100K rows, run alter, kill it from another connection, check SHOW CREATE TABLE, check for *ibd files).
Up to and including revno 4261, SHOW CREATE would show TokuDB, and *ibd files would exist. Starting from revno 4261, SHOW CREATE shows InnoDB.
This is a good suspect for fixing the issue from MySQL 5.5.39 merge:
revno: 3077.203.31 revision-id: nisha.gopalakrishnan@oracle.com-20140624044553-t8m0c6nhcsvm5czn parent: gleb.shchepa@oracle.com-20140623155915-kq1lsz7g5jptimym committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com> branch nick: mysql-5.5-18618561 timestamp: Tue 2014-06-24 10:15:53 +0530 message: BUG#18618561: FAILED ALTER TABLE ENGINE CHANGE WITH PARTITIONS CORRUPTS FRM Analysis: --------- ALTER TABLE on a partitioned table resulted in the wrong engine being written into the table's FRM file and displayed in SHOW CREATE TABLE. The prep_alter_part_table() modifies the partition_info object for TABLE instance representing the old version of table. If the ALTER TABLE ENGINE statement fails, the partition_info object for the TABLE contains the altered storage engine name. The SHOW CREATE TABLE uses the TABLE object to display the table information, hence displays incorrect storage engine for the table. Also a subsequent successful ALTER TABLE operation will write the incorrect engine information into the FRM file. Fix: --- A copy of the partition_info object is created before modification so that any changes would not cause the the original partition_info object to be modified if the ALTER TABLE fails.(Backported part of the code provided as fix for bug#14156617 in mysql-5.6.6).