Bug #46958 Assertion in Diagnostics_area::set_ok_status, trigger, merge table
Submitted: 27 Aug 2009 16:24 Modified: 18 Dec 2009 13:11
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.4 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[27 Aug 2009 16:24] Matthias Leich
Description:
--disable_warnings
DROP DATABASE IF EXISTS test_db ;
--enable_warnings

CREATE DATABASE test_db;
CREATE TABLE test_db.t1 (f1 INTEGER, PRIMARY KEY (f1)) ENGINE = MyISAM;
CREATE TABLE test_db.t2 LIKE test_db.t1;
CREATE TABLE test_db.t3 LIKE test_db.t1;
ALTER  TABLE test_db.t3 ENGINE = MERGE UNION (test_db.t1, test_db.t2);

INSERT INTO test_db.t2 VALUES (8), (9);

CREATE VIEW test_db.v1 AS
SELECT f1 FROM test_db.t2 AS A WHERE f1 BETWEEN 5 AND 8;
delimiter |;
CREATE TRIGGER test_db.tr1_1_A BEFORE DELETE ON test_db.t2 FOR EACH ROW
BEGIN
   INSERT test_db.t_not_exists1 VALUES (1);
END|
delimiter ;|
# The next statement gets in MySQL
# 5.4 : an assertion
# 5.0, 5.1: ERROR 42S02: Unknown table 'A' in MULTI DELETE
#           non sense, because test_db.t3 exists    
DELETE A,B FROM test_db.t3 AS A NATURAL JOIN test_db.v1 B;

DROP DATABASE test_db;

Backtrace from mysql-next-bugfixing:
------------------------------------
Thread 1 (process 5470):
#0  0x00007f0a815a7ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b57ee8 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006ef875 in handle_segfault (sig=6) at mysqld.cc:2738
#3  <signal handler called>
#4  0x00007f0a804a35c5 in raise () from /lib64/libc.so.6
#5  0x00007f0a804a4bb3 in abort () from /lib64/libc.so.6
#6  0x00007f0a8049c1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000007c111c in Diagnostics_area::set_ok_status (this=0x16714d0, thd=0x166e938, affected_rows_arg=1, last_insert_id_arg=0, message_arg=0x0) at sql_error.cc:358
#8  0x00000000006023ee in my_ok (thd=0x166e938, affected_rows=1, id=0, message=0x0) at sql_class.h:2639
#9  0x00000000007cb806 in multi_delete::send_eof (this=0x17e79e8) at sql_delete.cc:1036
#10 0x000000000078c733 in do_select (join=0x17f27d0, fields=0x1670a38, table=0x0, procedure=0x0) at sql_select.cc:15863
#11 0x00000000007a6d64 in JOIN::exec (this=0x17f27d0) at sql_select.cc:2900
#12 0x00000000007a1738 in mysql_select (thd=0x166e938, rref_pointer_array=0x1670b18, tables=0x160c6c8, wild_num=0, fields=@0x1670a38, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489942144, result=0x17e79e8,
    unit=0x16702a0, select_lex=0x1670930) at sql_select.cc:3091
#13 0x00000000007052b7 in mysql_execute_command (thd=0x166e938) at sql_parse.cc:3401
#14 0x000000000070a192 in mysql_parse (thd=0x166e938, inBuf=0x160b9e0 "DELETE A,B FROM test_db.t3 AS A NATURAL JOIN test_db.v1 B", length=57, found_semicolon=0x414b7f20) at sql_parse.cc:5950
#15 0x000000000070adb0 in dispatch_command (command=COM_QUERY, thd=0x166e938, packet=0x1607989 "DELETE A,B FROM test_db.t3 AS A NATURAL JOIN test_db.v1 B", packet_length=57) at sql_parse.cc:1062
#16 0x000000000070c254 in do_command (thd=0x166e938) at sql_parse.cc:744
#17 0x00000000006f9703 in handle_one_connection (arg=0x166e938) at sql_connect.cc:1163
#18 0x00007f0a815a3040 in start_thread () from /lib64/libpthread.so.0
#19 0x00007f0a8054408d in clone () from /lib64/libc.so.6
#20 0x0000000000000000 in ?? ()

