Bug #31210 INSERT DELAYED crashes server when used on partitioned table
Submitted: 26 Sep 2007 17:34 Modified: 20 Jun 2010 22:57
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.20 OS:Mac OS X
Assigned to: Ingo Strüwing CPU Architecture:Any
Triage: D4 (Minor)

[26 Sep 2007 17:34] Tobias Asplund
Description:
See subject

How to repeat:
mysql> CREATE TABLE t1 ( a INT ) ENGINE = MyISAM PARTITION BY HASH(a) PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT DELAYED INTO t1 VALUES (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
Create one background thread per partition so that DELAYED can be used to bypass some of the incredible slow downs with partitioned tables and bulk inserts?
[27 Sep 2007 1:04] Miguel Solorzano
Thank you for the bug report. Verified on Linux 64-bit too with latest source tree:

[miguel@luar 5.1]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-beta-debug Source distribution

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

mysql> CREATE TABLE t1 ( a INT ) ENGINE = MyISAM PARTITION BY HASH(a) PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT DELAYED INTO t1 VALUES (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[10 Oct 2007 9:56] Mikael Ronström
Crashing bug => P1
[11 Oct 2007 14:32] Ingo Strüwing
When starting on the bug, Mikael and I believed that we would just need to disallow DELAYED for partitioned tables.

I found that we do this already. The crash happens during open of the table and parsing the partition string from the .frm file. The table_flags are evaluated after the open only.

Adding lex_start(thd) before open_ltable() in the delayed thread cures the crash. But Dmitri said that this does only hide the real bug. He analyzed:

"
Basically what we do when we open partition code is that

a) we use Name_resolution_context from the current lex (not the proxy lex) during the parsing (and we probably can get rid of it more or less easily... since we will get rid of references to this context anyway later). See func_expr->walk(&Item::change_context_processor, 0, (uchar*) context); in sql_partition.cc.

b) then we again use Name_resolution_context from the current lex with temporarily substituted values to resolve fields used in partitioning expression and it can be problematic to change the latter...

I think that we can do something even with step b) like using Name_resolution_context allocated on the stack. After all, even now, if we need this context later then probably we have a bug...
"
[punctuation changed by me.]
[12 Oct 2007 10:28] 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/35461

ChangeSet@1.2571, 2007-10-12 12:28:09+02:00, istruewing@stella.local +3 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  
  Not to be pushed. Internal commit. It does just hide the problem.
  
  Trying INSERT DELAYED on a partitioned table, that has not been
  used right before, crashes the server. When a table is used for
  select or update, it is kept open for some time. This period I
  mean with "right before".
  
  Information about partitioning of a table is stored in form of
  a string in the .frm file. Parsing of this string requires a
  correctly set up lexical analyzer (lex). The partitioning code
  uses a new temporary instance of a lex. But it does still refer
  to the previously active lex. The delayd insert thread does not
  initialize its lex though...
  
  Added initialization for thd->lex before open table in the delayed
  thread. This hides the crash. But lex handling of the partition
  code may still need a fix.
[14 Oct 2007 17:28] 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/35536

ChangeSet@1.2572, 2007-10-14 19:28:30+02:00, istruewing@stella.local +13 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  
  Not to be pushed. Experimental patch. It shows at which places
  it would be necessary to call lex_start() when all tables are
  partitioned and need to parse the .frm file.
  
  Added 'is_lex_started' to test if lex is initialized.
  Initialized lex at many places for later use in open_table().
