Bug #54106 assert in Protocol::end_statement, INSERT IGNORE ... SELECT ... UNION SELECT ...
Submitted: 31 May 2010 15:20 Modified: 14 Oct 2010 15:35
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1, 5.6.99-m4-debug.5.5.5-m3 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[31 May 2010 15:20] Matthias Leich
Description:
The assert happens in sql/protocol.cc line 540:
  default:
    DBUG_ASSERT(0);  <---------------
    error= send_ok(thd->server_status, 0, 0, 0, NULL);
    break

RQG test with
- 64 threads
- derivate of the WL5005_* grammars

Result on 5.6.99-m4-debug
mysql-next-mr revno: 3153 2010-05-31
------------------------------------
Thread 1 (process 11047):
#0  0x00007f1d6a56dce6 in pthread_kill ()
                       from /lib64/libpthread.so.0
#1  0x00000000009fb6a1 in my_write_core (sig=6)
                       at .../mysys/stacktrace.c:326
#2  0x000000000053cbf6 in handle_segfault (sig=6)
                       at .../sql/mysqld.cc:2787
#3  <signal handler called>
#4  0x00007f1d697855c5 in raise ()
                       from /lib64/libc.so.6
#5  0x00007f1d69786bb3 in abort ()
                       from /lib64/libc.so.6
#6  0x00007f1d6977e1e9 in __assert_fail ()
                       from /lib64/libc.so.6
#7  0x000000000054981a in Protocol::end_statement (this=0x7f1d5c1bc9a0)
                       at .../sql/protocol.cc:540
#8  0x00000000005d55e6 in dispatch_command (command=COM_QUERY, thd=0x7f1d5c1bc4e8,
    packet=0x7f1d5c252759 "INSERT LOW_PRIORITY IGNORE INTO testdb_S . t1_base1_S  ( `pk` , `col_int_key` , `pk`  ) SELECT  SQL_CACHE `col_int` , `pk` , `col_int_key`  FROM ( SELECT * FROM testdb_S . t1_base2_N  )  A UNION SELEC"...
, packet_length=294)
                       at .../sql/sql_parse.cc:1461
#9  0x00000000005d5985 in do_command (thd=0x7f1d5c1bc4e8)
                       at .../sql/sql_parse.cc:771
#10 0x00000000006a0df0 in do_handle_one_connection (thd_arg=0x7f1d5c1bc4e8)
                       at .../sql/sql_connect.cc:1195
#11 0x00000000006a0eb5 in handle_one_connection (arg=0x7f1d5c1bc4e8)
                       at .../sql/sql_connect.cc:1134
#12 0x00007f1d6a569040 in start_thread ()
                       from /lib64/libpthread.so.0
#13 0x00007f1d6982608d in clone ()
                       from /lib64/libc.so.6
#14 0x0000000000000000 in ?? ()

This bug looks a lot like
   Bug#41425 Assertion in Protocol::end_statement() (pushbuild2) 
             (diagnostics_area)
but my mysql-next-mr tree contains already the fix for #41425.

How to repeat:
I will try to come up with simplified grammars soon.
[8 Jun 2010 8:01] Jon Olav Hauglid
Reproduced the assert in mysql-trunk-runtime, but for DROP SCHEMA.
(gdb) p thd->stmt_da->m_status
$3 = Diagnostics_area::DA_EMPTY
(gdb) p thd->killed
$4 = THD::NOT_KILLED
[23 Jun 2010 9:10] Matthias Leich
Reproduced on
mysql-trunk-runtime revno: 3060 2010-06-18

The intersection of my various backtraces is
INSERT ... IGNORE INTO <table>
SELECT ... FROM <select_table1> ...
UNION
SELECT ... FROM <select_table2> ...
<table> does not need to be a complicated one
like merge or partitioned table.
[23 Jun 2010 9:48] Matthias Leich
Automatic testcase simplification is on the way.
I expect the result ~ 24. June.
[24 Jun 2010 13:39] Matthias Leich
My script:
----------
CREATE TABLE t1 ( pk BIGINT) ;
--error ER_FIELD_SPECIFIED_TWICE
INSERT IGNORE t1 ( pk , pk ) SELECT 1,1;
--error ER_FIELD_SPECIFIED_TWICE
INSERT t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ;
INSERT IGNORE t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ;

Result on 5.1.49-debug (mysql-5.1 revno: 3410 2010-06-03)
---------------------------------------------------------
...
CURRENT_TEST: main.ml0013
mysqltest: At line 6: query 'INSERT IGNORE t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ' failed: 2013: Lost connection to MySQL server during query

