Bug #18306 MySQL crashes and restarts using subquery
Submitted: 17 Mar 2006 12:52 Modified: 31 Mar 2006 7:43
Reporter: Berend Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.19/5.0.20BK/5.1.8BK OS:Linux (Gentoo kernel 2.6.15.4)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[17 Mar 2006 12:52] Berend
Description:
My queries + debug:
Query: DELETE QUICK FROM table_32 WHERE date <= 1140006215 AND (SELECT value FROM config WHERE variable = 'nodeid') = 1
MYSQL-DEBUG:
Query: OPTIMIZE LOCAL TABLE table_32
MYSQL-DEBUG:
Query: DELETE QUICK FROM table_33 WHERE date <= 1140006215 AND (SELECT value FROM config WHERE variable = 'nodeid') = 1
MYSQL-DEBUG:
Query: OPTIMIZE LOCAL TABLE table_33
MYSQL-DEBUG: Lost connection to MySQL server during query
Query: DELETE QUICK FROM table_34 WHERE date <= 1140006215 AND (SELECT value FROM config WHERE variable = 'nodeid') = 1
MYSQL-DEBUG: MySQL server has gone away
OPTIMIZE LOCAL TABLE table_34
MYSQL-DEBUG: MySQL server has gone away

Really strange bug. The DELETE query works X times and then mysql crashes.

MySQL .err file:
thd=0x89b2aa0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xa5dbb568, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81636ef
0xb7ec6e55
0x8158bc8
0x8156097
0x81802a8
0x81761b3
0x8175cfd
0x8175072
0xb7ec114d
0xb7d3189a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x89c1b08 = DELETE QUICK FROM table_33 WHERE date <= 1140005793 AND (SELECT value FROM config WHERE variable = 'nodeid') = 1
thd->thread_id=2
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
See queries Description
[17 Mar 2006 15:01] Miguel Solorzano
Thank you for the bug report.
Could you please provide a dump file with table/insert data for to try
these queries on our side?.
Thanks in advance.
[17 Mar 2006 17:00] Miguel Solorzano
Thank you for the feedback. I was able for to repeat with the first query
against 5.0 BK source running on Linux Suse:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create news
miguel@hegel:~/dbs/5.0> bin/mysql -uroot news < home/miguel/dumps/news.sql 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20-debug

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

mysql> DELETE QUICK FROM table_34 WHERE date <= 1140020339 AND (SELECT value
    -> FROM config WHERE variable = 'nodeid') = 1;

060317 13:52:36 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 9061)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 9061)]
0x082a1f55 in SQL_SELECT::cleanup (this=0x8e7de98) at opt_range.cc:694
694       delete quick;
(gdb) bt full
#0  0x082a1f55 in SQL_SELECT::cleanup (this=0x8e7de98) at opt_range.cc:694
No locals.
#1  0x082a2001 in ~SQL_SELECT (this=0x8e7de98) at opt_range.cc:708
No locals.
#2  0x08247a35 in JOIN::destroy (this=0x8e82080) at sql_select.cc:1777
        _db_func_ = 0x833d8fc "\203Ä \213E\b\213\200Ô"
        _db_file_ = 0x4376c3a8 "èÃvC#Û3\b\020\026è\bÐÃvCÌÃvCÈÃvC¨²è"
        _db_level_ = 1076963488
        _db_framep_ = (char **) 0x4025a1f4
#3  0x0833d94f in st_select_lex::cleanup (this=0x8e81610) at sql_union.cc:711
        _db_func_ = 0x4376c3e8 "\030ÄvC¢h%\bp\027è\bT\035æ\bT\035æ\bëø\025\b"
        _db_file_ = 0x86c87a5 "mf_cache.c"
        error = false
        _db_level_ = 141330391
        _db_framep_ = (char **) 0x85a0975
        __PRETTY_FUNCTION__ = "bool st_select_lex::cleanup()"
#4  0x0833db23 in st_select_lex_unit::cleanup (this=0x8e81770) at sql_union.cc:605
        sl = (SELECT_LEX *) 0x8e81610
        _db_func_ = 0x4376c3f8 "T\035æ\bëø\025\b"
        _db_file_ = 0x0
        error = 0
        _db_level_ = 4294967295
        _db_framep_ = (char **) 0x0
#5  0x082568a2 in free_underlaid_joins (thd=0x8e619e8, select=0x8e61c60) at sql_select.cc:13411
        unit = (SELECT_LEX_UNIT *) 0x8e81770
#6  0x082736da in mysql_delete (thd=0x8e619e8, table_list=0x8e812c0, conds=0x8e81fc8, order=0x8e61d54, limit=18446744073709551615, 
    options=4194304, reset_auto_increment=false) at sql_delete.cc:296
        select = (SQL_SELECT *) 0x8e82f00
        deleted = 0
        select_lex = (SELECT_LEX *) 0x8e61c60
        _db_func_ = 0x1 <Address 0x1 out of bounds>
        _db_file_ = 0x8e619e8 "\2105a\bTD~\bXD~\b\2345a\b ßç\b\b\032æ\b"
        table = (TABLE *) 0x8e8a158
        _db_level_ = 136300109
        _db_framep_ = (char **) 0x4376c6d8
        info = {table = 0x0, file = 0x8e8a960, forms = 0x4376c628, read_record = 0x82baa2e <rr_quick>, thd = 0x8e619e8, select = 0x8e82f00, 
  cache_records = 0, ref_length = 6, struct_length = 0, reclength = 0, rec_cache_size = 0, error_offset = 0, index = 0, ref_pos = 0x0, 
  record = 0x8e8ae08 "!³\001", rec_buf = 0x0, cache = 0x0, cache_pos = 0x0, cache_end = 0x0, read_positions = 0x0, io_cache = 0x0, 
  print_error = true, ignore_not_found_rows = false}
---Type <return> to continue, or q <return> to quit---
        safe_update = false
        const_cond = false
        error = -1
        using_limit = false
        transactional_table = false
        usable_index = 64
#7  0x082033b0 in mysql_execute_command (thd=0x8e619e8) at sql_parse.cc:3359
        res = false
        lex = (LEX *) 0x8e61a28
        _db_func_ = 0x81da6f8 "\203Ä\020\213Eà;Eä\017\225Àº"
        _db_file_ = 0x87e35fc "ü5~\b"
        _db_level_ = 149298320
        _db_framep_ = (char **) 0x8e81280
        result = 0
        select_lex = (SELECT_LEX *) 0x8e61c60
        first_table = (TABLE_LIST *) 0x8e812c0
        all_tables = (TABLE_LIST *) 0x8e812c0
        unit = (SELECT_LEX_UNIT *) 0x8e61a38
        __PRETTY_FUNCTION__ = "bool mysql_execute_command(THD*)"
#8  0x082086eb in mysql_parse (thd=0x8e619e8, 
    inBuf=0x8e811d8 "DELETE QUICK FROM table_34 WHERE date <= 1140020339 AND (SELECT value\nFROM config WHERE variable = 'nodeid') = 1", 
    length=112) at sql_parse.cc:5697
        lex = (LEX *) 0x8e61a28
        _db_func_ = 0x87e8200 "è f\b"
        _db_file_ = 0x8208faf "\203Ä ¡´÷~\b\205Àt/\203ì\bh¯Bb\bh³\006"
        _db_level_ = 1131860760
        _db_framep_ = (char **) 0x0
        __PRETTY_FUNCTION__ = "void mysql_parse(THD*, char*, uint)"
#9  0x0820902e in dispatch_command (command=COM_QUERY, thd=0x8e619e8, 
    packet=0x8e79179 "DELETE QUICK FROM table_34 WHERE date <= 1140020339 AND (SELECT value\nFROM config WHERE variable = 'nodeid') = 1", 
    packet_length=113) at sql_parse.cc:1720
        packet_end = 0x8e81248 ""
        net = (NET *) 0x8e621ec
        _db_func_ = 0x4 <Address 0x4 out of bounds>
        _db_file_ = 0x0
        error = false
        _db_level_ = 16793663
        _db_framep_ = (char **) 0x8e62ae8
#10 0x0820a3bc in do_command (thd=0x8e619e8) at sql_parse.cc:1516
---Type <return> to continue, or q <return> to quit---
        packet = 0x8e79178 "\003DELETE QUICK FROM table_34 WHERE date <= 1140020339 AND (SELECT value\nFROM config WHERE variable = 'nodeid') = 1"
        old_timeout = 30
        packet_length = 113
        net = (NET *) 0x8e621ec
        command = COM_QUERY
        _db_func_ = 0x4376d378 "XÔvCȧ \bè\031æ\b\001"
        _db_file_ = 0x8e62c04 "ø1è\b"
        _db_level_ = 149434872
        _db_framep_ = (char **) 0x1010
#11 0x0820a7c8 in handle_one_connection (arg=0x8e619e8) at sql_parse.cc:1159
        error = 0
        net = (NET *) 0x8e621ec
        sctx = (Security_context *) 0x8e629cc
        thd = (class THD *) 0x8e619e8
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#12 0x40179297 in start_thread () from /lib/tls/libpthread.so.0
No symbol table info available.
#13 0x402b437e in clone () from /lib/tls/libc.so.6
No symbol table info available.
#14 0x4376dbb0 in ?? ()
No symbol table info available.
(gdb)
[18 Mar 2006 0:34] Miguel Solorzano
Also repeatable on 5.1.8BK:

miguel@hegel:~/dbs/5.1> bin/mysql -uroot news
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.8-beta-debug

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

mysql> DELETE QUICK FROM table_34 WHERE date <= 1140020339 AND (SELECT value
    -> FROM config WHERE variable = 'nodeid') = 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

060317 21:24:08 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.8-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1119902640 (LWP 7966)]
[Thread 1119902640 (zombie) exited]
[New Thread 1119902640 (LWP 7968)]
[Thread 1119902640 (zombie) exited]
[New Thread 1119902640 (LWP 7972)]
[Thread 1119902640 (zombie) exited]
[New Thread 1119902640 (LWP 7975)]
[Thread 1119902640 (zombie) exited]
[New Thread 1119902640 (LWP 7978)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1119902640 (LWP 7978)]
0x082de031 in SQL_SELECT::cleanup (this=0x920bdf0) at opt_range.cc:795
795       delete quick;
[21 Mar 2006 12:24] 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/3991
[23 Mar 2006 14:11] 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/4070
[23 Mar 2006 15:40] Ramil Kalimullin
fixed in 5.0.20
[31 Mar 2006 7:43] 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 5.0.20 changelog. Closed.