[5 Nov 2007 15:02] Ingo Strüwing
We agreed to push the above patches to 5.1. In 6.0 we will add a fix for the partitioning code. See Bug#32115 - Bad use of Name_resolution_context from current LEX in partitioning.
[5 Nov 2007 15: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/37100

ChangeSet@1.2571, 2007-11-05 16:25:40+01:00, istruewing@stella.local +17 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  
  Trying INSERT DELAYED on a partitioned table, that has not been
  used right before, crashes the server. When a table is used for
  select or update, it is kept open for some time. This period I
  mean with "right before".
  
  Information about partitioning of a table is stored in form of
  a string in the .frm file. Parsing of this string requires a
  correctly set up lexical analyzer (lex). The partitioning code
  uses a new temporary instance of a lex. But it does still refer
  to the previously active lex. The delayd insert thread does not
  initialize its lex though...
  
  Added initialization for thd->lex before open table in the delayed
  thread and at all other places where it is necessary to call
  lex_start() if all tables would be partitioned and need to parse
  the .frm file.
[10 Nov 2007 13: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/37514

ChangeSet@1.2611, 2007-11-10 14:36:25+01:00, istruewing@stella.local +1 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  
  Post-merge fix
  
  A new need for lex initialization arose.
[10 Nov 2007 22:21] Ingo Strüwing
Queued to 6.0-engines, 5.1-engines.
[11 Nov 2007 19:38] 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/37545

ChangeSet@1.2612, 2007-11-11 20:38:28+01:00, istruewing@stella.local +3 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
                partitioned table
    
  Post-pushbuild fix
    
  Pushbuild detected a new need for lex initialization in
  embedded server.
  
  Fixed test for INSERT DELAYED in partitions_hash.test so that
  it works with embedded server.
[12 Nov 2007 12: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/37571

ChangeSet@1.2613, 2007-11-12 13:07:54+01:00, istruewing@stella.local +1 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
                  partitioned table
      
  Post-pushbuild fix
      
  Pushbuild detected yet another need for lex initialization in
  embedded server.
[13 Nov 2007 9:13] 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/37640

ChangeSet@1.2656, 2007-11-13 10:13:08+01:00, istruewing@stella.local +1 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  Post-merge fix
[13 Nov 2007 14:16] 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/37669

ChangeSet@1.2662, 2007-11-13 15:15:50+01:00, istruewing@stella.local +2 -0
  Bug#31210 - INSERT DELAYED crashes server when used on
              partitioned table
  Post-merge fix. Privileges are now in column 32.
[14 Nov 2007 9:41] Bugs System
Pushed into 6.0.4-alpha
[14 Nov 2007 9:46] Bugs System
Pushed into 5.1.23-rc
[15 Nov 2007 14:55] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.23 and 6.0.4 changelogs.
[31 Mar 2008 16:38] Hartmut Holzgraefe
Reset to "Documenting" for two reasons:

* the change log entry on http://dev.mysql.com/doc/refman/5.1/en/news-5-1-23.html says: "Partitioning: INSERT DELAYED into a partitioned table crashed the server. (Bug#31201)" so there is a typo in the bug number (last two digits swapped)

* at least the "INSERT DELAYED" manual page http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html does not mention the limitation that DELAYED won't work with any partitioned table, even if the table type is one of those listed as supporting DELAYED (MyISAM, MEMORY, ARCHIVE, BLACKHOLE)
[31 Mar 2008 16:43] Hartmut Holzgraefe
I just checked the "Partion Limitations" pages and it doesn't seem to be mentioned there either.
[31 Mar 2008 17:04] Ingo Strüwing
But
12.2.4.2. INSERT DELAYED Syntax
says
INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and (as of MySQL 5.1.19) BLACKHOLE tables.
Isn't this enough?
[2 Apr 2008 7:44] Jon Stephens
Hi! Thanks for spotting this problem with the docs, which I've fixed as below:

1. Fixed the bug number referenced in the changelog entry, and updated the entry itself to read:

        INSERT DELAYED on a partitioned table
        crashed the server. The server now rejects the statement with an 
        error.

2. Added the following to partition-limitations:

          DELAYED option not supported. Use of INSERT DELAYED to insert rows 
          into a partitioned table is not supported. Beginning with MySQL 
          5.1.23 [6.0.4], attempting to do so fails with an error.

3. Added the following to insert-delayed:

              INSERT DELAYED is not supported for partitioned
              tables.

However, I am setting this bug back to Open because of the following:

mysql> SHOW CREATE TABLE part1\G
*************************** 1. row ***************************
       Table: part1
Create Table: CREATE TABLE `part1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=1409286290 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1 div 3) PARTITIONS 4  */
1 row in set (0.00 sec)

mysql> INSERT DELAYED INTO part1 VALUES ('', 5);
ERROR 1031 (HY000): Table storage engine for 'part1' doesn't have this option

I believe that this error message is highly misleading/confusing, because it might well lead some users to believe that the storage engine in use, and not the fact that the table is partitioned, is the source of the problem. It ought to say something like this:

DELAYED option not supported for partitioned tables

Or this:

INSERT DELAYED not supported for partitioned tables

Either of which addresses the issue much better IMHO.
[2 Apr 2008 8:22] Jon Stephens
BTW, if my previous comment didn't make this clear, the answer to Ingo's question is "No", because partitioning is not a storage engine, it is (at least in theory) engine-agnostic:

mysql> SHOW CREATE TABLE part1\G
*************************** 1. row ***************************
       Table: part1
Create Table: CREATE TABLE `part1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=1409286290 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1 div 3) PARTITIONS 4  */
1 row in set (0.00 sec)

mysql> ALTER TABLE part1 ENGINE = InnoDB;
Query OK, 443558 rows affected (30.51 sec)
Records: 443558  Duplicates: 0  Warnings: 0
[2 Apr 2008 14:25] Trudy Pelzer
Changing defect_class to D4 as the report now
is about a misleading error message; the crash
has been fixed.
[8 Apr 2008 12:14] 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/45054

ChangeSet@1.2576, 2008-04-08 14:13:53+02:00, mattiasj@witty. +6 -0
  Bug#31210: INSERT DELAYED crashes server when used on partitioned
  tables
  
  Problem was an unclear error message since it could suggest that
  MyISAM did not support INSERT DELAYED.
  
  Changed the error message to say that DELAYED is not supported by the
  table, instead of the table's storage engine.
  The confusion is that a partitioned table is in somewhat sense using
  the partitioning storage engine, which in turn uses the ordinary
  storage engine. By saying that the table does not support DELAYED we
  do not give any extra informantion about the storage engine or if it
  is partitioned.
[3 Jun 2008 11: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/47353

2659 Mattias Jonsson	2008-06-03
      Bug#31210: INSERT DELAYED crashes server when used on partitioned tables
        
      Problem was an unclear error message since it could suggest that
      MyISAM did not support INSERT DELAYED.
      Changed the error message to say that DELAYED is not supported by the
      table, instead of the table's storage engine.
      The confusion is that a partitioned table is in somewhat sense using
      the partitioning storage engine, which in turn uses the ordinary
      storage engine. By saying that the table does not support DELAYED we
      do not give any extra informantion about the storage engine or if it
      is partitioned.
[3 Jun 2008 11:50] 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/47358

2664 Mattias Jonsson	2008-06-03 [merge]
      Bug#31210: INSERT DELAYED crashes server when used on partitioned tables
      
      Manual merge from 5.1
[4 Jun 2008 8:41] 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/47401

2665 Mattias Jonsson	2008-06-04
      Bug#31210: INSERT DELAYED crashes server when used on partitioned tables
      
      Update of results files after new error code introduced in 5.1
[4 Jun 2008 9:30] Mattias Jonsson
The new error code/message is pushed into mysql-6.0-bugteam and mysql-5.1-bugteam
[9 Jun 2008 12: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/47599

2661 Mattias Jonsson	2008-06-09
      Bug#31210: INSERT DELAYED crashes server when used on partitioned tables
      
      post push patch
      
      push build found tree failing test when using:
      --ps-protocol --mysqld=--binlog-format=row
      Changed to the new error message.
[9 Jun 2008 18:50] 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/47635

2661 Mattias Jonsson	2008-06-09
      Bug#31210: INSERT DELAYED crashes server when used on partitioned tables
      
      post push patch
      
      push build found tree failing test when using:
      --ps-protocol --mysqld=--binlog-format=row
      Changed to the new error message.
[19 Jul 2008 15:30] Ingo Strüwing
The error message fix has been pushed to main meanwhile.
However I don't know to which version it was pushed.
At the moment it is in 6.0.7 and 5.1.28.
[28 Jul 2008 16:49] Bugs System
Pushed into 5.1.27  (revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (version source revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (pib:3)
[15 Sep 2009 8:52] Jon Stephens
Added the following entry to the 5.1.28 and 6.0.7 changelogs:

        Attempting to execute an INSERT DELAYED statement on a
        partitioned table produced the error -Table storage engine for
        'table' doesn't have this option-, which did not reflect the
        source of the error accurately. The error message returned in
        such cases has been changed to -DELAYED option not supported for
        table 'table'-.

Bug already closed, no need to update status.
[5 May 2010 15:08] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 3:05] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:09] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:05] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 May 2010 1:07] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:14] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:01] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:42] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)