Bug #38005 Partitions: error with insert select

• Submitted: 10 Jul 2008 0:22
• Modified: 17 Oct 2008 17:51
• Reporter: Peter Gulutzan
• Status: Closed
• 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:[e-mail]-20081006131420-fid8uxfb4vlwfohf) (version source revid:[e-mail]-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:[e-mail]-20081006131420-fid8uxfb4vlwfohf) (version source revid:[e-mail]-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:[e-mail]-20081006131420-fid8uxfb4vlwfohf) (version source revid:[e-mail]-20081028140209-u4emkk1xphi5tkfb) (pib:5)

[28 Oct 2008 22:24] Bugs System

Pushed into 5.1.29-ndb-6.3.19  (revid:[e-mail]-20081006131420-fid8uxfb4vlwfohf) (version source revid:[e-mail]-20081028194045-0353yg8cvd2c7dd1) (pib:5)

[1 Nov 2008 9:50] Bugs System

Pushed into 5.1.29-ndb-6.4.0  (revid:[e-mail]-20081006131420-fid8uxfb4vlwfohf) (version source revid:[e-mail]-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

[2 Sep 2009 16:42] Bugs System

Pushed into 5.1.39 (revid:[e-mail]-20090902154533-8actmfcsjfqovgsb) (version source revid:[e-mail]-20090821160038-zfmtr3hyo8pv00in) (merge vers: 5.1.39) (pib:11)

[14 Sep 2009 16:05] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090914155317-m1g9wodmndzdj4l1) (version source revid:[e-mail]-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:[e-mail]-20091001055605-ap2kiaarr7p40mmv) (version source revid:[e-mail]-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:[e-mail]-20091001072547-kv17uu06hfjhgjay) (version source revid:[e-mail]-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:[e-mail]-20091001123013-g9ob2tsyctpw6zs0) (version source revid:[e-mail]-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:[e-mail]-20091005103850-dwij2dojwpvf5hi6) (version source revid:[e-mail]-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)