Bug #46639 1030 (HY000): Got error 124 from storage engine on INSERT ... SELECT ...
Submitted: 10 Aug 2009 22:51 Modified: 16 Sep 2009 8:57
Reporter: Roel Van de Paar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.36 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[10 Aug 2009 22:51] Roel Van de Paar 
Description:
mysql> INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid;
ERROR 1030 (HY000): Got error 124 from storage engine

Repeatable testcase below.

If the first index is dropped, it works fine:

mysql> ALTER TABLE met DROP INDEX `mid`;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

How to repeat:
CREATE TABLE vmm (vmid int not null, mid int not null) ENGINE=InnoDB;

CREATE TABLE met (tmin integer NOT NULL, mid int NOT NULL, nid int NOT NULL, cid int NOT NULL, aid int NOT NULL, val bigint NOT NULL
,INDEX(mid)
,INDEX(tmin,mid,nid, cid)
,INDEX(tmin, mid, cid)
) ENGINE=MYISAM, AVG_ROW_LENGTH=60, MAX_ROWS=4000000000
PARTITION BY LIST( ((tmin DIV 60) MOD 24) ) (
PARTITION hour0to3 VALUES IN (0,1,2,3),
PARTITION hour4to7 VALUES IN (4,5,6,7),
PARTITION hour8to11 VALUES IN (8,9,10,11),
PARTITION hour12to15 VALUES IN (12,13,14,15),
PARTITION hour16to19 VALUES IN (16,17,18,19),
PARTITION hour20to23 VALUES IN (20,21,22,23)
);

INSERT INTO vmm VALUES (399, 22);
INSERT INTO met values (1, 22, 0, 1, 1,1), (1, 42, 0, 1, 1,1);

INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM WHERE MD.mid = VM.mid;
[10 Aug 2009 22:54] Roel Van de Paar 
Verifying as D2
[10 Aug 2009 22:57] Roel Van de Paar 
Also see bug #44657
[11 Aug 2009 0:18] Roel Van de Paar 
Customer reported another workaround: 'If each partition has at least one row, then everything is ok.'
[13 Aug 2009 16:15] MySQL Verification Team 
errno 124 is returned as "Wrong medium type", which is wrong in this case. Correct errno text is "Wrong index given to function".

One thing to be checked here is whether result set from SELECT is first correctly stored in a temporary table.
[17 Aug 2009 3:09] Roel Van de Paar 
Possible workaround may be to use a temporary table:

mysql> create table testtemp(a int, b int, c int, d int, e int, f int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO testtemp (a,b,c,d,e,f) SELECT 1, 399, 0,1,1,1 FROM met MD, vmm VM where MD.mid=VM.mid;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from testtemp;
+------+------+------+------+------+------+
| a    | b    | c    | d    | e    | f    |
+------+------+------+------+------+------+
|    1 |  399 |    0 |    1 |    1 |    1 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO met (tmin, mid, nid,cid, aid, val) SELECT * from testtemp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
[18 Aug 2009 10:49] Mattias Jonsson 
Problem seems to be that an empty myisam partition is used for both reading and writing, which leads to disabling of indexes in ha_myisam::start_bulk_insert which causes a read by any index to fail in mi_check_index.

Can be solved by either delaying the start_bulk_insert (see Bug#35845) or fallback on full partition scan (which in this specific case would not find any rows).

I propose to backport bug#35845, which also would help to solve bug#45840.
[18 Aug 2009 13:43] Mattias Jonsson 
This was only affecting myisam, so I will add a small patch there instead.
[18 Aug 2009 14:08] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80995

3072 Mattias Jonsson	2009-08-18
      Bug#46639: 1030 (HY000): Got error 124 from storage engine on
      INSERT ... SELECT ...
      
      Problem was that when bulk insert is used on an empty
      table/partition, it disables the indexes for better
      performance, but in this specific case it also tries
      to read from that partition using an index, which is
      not possible since it has been disabled.
      
      Solution was to allow index reads on disabled indexes
      if there are no records.
     @ mysql-test/r/partition.result
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        updated result file
     @ mysql-test/t/partition.test
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        Added testcase
     @ storage/myisam/mi_search.c
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        Return KEY_NOT_FOUND instead of WRONG_INDEX when
        there are no rows.
[21 Aug 2009 15:27] Mattias Jonsson 
Manually verified that bug#44657 is a duplicate of this (an assert on the same place failed).
[21 Aug 2009 15:39] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81326

3072 Mattias Jonsson	2009-08-21
      Bug#46639: 1030 (HY000): Got error 124 from storage engine on
      INSERT ... SELECT ...
      
      Problem was that when bulk insert is used on an empty
      table/partition, it disables the indexes for better
      performance, but in this specific case it also tries
      to read from that partition using an index, which is
      not possible since it has been disabled.
      
      Solution was to allow index reads on disabled indexes
      if there are no records.
      
      Also reverted the patch for bug#38005, since that was a workaround
      in the partitioning engine instead of a fix in myisam.
     @ mysql-test/r/partition.result
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        updated result file
     @ mysql-test/t/partition.test
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        Added testcase
     @ sql/ha_partition.cc
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        reverted the patch for bug#38005, since that was a workaround
        around this problem, not needed after fixing it in myisam.
     @ storage/myisam/mi_search.c
        Bug#46639: 1030 (HY000): Got error 124 from storage engine on
        INSERT ... SELECT ...
        
        Return HA_ERR_END_OF_FILE instead of HA_ERR_WRONG_INDEX
        when there are no rows.
[24 Aug 2009 7:18] Mattias Jonsson 
pushed into mysql-5.1-bugteam and mysql-pe
[2 Sep 2009 16:42] Bugs System 
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:mattias.jonsson@sun.com-20090821160038-zfmtr3hyo8pv00in) (merge vers: 5.1.39) (pib:11)
[3 Sep 2009 15:29] Jon Stephens 
Documented bugfix in the 5.1.39 changelog as follows:

        An INSERT ... SELECT statement on an empty partition of a
        user-partitioned table failed with ERROR 1030 (HY000): Got error
        124 from storage engine.

Set NDI status pending merge to 5.4 tree.
[14 Sep 2009 16:04] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 8:57] Jon Stephens 
Also documented bugfix in the 5.4.4 changelog. Modified changelog entry (per BUG#44567) to read:

        An INSERT ... SELECT statement on an empty partition of a
        user-partitioned table failed with ERROR 1030 (HY000): Got error
        124 from storage engine. This issue also caused queries run 
        against a partitioned table while a LOAD DATA CONCURRENT INFILE 
        statement was in progress to fail with the same error.

Closed.
[1 Oct 2009 5:58] Bugs System 
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System 
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System 
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:31] Paul DuBois 
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System 
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)