My environment:
---------------
- mysql-next-bugfixing (5.4) 2009-08-27
- mysql-5.1-bugteam late July 2009
- mysql-5.0-bugteam late July 2009
- ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
Please see above
[9 Sep 2009 7:59] Martin Hansson
In 5.0 and 5.1, this is expected behaviour. The manual says

"Cross-database deletes are supported for multiple-table deletes, but you should be aware that in the list of tables from which to delete rows, aliases will have a default database unless one is specified explicitly."

But we should not get a failed assertion as in 5.4, obviously. I fill fix in that version only.
[9 Sep 2009 8:01] Martin Hansson
In fact, you get the assertion in 5.1 as well.
[9 Sep 2009 13:19] 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/82827

3093 Martin Hansson	2009-09-09
      Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, merge table
      
      When multi-DELETE statements terminated, they checked only for errors local
      to the statement itself, and otherwise tried to set OK status in the 
      diagnostics area.
      
      Fixed by also taking thread context level errors into account.
     @ mysql-test/r/delete.result
        Bug#46958: Test result.
     @ mysql-test/t/delete.test
        Bug#46958: Test case.
     @ sql/sql_base.cc
        Bug#46958: Corrected spelling.
     @ sql/sql_delete.cc
        Bug#46958: Fix.
[22 Sep 2009 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/84111

3118 Martin Hansson	2009-09-22
      Bug#46958: Assertion in Diagnostics_area::set_ok_status, 
      trigger, merge table
      
      The problem with break statements is that they have very local effects. Hence
      a break statement within the inner loop of a nested-loops join caused
      execution to proceed to the next table even though a serious error
      occured. The problem was fixed by breaking out the inner loop into its own
      method. The change empowers all errors to terminate the execution.
      
      The errors that will now halt multi-DELETE execution alltogether are 
      - triggers returning errors
      - handler errors
      - server being killed
     @ mysql-test/r/delete.result
        Bug#46958: Test result.
     @ mysql-test/t/delete.test
        Bug#46958: Test case.
     @ sql/sql_class.h
        Bug#46958: New method declaration.
     @ sql/sql_delete.cc
        Bug#46958: New method implementation.
[25 Sep 2009 9:06] 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/84590

3127 Martin Hansson	2009-09-25
      Bug#46958: Assertion in Diagnostics_area::set_ok_status, 
      trigger, merge table
            
      The problem with break statements is that they have very local effects. Hence
      a break statement within the inner loop of a nested-loops join caused
      execution to proceed to the next table even though a serious error
      occurred. The problem was fixed by breaking out the inner loop into its own
      method. The change empowers all errors to terminate the execution.
            
      The errors that will now halt multi-DELETE execution altogether are 
        - triggers returning errors
        - handler errors
        - server being killed
     @ mysql-test/r/delete.result
        Bug#46958: Test result.
     @ mysql-test/t/delete.test
        Bug#46958: Test case.
     @ sql/sql_class.h
        Bug#46958: New method declaration.
     @ sql/sql_delete.cc
        Bug#46958: New method implementation.
[27 Sep 2009 8:32] 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/84729

3128 Martin Hansson	2009-09-27 [merge]
      Merge of Bug#46958
[28 Sep 2009 12:44] 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/84843

3624 Martin Hansson	2009-09-28 [merge]
      Merge of Bug#46958
[29 Sep 2009 8:12] MySQL Verification Team
this crashes 5.1.40-debug (note missing definer is specified):

create or replace definer=`nobody`@`nobody` sql security definer view `view1` 
as select now();
lock table `view1` read;

is this a new bug?
[29 Sep 2009 12:39] Davi Arnaut
Shane, yes.
[6 Oct 2009 9:01] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:martin.hansson@sun.com-20090928104852-yrczxug69c2qvf8d) (merge vers: 5.1.40) (pib:11)
[7 Oct 2009 23:55] Paul Dubois
Noted in 5.1.40 changelog.

Failed multiple-table DELETE statements could raise an assertion.

Setting report to NDI pending push into 5.4.x.
[22 Oct 2009 6:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:07] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 19:45] Paul Dubois
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:32] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:48] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:03] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:17] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 13:11] MC Brown
Already documented in 5.1.41