Bug #38005 Partitions: error with insert select

Submitted: 10 Jul 2008 0:22 Modified: 17 Oct 2008 17:51
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Linux (SUSE 10 | 32-bit Ubuntu 64bit)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: regression

[10 Jul 2008 0:22] Peter Gulutzan 
Description:
I create a partitioned indexed MyISAM table.
I insert a row.
I insert another row, using INSERT ... SELECT.
I insert another row, using INSERT ... SELECT.
I see an error:
ERROR 1030 (HY000): Got error 124 from storage engine

This happens only if I use one of these partitioning clauses:
PARTITION BY HASH (column_name) PARTITIONS n;/* where n > 1 */
PARTITION BY KEY (column_name) PARTITIONS n; /* where n > 1 */
PARTITION BY LIST (column_name) (PARTITION ..., PARTITION ...);

This happens with mysql-5.1 and mysql-5.1-bugteam, pulled today,
so I doubt there is a connection with Bug#35931.

How to repeat:
mysql> create table t (s1 int) partition by hash(s1) partitions 2;
Query OK, 0 rows affected (0.08 sec)

mysql> create index i on t (s1);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t select s1 from t;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select s1 from t;
ERROR 1030 (HY000): Got error 124 from storage engine
[10 Jul 2008 4:06] Valeriy Kravchuk 
Works for me in 5.1.25:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t (s1 int) partition by hash(s1) partitions 2;
Query OK, 0 rows affected (0.25 sec)

mysql> create index i on t (s1);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t select s1 from t;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select s1 from t;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t select s1 from t;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t select s1 from t;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

So, looks like some recent regression.
[10 Jul 2008 13:53] Susanne Ebrecht 
This is recent regression.

I tried on tree that I pulled yesterday evening:

mysql> create table t (s1 int) partition by hash(s1) partitions 2;
Query OK, 0 rows affected (0.08 sec)

mysql> create index i on t (s1);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t select s1 from t;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select s1 from t;
ERROR 1030 (HY000): Got error 124 from storage engine

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.28-debug | 
+--------------+
[10 Jul 2008 14:00] Susanne Ebrecht 
This also hits MySQL 6.0 bzr tree.
[21 Jul 2008 6:33] 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/50094