The result from queries just before the failure was:
CREATE TABLE t1 ( pk BIGINT) ;
INSERT IGNORE t1 ( pk , pk ) SELECT 1,1;
ERROR 42000: Column 'pk' specified twice
INSERT t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ;
ERROR 42000: Column 'pk' specified twice
...
Thread 1 (process 14772):
#0  0x00007f13dd60cce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b21a1f in my_write_core (sig=6) at stacktrace.c:329
#2  0x00000000006bd0ed in handle_segfault (sig=6) at mysqld.cc:2571
#3  <signal handler called>
#4  0x00007f13dc8155c5 in raise () from /lib64/libc.so.6
#5  0x00007f13dc816bb3 in abort () from /lib64/libc.so.6
#6  0x00007f13dc80e1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000006b040d in net_end_statement (thd=0x11c3998) at protocol.cc:465
#8  0x00000000006da14d in dispatch_command (command=COM_QUERY, thd=0x11c3998, packet=0x1218269 "INSERT IGNORE t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ", packet_length=58) at sql_parse.cc:1646
#9  0x00000000006da502 in do_command (thd=0x11c3998) at sql_parse.cc:882
#10 0x00000000006c688c in handle_one_connection (arg=0x11c3998) at sql_connect.cc:1134
#11 0x00007f13dd608040 in start_thread () from /lib64/libpthread.so.0
#12 0x00007f13dc8b608d in clone () from /lib64/libc.so.6
#13 0x0000000000000000 in ?? ()

The assert disappears in case I remove the IGNORE or the UNION SELECT ...

The assert happens in protocol.cc:465 :
  case Diagnostics_area::DA_DISABLED:
    break;
  case Diagnostics_area::DA_EMPTY:
  default:
    DBUG_ASSERT(0); <--------------
    error= net_send_ok(thd, thd->server_status, thd->total_warn_count,
                       0, 0, NULL);
    break;

The same test on some old 5.6.99-m4
(mysql-next-mr revno: 3145 2010-05-20) NOT compiled with debug:
---------------------------------------------------------------
CREATE TABLE t1 ( pk BIGINT) ;
INSERT IGNORE t1 ( pk , pk ) SELECT 1,1;
ERROR 42000: Column 'pk' specified twice
INSERT t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ;
ERROR 42000: Column 'pk' specified twice
INSERT IGNORE t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ;
   <--- no assert because not compiled with debug, ok
   <--- no error message about 'pk' specified twice, bad
SELECT * FROM t1;
pk
   <--- Empty result set, good

5.5.5-m3-debug
(mysql-trunk-runtime revno: 3060 2010-06-18 compiled with debug)
----------------------------------------------------------------
hread 1 (process 15246):
#0  0x00007fd026751ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a10fe9 in my_write_core (sig=6) at /work2/repo/mysql-trunk-runtime/mysys/stacktrace.c:326
#2  0x0000000000545178 in handle_segfault (sig=6) at /work2/repo/mysql-trunk-runtime/sql/mysqld.cc:2791
#3  <signal handler called>
#4  0x00007fd0257675c5 in raise () from /lib64/libc.so.6
#5  0x00007fd025768bb3 in abort () from /lib64/libc.so.6
#6  0x00007fd0257601e9 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000551c3a in Protocol::end_statement (this=0x11bc768) at /work2/repo/mysql-trunk-runtime/sql/protocol.cc:540
#8  0x00000000005dd436 in dispatch_command (command=COM_QUERY, thd=0x11bc2b8, packet=0x1241cf9 "INSERT IGNORE t1 ( pk , pk ) SELECT 1,1  UNION SELECT 2,2 ", packet_length=58) at /work2/repo/mysql-trunk-runtime/sql/sql_parse.cc:1514
#9  0x00000000005dd7d5 in do_command (thd=0x11bc2b8) at /work2/repo/mysql-trunk-runtime/sql/sql_parse.cc:807
#10 0x00000000006a874f in do_handle_one_connection (thd_arg=0x11bc2b8) at /work2/repo/mysql-trunk-runtime/sql/sql_connect.cc:1196
#11 0x00000000006a8814 in handle_one_connection (arg=0x11bc2b8) at /work2/repo/mysql-trunk-runtime/sql/sql_connect.cc:1135
#12 0x000000000095bd47 in pfs_spawn_thread (arg=0x12273f8) at /work2/repo/mysql-trunk-runtime/storage/perfschema/pfs.cc:1015
#13 0x00007fd02674d040 in start_thread () from /lib64/libpthread.so.0
#14 0x00007fd02580808d in clone () from /lib64/libc.so.6
#15 0x0000000000000000 in ?? ()
[21 Jul 2010 10:36] 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/114027

