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:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.28, 5.6.9 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[27 Dec 2012 23:45] Elena Stepanova
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;
[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]