2708 Alexey Botchkov	2008-07-21
      Bug#38005 Partitions: error with insert select.
      
      
      MyISAM blocks index usage for bulk insert into zero-records tables.
      See ha_myisam::start_bulk_insert() lines from
      ...
          if (file->state->records == 0 ...
      ...
      
      That causes problems for partition engine when some partitions have records some not
      as the engine uses same access method for all partitions.
      
      Fixed by returning EOF from ha_myisam::index_first for empty tables.
[7 Aug 2008 6:07] 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/51073

2708 Alexey Botchkov	2008-08-07
      Bug#38005 Partitions: error with insert select.
            
            
            MyISAM blocks index usage for bulk insert into zero-records tables.
            See ha_myisam::start_bulk_insert() lines from
            ...
                if (file->state->records == 0 ...
            ...
            
            That causes problems for partition engine when some partitions have records some not
            as the engine uses same access method for all partitions.
            
            Fixed by returning EOF from ha_myisam::index_first for empty tables.
      
      per-file comments:
        mysql-test/r/partition.result
              Bug#38005 Partitions: error with insert select.
              test result
      
        mysql-test/t/partition.test
              Bug#38005 Partitions: error with insert select.
              test case
      
        storage/myisam/ha_myisam.cc
              Bug#38005 Partitions: error with insert select.
              ha_myisam::index_first and 
              ha_myisam::index_last modified to return EOF if the table has no records.
[20 Aug 2008 14:51] 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/52060

2683 Alexey Botchkov	2008-08-20
      Bug#38005 Partitions: error with insert select.
                  
      MyISAM blocks index usage for bulk insert into zero-records tables.
      See ha_myisam::start_bulk_insert() lines from
      ...
          if (file->state->records == 0 ...
      ...
      
      That causes problems for partition engine when some partitions have records some not
      as the engine uses same access method for all partitions.
      
      Now partition engine doesn't call index_first/index_last
      for empty tables.
      
      per-file comments:
              mysql-test/r/partition.result
                    Bug#38005 Partitions: error with insert select.
                    test result
            
              mysql-test/t/partition.test
                    Bug#38005 Partitions: error with insert select.
                    test case
            
              sql/ha_partition.cc
                    Bug#38005 Partitions: error with insert select.
                    ha_myisam::index_first and 
                    ha_myisam::index_last not called for empty tables.
[6 Oct 2008 11:56] 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/55442

2683 Alexey Botchkov	2008-10-06
      Bug#38005 Partitions: error with insert select.
      
      MyISAM blocks index usage for bulk insert into zero-records tables.
      See ha_myisam::start_bulk_insert() lines from
      ...
          if (file->state->records == 0 ...
      ...
      
      That causes problems for partition engine when some partitions have records some not
      as the engine uses same access method for all partitions.
      
      Now partition engine doesn't call index_first/index_last
      for empty tables.
      
      per-file comments:
      mysql-test/r/partition.result
        Bug#38005 Partitions: error with insert select.
        test result
      
      mysql-test/t/partition.test
        Bug#38005 Partitions: error with insert select.
        test case
      
      sql/ha_partition.cc
        Bug#38005 Partitions: error with insert select.
        ha_engine::index_first and
        ha_engine::index_last not called for empty tables.
[6 Oct 2008 14:19] Mattias Jonsson 
Patch approved, OK to push
[6 Oct 2008 14:25] 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/55458

2690 Alexey Botchkov	2008-10-06
      Bug#38005 Partitions: error with insert select.
      
      MyISAM blocks index usage for bulk insert into zero-records tables.
      See ha_myisam::start_bulk_insert() lines from
      ...
          if (file->state->records == 0 ...
      ...
      
      That causes problems for partition engine when some partitions have records some not
      as the engine uses same access method for all partitions.
      
      Now partition engine doesn't call index_first/index_last
      for empty tables.
      
      per-file comments:
        mysql-test/r/partition.result
              Bug#38005 Partitions: error with insert select.
              test result
      
        mysql-test/t/partition.test
              Bug#38005 Partitions: error with insert select.
              test case
      
        sql/ha_partition.cc
              Bug#38005 Partitions: error with insert select.
              ha_engine::index_first and
              ha_engine::index_last not called for empty tables.
[7 Oct 2008 18:55] Paul DuBois 
Noted in 5.1.29 changelog.

Using INSERT ... SELECT to insert records into a partitioned MyISAM
table could fail if some partitions are empty and others are not.

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 17:50] Bugs System 
Pushed into 5.1.30  (revid:holyfoot@mysql.com-20081006131420-fid8uxfb4vlwfohf) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)
[9 Oct 2008 18:27] Paul DuBois 
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:45] Bugs System 
Pushed into 6.0.8-alpha  (revid:holyfoot@mysql.com-20081006131420-fid8uxfb4vlwfohf) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[17 Oct 2008 17:51] Paul DuBois 
Noted in 6.0.8 changelog.
[28 Oct 2008 21:05] Bugs System 
Pushed into 5.1.29-ndb-6.2.17  (revid:holyfoot@mysql.com-20081006131420-fid8uxfb4vlwfohf) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:24] Bugs System 
Pushed into 5.1.29-ndb-6.3.19  (revid:holyfoot@mysql.com-20081006131420-fid8uxfb4vlwfohf) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:50] Bugs System 
Pushed into 5.1.29-ndb-6.4.0  (revid:holyfoot@mysql.com-20081006131420-fid8uxfb4vlwfohf) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
[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)
[14 Sep 2009 16:05] 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)
[1 Oct 2009 5:59] 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)
[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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.099 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.