3081 Jon Olav Hauglid	2010-07-21
      Bug #54106 assert in Protocol::end_statement,
                 INSERT IGNORE ... SELECT ... UNION SELECT ...
      
      This assert was triggered by INSERT IGNORE ... SELECT. The assert checks that a
      statement either sends OK or an error to the client. If the bug was triggered
      on release builds, it caused OK to be sent to the client instead of the correct
      error message (in this case ER_FIELD_SPECIFIED_TWICE).
      
      The reason the assert was triggered, was that lex->no_error was set to TRUE
      during JOIN::optimize() because of IGNORE. This causes all errors to be ignored.
      However, not all errors can be ignored. Some, such as ER_FIELD_SPECIFIED_TWICE
      will cause the INSERT to fail no matter what. But since lex->no_error was set,
      the critical errors were ignored, the INSERT failed and neither OK nor the
      error message was sent to the client.
      
      This patch fixes the problem by temporarily turning off lex->no_error in
      places where errors cannot be ignored during processing of INSERT ... SELECT.
      
      Test case added to insert.test.
[22 Jul 2010 10: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/114126

3500 Jon Olav Hauglid	2010-07-22
      Bug #54106 assert in Protocol::end_statement,
                 INSERT IGNORE ... SELECT ... UNION SELECT ...
      
      This assert was triggered by INSERT IGNORE ... SELECT. The assert checks that a
      statement either sends OK or an error to the client. If the bug was triggered
      on release builds, it caused OK to be sent to the client instead of the correct
      error message (in this case ER_FIELD_SPECIFIED_TWICE).
      
      The reason the assert was triggered, was that lex->no_error was set to TRUE
      during JOIN::optimize() because of IGNORE. This causes all errors to be ignored.
      However, not all errors can be ignored. Some, such as ER_FIELD_SPECIFIED_TWICE
      will cause the INSERT to fail no matter what. But since lex->no_error was set,
      the critical errors were ignored, the INSERT failed and neither OK nor the
      error message was sent to the client.
      
      This patch fixes the problem by temporarily turning off lex->no_error in
      places where errors cannot be ignored during processing of INSERT ... SELECT.
      
      Test case added to insert.test.
[9 Aug 2010 11: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/115309

3479 Jon Olav Hauglid	2010-08-09
      Bug #54106 assert in Protocol::end_statement,
                 INSERT IGNORE ... SELECT ... UNION SELECT ...
      
      This assert was triggered by INSERT IGNORE ... SELECT. The assert checks that a
      statement either sends OK or an error to the client. If the bug was triggered
      on release builds, it caused OK to be sent to the client instead of the correct
      error message (in this case ER_FIELD_SPECIFIED_TWICE).
      
      The reason the assert was triggered, was that lex->no_error was set to TRUE
      during JOIN::optimize() because of IGNORE. This causes all errors to be ignored.
      However, not all errors can be ignored. Some, such as ER_FIELD_SPECIFIED_TWICE
      will cause the INSERT to fail no matter what. But since lex->no_error was set,
      the critical errors were ignored, the INSERT failed and neither OK nor the
      error message was sent to the client.
      
      This patch fixes the problem by temporarily turning off lex->no_error in
      places where errors cannot be ignored during processing of INSERT ... SELECT.
      
      Test case added to insert.test.
[9 Aug 2010 12:14] Jon Olav Hauglid
Pushed to mysql-5.1-bugteam (5.1.50) and merged to mysql-5.5-merge.
[18 Aug 2010 7:21] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100818071819-2lu46b0mm3cs34rf) (version source revid:alik@sun.com-20100818071732-g682fg1v0nnrrutx) (merge vers: 5.6.1-m4) (pib:20)
[18 Aug 2010 7:22] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100818071923-4ounwbhiium2met1) (version source revid:alik@sun.com-20100818071743-lrzordai06i2crty) (pib:20)
[18 Aug 2010 7:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (version source revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (merge vers: 5.5.6-m3) (pib:20)
[18 Aug 2010 15:08] Paul DuBois
Noted in 5.5.6, 5.6.1 changelogs.

INSERT IGNORE INTO ... SELECT statements could cause a debug
assertion to be raised. 

Setting report to Need Merge pending push into 5.1.x.
[21 Aug 2010 1:23] Paul DuBois
Noted in 5.1.51 changelog.
[28 Sep 2010 8:49] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[14 Oct 2010 8:38] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:53] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:10] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 15:35] Jon Stephens
Already documented in the 5.1.51 changelog; no new changelog entries required. Setting back to Closed state.