| Bug #64742 | "Incorrect key file for table 'm'; try to repair " when using partitioning | ||
|---|---|---|---|
| Submitted: | 23 Mar 2012 9:28 | Modified: | 14 Sep 2012 9:29 |
| Reporter: | Grégory Duchatelet | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.6.4 | OS: | Any (Linux, Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[23 Mar 2012 9:28]
Grégory Duchatelet
[23 Mar 2012 9:58]
Valeriy Kravchuk
Thank you for the bug report. Verified on Windows XP also:
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-m7 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS m;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `m` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `message_type_id` tinyint(3) unsigned NOT NULL,
-> `user_id_from` int(11) unsigned NOT NULL,
-> `user_id_to` int(11) unsigned NOT NULL,
-> `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-> `opened_at` datetime DEFAULT NULL,
-> `deleted_from_at` datetime DEFAULT NULL,
-> `deleted_to_at` datetime DEFAULT NULL,
-> `archived` tinyint(1) unsigned DEFAULT '0',
-> `message_subject_id` char(32) DEFAULT NULL,
-> `message_content_id` char(32) DEFAULT NULL,
-> PRIMARY KEY (`id`,`created_at`),
-> KEY `created_at` (`created_at`,`message_type_id`),
-> KEY `user_id_to` (`user_id_to`),
-> KEY `user_id_from` (`user_id_from`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
-> /*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
-> (PARTITION pold VALUES LESS THAN (734138) ENGINE = InnoDB,
-> PARTITION p1001 VALUES LESS THAN (734169) ENGINE = InnoDB,
-> PARTITION p1002 VALUES LESS THAN (734197) ENGINE = InnoDB,
-> PARTITION p1003 VALUES LESS THAN (734228) ENGINE = InnoDB,
-> PARTITION p1004 VALUES LESS THAN (734258) ENGINE = InnoDB,
-> PARTITION p1005 VALUES LESS THAN (734289) ENGINE = InnoDB,
-> PARTITION p1006 VALUES LESS THAN (734319) ENGINE = InnoDB,
-> PARTITION p1007 VALUES LESS THAN (734350) ENGINE = InnoDB,
-> PARTITION p1008 VALUES LESS THAN (734381) ENGINE = InnoDB,
-> PARTITION p1009 VALUES LESS THAN (734411) ENGINE = InnoDB,
-> PARTITION p1010 VALUES LESS THAN (734442) ENGINE = InnoDB,
-> PARTITION p1011 VALUES LESS THAN (734472) ENGINE = InnoDB,
-> PARTITION p1012 VALUES LESS THAN (734503) ENGINE = InnoDB,
-> PARTITION p1101 VALUES LESS THAN (734534) ENGINE = InnoDB,
-> PARTITION p1102 VALUES LESS THAN (734562) ENGINE = InnoDB,
-> PARTITION p1103 VALUES LESS THAN (734593) ENGINE = InnoDB,
-> PARTITION p1104 VALUES LESS THAN (734623) ENGINE = InnoDB,
-> PARTITION p1105 VALUES LESS THAN (734654) ENGINE = InnoDB,
-> PARTITION p1106 VALUES LESS THAN (734684) ENGINE = InnoDB,
-> PARTITION p1107 VALUES LESS THAN (734715) ENGINE = InnoDB,
-> PARTITION p1108 VALUES LESS THAN (734746) ENGINE = InnoDB,
-> PARTITION p1109 VALUES LESS THAN (734776) ENGINE = InnoDB,
-> PARTITION p1110 VALUES LESS THAN (734807) ENGINE = InnoDB,
-> PARTITION p1111 VALUES LESS THAN (734837) ENGINE = InnoDB,
-> PARTITION p1112 VALUES LESS THAN (734868) ENGINE = InnoDB,
-> PARTITION p1201 VALUES LESS THAN (734899) ENGINE = InnoDB,
-> PARTITION p1202 VALUES LESS THAN (734928) ENGINE = InnoDB,
-> PARTITION p1203 VALUES LESS THAN (734959) ENGINE = InnoDB,
-> PARTITION p1204 VALUES LESS THAN (734989) ENGINE = InnoDB,
-> PARTITION p1205 VALUES LESS THAN (735020) ENGINE = InnoDB,
-> PARTITION p1206 VALUES LESS THAN (735050) ENGINE = InnoDB,
-> PARTITION p1207 VALUES LESS THAN (735081) ENGINE = InnoDB,
-> PARTITION p1208 VALUES LESS THAN (735112) ENGINE = InnoDB,
-> PARTITION p1209 VALUES LESS THAN (735142) ENGINE = InnoDB,
-> PARTITION p1210 VALUES LESS THAN (735173) ENGINE = InnoDB,
-> PARTITION p1211 VALUES LESS THAN (735203) ENGINE = InnoDB,
-> PARTITION p1212 VALUES LESS THAN (735234) ENGINE = InnoDB,
-> PARTITION p1301 VALUES LESS THAN (735265) ENGINE = InnoDB,
-> PARTITION p1302 VALUES LESS THAN (735293) ENGINE = InnoDB,
-> PARTITION p1303 VALUES LESS THAN (735324) ENGINE = InnoDB,
-> PARTITION p1304 VALUES LESS THAN (735354) ENGINE = InnoDB,
-> PARTITION p1305 VALUES LESS THAN (735385) ENGINE = InnoDB,
-> PARTITION p1306 VALUES LESS THAN (735415) ENGINE = InnoDB,
-> PARTITION p1307 VALUES LESS THAN (735446) ENGINE = InnoDB,
-> PARTITION p1308 VALUES LESS THAN (735477) ENGINE = InnoDB,
-> PARTITION p1309 VALUES LESS THAN (735507) ENGINE = InnoDB,
-> PARTITION p1310 VALUES LESS THAN (735538) ENGINE = InnoDB,
-> PARTITION p1311 VALUES LESS THAN (735568) ENGINE = InnoDB,
-> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ ;
Query OK, 0 rows affected (0.30 sec)
mysql> insert into m values(NULL, 1, 1, 2, NOW(), NULL, NULL, NULL, 0, NULL, NUL
L);
Query OK, 1 row affected (0.05 sec)
mysql> insert into m values(NULL, 1, 1, 2, NOW(), NULL, NULL, NULL, 0, NULL, NUL
L);
Query OK, 1 row affected (0.00 sec)
mysql> insert into m values(NULL, 1, 1, 2, NOW(), NULL, NULL, NULL, 0, NULL, NUL
L);
Query OK, 1 row affected (0.00 sec)
mysql> insert into m values(NULL, 1, 1, 2, NOW(), NULL, NULL, NULL, 0, NULL, NUL
L);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE m SET opened_at=NOW() WHERE `user_id_to` = 2 AND `user_id_from` IN
(1) AND
-> `deleted_to_at` IS NULL;
ERROR 1034 (HY000): Incorrect key file for table 'm'; try to repair it
mysql> ALTER TABLE m REMOVE PARTITIONING;
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> UPDATE m SET opened_at=NOW() WHERE `user_id_to` = 2 AND `user_id_from` IN
(1) AND
-> `deleted_to_at` IS NULL;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
[23 Mar 2012 10:00]
Valeriy Kravchuk
5.5.21 works as expected, so this is a regression bug.
[23 Mar 2012 11:22]
Grégory Duchatelet
Same with TIMESTAMP :
ALTER TABLE m
MODIFY created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
MODIFY `opened_at` TIMESTAMP NULL DEFAULT NULL,
MODIFY `deleted_from_at` TIMESTAMP NULL DEFAULT NULL,
MODIFY `deleted_to_at` TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE m REMOVE PARTITIONING;
ALTER TABLE m PARTITION BY RANGE(UNIX_TIMESTAMP(created_at))
(PARTITION pold VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01')),
PARTITION p1001 VALUES LESS THAN (UNIX_TIMESTAMP('2010-02-01')),
PARTITION p1002 VALUES LESS THAN (UNIX_TIMESTAMP('2010-03-01')),
PARTITION p1003 VALUES LESS THAN (UNIX_TIMESTAMP('2010-04-01')),
PARTITION p1004 VALUES LESS THAN (UNIX_TIMESTAMP('2010-05-01')),
PARTITION p1005 VALUES LESS THAN (UNIX_TIMESTAMP('2010-06-01')),
PARTITION p1006 VALUES LESS THAN (UNIX_TIMESTAMP('2010-07-01')),
PARTITION p1007 VALUES LESS THAN (UNIX_TIMESTAMP('2010-08-01')),
PARTITION p1008 VALUES LESS THAN (UNIX_TIMESTAMP('2010-09-01')),
PARTITION p1009 VALUES LESS THAN (UNIX_TIMESTAMP('2010-10-01')),
PARTITION p1010 VALUES LESS THAN (UNIX_TIMESTAMP('2010-11-01')),
PARTITION p1011 VALUES LESS THAN (UNIX_TIMESTAMP('2010-12-01')),
PARTITION p1012 VALUES LESS THAN (UNIX_TIMESTAMP('2011-01-01')),
PARTITION p1101 VALUES LESS THAN (UNIX_TIMESTAMP('2011-02-01')),
PARTITION p1102 VALUES LESS THAN (UNIX_TIMESTAMP('2011-03-01')),
PARTITION p1103 VALUES LESS THAN (UNIX_TIMESTAMP('2011-04-01')),
PARTITION p1104 VALUES LESS THAN (UNIX_TIMESTAMP('2011-05-01')),
PARTITION p1105 VALUES LESS THAN (UNIX_TIMESTAMP('2011-06-01')),
PARTITION p1106 VALUES LESS THAN (UNIX_TIMESTAMP('2011-07-01')),
PARTITION p1107 VALUES LESS THAN (UNIX_TIMESTAMP('2011-08-01')),
PARTITION p1108 VALUES LESS THAN (UNIX_TIMESTAMP('2011-09-01')),
PARTITION p1109 VALUES LESS THAN (UNIX_TIMESTAMP('2011-10-01')),
PARTITION p1110 VALUES LESS THAN (UNIX_TIMESTAMP('2011-11-01')),
PARTITION p1111 VALUES LESS THAN (UNIX_TIMESTAMP('2011-12-01')),
PARTITION p1112 VALUES LESS THAN (UNIX_TIMESTAMP('2012-01-01')),
PARTITION p1201 VALUES LESS THAN (UNIX_TIMESTAMP('2012-02-01')),
PARTITION p1202 VALUES LESS THAN (UNIX_TIMESTAMP('2012-03-01')),
PARTITION p1203 VALUES LESS THAN (UNIX_TIMESTAMP('2012-04-01')),
PARTITION p1204 VALUES LESS THAN (UNIX_TIMESTAMP('2012-05-01')),
PARTITION p1205 VALUES LESS THAN (UNIX_TIMESTAMP('2012-06-01')),
PARTITION p1206 VALUES LESS THAN (UNIX_TIMESTAMP('2012-07-01')),
PARTITION p1207 VALUES LESS THAN (UNIX_TIMESTAMP('2012-08-01')),
PARTITION p1208 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-01')),
PARTITION p1209 VALUES LESS THAN (UNIX_TIMESTAMP('2012-10-01')),
PARTITION p1210 VALUES LESS THAN (UNIX_TIMESTAMP('2012-11-01')),
PARTITION p1211 VALUES LESS THAN (UNIX_TIMESTAMP('2012-12-01')),
PARTITION p1212 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01')),
PARTITION p1301 VALUES LESS THAN (UNIX_TIMESTAMP('2013-02-01')),
PARTITION p1302 VALUES LESS THAN (UNIX_TIMESTAMP('2013-03-01')),
PARTITION p1303 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),
PARTITION p1304 VALUES LESS THAN (UNIX_TIMESTAMP('2013-05-01')),
PARTITION p1305 VALUES LESS THAN (UNIX_TIMESTAMP('2013-06-01')),
PARTITION p1306 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),
PARTITION p1307 VALUES LESS THAN (UNIX_TIMESTAMP('2013-08-01')),
PARTITION p1308 VALUES LESS THAN (UNIX_TIMESTAMP('2013-09-01')),
PARTITION p1309 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),
PARTITION p1310 VALUES LESS THAN (UNIX_TIMESTAMP('2013-11-01')),
PARTITION p1311 VALUES LESS THAN (UNIX_TIMESTAMP('2013-12-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE);
[23 Mar 2012 11:45]
Mattias Jonsson
I cannot repeat the error in the latest mysql-trunk. I will continue to analyze where it got fixed.
[27 Mar 2012 7:25]
Grégory Duchatelet
Hope this help : I can repeat this bug in 5.6.3.
[14 Sep 2012 9:29]
Jon Stephens
Fixed in 5.6.7. Closed.
