Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3978

Query: DELETE FROM .. ORDER BY .. LIMIT 1 failed: 1030 Got error 124 "Wrong index given to function" from storage engine

        Details

          Description

          The problem also exists on MySQL 5.5/5.6 and filed as http://bugs.mysql.com/bug.php?id=67982

          
--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;

          bug.mdev3978-4                           [ fail ]
        Test ended at 2012-12-28 03:48:24

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 "Wrong index given to function" from storage engine

          revision-id: monty@askmonty.org-20121217203456-gjlhf7eslleda9rz
date: 2012-12-17 22:34:56 +0200
revno: 3597
branch: maria/5.5

          Also reproducible on 10.0-base and on last release versions 5.5.28a and 10.0.0.
          Not reproducible on MariaDB 5.3 or MySQL 5.1.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7059 Add test case for MDEV-3978 / MySQL#67982 / Oracle#16051817

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Open

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Upstream bug fixed in MySQL 5.5.35, 5.6.15, 5.7.3.
                  Both MariaDB 5.5 and 10.0 also have the fix, apparently through the merge of MySQL 5.5.

                  revno: 4522
revision-id: aditya.a@oracle.com-20131021063702-ak3uppt7h8a0czun
parent: mattias.jonsson@oracle.com-20131019132951-3ah7cc1jcb32bu98
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]
                  Show
                  elenst Elena Stepanova added a comment - Upstream bug fixed in MySQL 5.5.35, 5.6.15, 5.7.3. Both MariaDB 5.5 and 10.0 also have the fix, apparently through the merge of MySQL 5.5. revno: 4522 revision-id: aditya.a@oracle.com-20131021063702-ak3uppt7h8a0czun parent: mattias.jonsson@oracle.com-20131019132951-3ah7cc1jcb32bu98 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]

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: