Bug #15536 Crash when DELETE with subquery using BDB tables
Submitted: 6 Dec 2005 18:35 Modified: 21 Dec 2005 8:53
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: BDB Severity:S1 (Critical)
Version:4.1 OS:Linux (SLES-9/x86_64)
Assigned to: Jim Winstead CPU Architecture:Any

[6 Dec 2005 18:35] Axel Schwenke
Description:
I stumbled over this one while trying to get a testcase for a BDB related crash: the BDB engine stops with an error message triggered in storage/bdb/txn/txn.c:926 (active cursors at txn->commit)

Stack trace:
#2  0x00000000007e0ee6 in __db_panic (dbenv=0x373dc28, errval=22)
    at db_err.c:205
#3  0x000000000079c1e3 in __txn_commit (txnp=0x3af5098, flags=0) at txn.c:471
#4  0x000000000079d059 in __txn_commit_pp (txnp=<value optimized out>, 
    flags=<value optimized out>) at txn.c:442
#5  0x0000000000678f21 in berkeley_commit (thd=<value optimized out>, 
    all=false) at ha_berkeley.cc:293
#6  0x000000000060168d in ha_commit_one_phase (thd=0x37ccde0, all=false)
    at handler.cc:610
#7  0x0000000000601527 in ha_commit_trans (thd=0x37ccde0, all=false)
    at handler.cc:580
#8  0x00000000006019df in ha_autocommit_or_rollback (thd=0x37ccde0, error=0)
    at handler.cc:719
#9  0x00000000005c2238 in mysql_delete (thd=0x37ccde0, 
    table_list=0x2ae26be390, conds=<value optimized out>, order=0x2ae2672588, 
    limit=18446744073709551614, options=0, reset_auto_increment=false)
    at sql_delete.cc:306
#10 0x000000000055c7a4 in mysql_execute_command (thd=0x37ccde0)
    at sql_parse.cc:3332
#11 0x00000000005bcfaa in Prepared_statement::execute (this=0x2ae2671f90, 
    expanded_query=0x410bf260, open_cursor=false) at sql_prepare.cc:2896

Statement:
(gdb) f 6
(gdb) p thd->query
$1 = 0x3b14310 "DELETE FROM `TATOPA` WHERE ( `TATOPA` .`F5C8` IN ( SELECT * FROM `SVERS` T_100 ) OR `TATOPA` .`F2I4` = 16 ) AND EXISTS ( SELECT * FROM ( `TRDIR` T_200 INNER JOIN `TADIR` T_201 ON T_200 .`NAME` = T_201"...

BDB-txn:
(gdb) f 3
(gdb) p *txnp
$2 = {mgrp = 0x373e7e8, parent = 0x3af9b58, last_lsn = {file = 6167, 
    offset = 14958600}, txnid = 2147496756, tid = 0, off = 15816, 
  lock_timeout = 0, expire = 0, txn_list = 0x0, links = {tqe_next = 0x0, 
    tqe_prev = 0x3af9b90}, xalinks = {tqe_next = 0x0, tqe_prev = 0x0}, 
  events = {tqh_first = 0x0, tqh_last = 0x3af50f0}, logs = {stqh_first = 0x0, 
    stqh_last = 0x3af5100}, kids = {tqh_first = 0x0, tqh_last = 0x3af5110}, 
  klinks = {tqe_next = 0x0, tqe_prev = 0x3af9bd0}, api_internal = 0x0, 
  xml_internal = 0x0, cursors = 1, abort = 0x79d0a0 <__txn_abort_pp>, 
  commit = 0x79d030 <__txn_commit_pp>, discard = 0x79cfc0 <__txn_discard_pp>, 
  id = 0x79a9e0 <__txn_id>, prepare = 0x79c5a0 <__txn_prepare>, 
  set_begin_lsnp = 0x79a9f0 <__txn_set_begin_lsnp>, 
  set_timeout = 0x79b650 <__txn_set_timeout>, flags = 64}

OK, while trying to create a testcase for the problem above I stumbled over another, probably related crash. When issuing a COMMIT while a cursor is open, the server crashes. 
This happens only with BDB tables. No problem with MyISAM or InnoDB.

How to repeat:
Create table:

use test;
create table t1 (c1 int) engine=bdb;

Test with the following C program:

#include <mysql.h>                                                              
#include <stdio.h>                                                              
                                                                                
int main (int argc, char **argv)                                                
{                                                                               
        MYSQL           *mysql;                                                 
        MYSQL_STMT      *stmt;         
        unsigned long   ctype = CURSOR_TYPE_READ_ONLY;                                         
        char * q1 = "SET AUTOCOMMIT=0";
        char * q2 = "SELECT * FROM t1";
        char * q3 = "COMMIT";
                                                                                
        mysql = mysql_init(NULL);                                               
        mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 0); 

        mysql_query(mysql, q1);

        stmt = mysql_stmt_init(mysql);                                          
        mysql_stmt_prepare(stmt, q2, strlen(q2));                         
        mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, &ctype);
        mysql_stmt_execute(stmt);                                               
        /* mysql_stmt_close(stmt); */
                                                 
        mysql_query(mysql, q3);
                                                                                
        mysql_close(mysql);                                                     
}
[7 Dec 2005 2:50] Jim Winstead
i wasn't able to reproduce this on an x86_64 box. you might want to double-check with the latest 5.1, where the latest BDB was pushed. (but this also worked fine for me with an earlier 5.1 tree.)
[7 Dec 2005 17:51] Axel Schwenke
OK, here we go with a new testcase. Download and gunzip the files I uploaded. Then repeat:

# mysql test
root@test>\. 15536.dump
...
root@test>\. 15536.boom
ERROR 2013 (HY000): Lost connection to MySQL server during query
[8 Dec 2005 2:45] Jim Winstead
this appears to be a bug caused by the subquery, where it is doing an index read but ha_index_end() isn't ever getting called on the index. this leaves a cursor open on one of the tables, which then causes bdb to fail on commit.
[8 Dec 2005 19:23] Jim Winstead
Updated synopsis to reflect what the bug report became. (There may be cursor issues, but this specific bug is a different problem.)

Problem exists back in 4.1, too.
[8 Dec 2005 20: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/47
[9 Dec 2005 21:46] Konstantin Osipov
OK to push.
Notice, that in 5.0 and up you seem to not need an additional variable in sql_insert.cc, the code flow can be reorganized without it.
[13 Dec 2005 2:25] Jim Winstead
Fixed in 4.1.17, 5.0.18, and 5.1.4. The bug also impacted INSERT and UPDATE statements with subqueries.
[21 Dec 2005 8:53] 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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.17, 5.0.18, and 5.1.4 changelogs. Bug closed.