| Bug #67982 | Got error 124 from storage engine on DELETE from a partitioned table | ||
|---|---|---|---|
| Submitted: | 27 Dec 2012 23:45 | Modified: | 1 Nov 2013 9:47 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.5.28, 5.6.9 | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[28 Dec 2012 0:05]
MySQL Verification Team
Thank you for the bug report.
d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 Source distribution
Copyright (c) 2000, 2012, 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 5.5 >use d1
Database changed
mysql 5.5 >CREATE TABLE IF NOT EXISTS t1 ( a INT, b INT, KEY(b) )
-> ENGINE = MYISAM PARTITION BY LIST (a) (
-> PARTITION p0 VALUES IN (1, NULL),
-> PARTITION p1 VALUES IN (4, 6, 3),
-> PARTITION p2 VALUES IN (8, 2, 5)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql 5.5 >INSERT INTO t1 VALUES (5,0), (6,3), (4,9), (8,4);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.5 >ALTER TABLE t1 DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)
mysql 5.5 >ALTER TABLE t1 REBUILD PARTITION p1;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.5 >DELETE FROM t1 WHERE a = 8 ORDER BY b, a LIMIT 1;
ERROR 1030 (HY000): Got error 124 from storage engine
mysql 5.5 >
d:\dbs>d:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.68-Win X64 Source distribution
Copyright (c) 2000, 2012, 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 5.1 >use test
Database changed
mysql 5.1 >CREATE TABLE IF NOT EXISTS t1 ( a INT, b INT, KEY(b) )
-> ENGINE = MYISAM PARTITION BY LIST (a) (
-> PARTITION p0 VALUES IN (1, NULL),
-> PARTITION p1 VALUES IN (4, 6, 3),
-> PARTITION p2 VALUES IN (8, 2, 5)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql 5.1 >INSERT INTO t1 VALUES (5,0), (6,3), (4,9), (8,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.1 >ALTER TABLE t1 DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)
mysql 5.1 >ALTER TABLE t1 REBUILD PARTITION p1;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.1 >DELETE FROM t1 WHERE a = 8 ORDER BY b, a LIMIT 1;
Query OK, 1 row affected (0.01 sec)
[1 Nov 2013 9:47]
Erlend Dahl
Documented fix as follows in the 5.5.35, 5.6.15, and 5.7.3 changelogs: After disabling the parent table's indexes with ALTER TABLE ... DISABLE KEYS, rebuilding any of its partitions enabled the indexes on those partitions, leading MyISAM to fail with an error when the optimizer tried to use one of the affected indexes. Now in such cases, we check for disabled indexes on the table before rebuilding any of its partitions. If the indexes have been disabled, then we disable them on the partition following the rebuild.
[4 Dec 2013 9:38]
Laurynas Biveinis
5.5$ bzr log -r 4522
------------------------------------------------------------
revno: 4522
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.5
timestamp: Mon 2013-10-21 12:07:02 +0530
message:
Bug #16051817 GOT ERROR 124 FROM STORAGE ENGINE
ON DELETE FROM A PARTITIONED TABLE
PROBLEM
-------
The user first disables all the non unique indexes
in the table and then rebuilds one partition.
During rebuild the indexes on that particular
partition are enabled. Now when we give a query
the optimizer is unaware that on one partition
indexes are enabled and if the optimizer selects
that index,myisam thinks that the index is not
active and gives an error.
FIX
---
Before rebuilding a partition check whether non
unique indexes are disabled on the partitons.
If they are disabled then after rebuild disable
the index on the partition.
[Approved by Mattiasj #rb3469]
Description: CREATE TABLE IF NOT EXISTS t1 ( a INT, b INT, KEY(b) ) ENGINE = MYISAM PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (1, NULL), PARTITION p1 VALUES IN (4, 6, 3), PARTITION p2 VALUES IN (8, 2, 5) ); INSERT INTO t1 VALUES (5,0), (6,3), (4,9), (8,4); ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 REBUILD PARTITION p1; bug.mdev3978-4 [ fail ] Test ended at 2012-12-28 03:44:31 CURRENT_TEST: bug.mdev3978-4 mysqltest: At line 12: query 'DELETE FROM t1 WHERE a = 8 ORDER BY b, a LIMIT 1' failed: 1030: Got error 124 from storage engine Could not reproduce on MySQL 5.1. How to repeat: --source include/have_partition.inc CREATE TABLE IF NOT EXISTS t1 ( a INT, b INT, KEY(b) ) ENGINE = MYISAM PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (1, NULL), PARTITION p1 VALUES IN (4, 6, 3), PARTITION p2 VALUES IN (8, 2, 5) ); INSERT INTO t1 VALUES (5,0), (6,3), (4,9), (8,4); ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 REBUILD PARTITION p1; DELETE FROM t1 WHERE a = 8 ORDER BY b, a LIMIT 1;