MySQL Bugs: #24587: mysql crashes sometimes on SELECT

Bug #24587	mysql crashes sometimes on SELECT
Submitted:	25 Nov 2006 16:08	Modified:	28 Dec 2006 16:39
Reporter:	Andrew Zabolotny	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (Fedora Core 6)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
mysql crashes on my forum database (~127Mb) very often. The forum is vbulletin 3.54.

It used to work on fedora core 4 with mysql 4.1.20 for a very long time without any problems. Today I've upgraded to Fedora Core 6 which is distributed with mysql 5.0.22, and the problems begun.

I've dumped all databases with mysqldump, and loaded them into 5.0.22 with mysql table <dump, doesn't help. I've run mysql_upgrade, mysql_fix_privilege_tables, doesn't help either.

I've installed mysql-debuginfo and here's the backtrace I've got:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1077999936 (LWP 14088)]
SEL_ARG::last (this=0x0) at opt_range.cc:1295
1295      if (!next_arg->right)
(gdb) where
#0  SEL_ARG::last (this=0x0) at opt_range.cc:1295
#1  0x00000000005e116f in get_func_mm_tree (param=0x4040aec0, 
    cond_func=0x133ec08, field=0xfd3a48, value=<value optimized out>, 
    cmp_type=INT_RESULT, inv=<value optimized out>) at opt_range.cc:3556
#2  0x00000000005e1859 in get_mm_tree (param=0x4040aec0, cond=0x133ec08)
    at opt_range.cc:3792
#3  0x00000000005e154e in get_mm_tree (param=0x4040aec0, cond=0x3)
    at opt_range.cc:3652
#4  0x00000000005e38c7 in SQL_SELECT::test_quick_select (this=0x1343928, 
    thd=0x132e660, keys_to_use=<value optimized out>, 
    prev_tables=<value optimized out>, limit=<value optimized out>, 
    force_quick_range=<value optimized out>) at opt_range.cc:1848
#5  0x000000000059e5b9 in make_join_statistics (join=0x133fa28, 
    tables=<value optimized out>, conds=<value optimized out>, 
    keyuse_array=0x1340c00) at sql_select.cc:1944
#6  0x000000000059f205 in JOIN::optimize (this=0x133fa28) at sql_select.cc:690
#7  0x00000000005a5b02 in mysql_select (thd=0x132e660, 
    rref_pointer_array=0x132ecc0, tables=0x133d178, wild_num=0, 
    fields=@0x132eb88, conds=0x133f868, og_num=0, order=0x0, group=0x0, 
    having=0x0, proc_param=0x0, select_options=2189707776, result=0x133fa08, 
    unit=0x132e6e0, select_lex=0x132ea88) at sql_select.cc:1887
#8  0x00000000005a6471 in handle_select (thd=0x132e660, lex=0x132e6c8, 
    result=0x133fa08, setup_tables_done_option=0) at sql_select.cc:250
#9  0x000000000055c0a9 in mysql_execute_command (thd=0x132e660)
    at sql_parse.cc:2499
#10 0x000000000056041b in mysql_parse (thd=0x132e660, 
    inBuf=<value optimized out>, length=345) at sql_parse.cc:5695
#11 0x0000000000560936 in dispatch_command (command=COM_QUERY, thd=0x132e660, 
    packet=<value optimized out>, packet_length=<value optimized out>)
    at sql_parse.cc:1736
#12 0x00000000005619c6 in do_command (thd=0x132e660) at sql_parse.cc:1522

(gdb) up
#1  0x00000000005e116f in get_func_mm_tree (param=0x407d9ec0, 
    cond_func=0xfb5d78, field=0x119fb18, value=<value optimized out>, 
    cmp_type=INT_RESULT, inv=<value optimized out>) at opt_range.cc:3556
3556                      SEL_ARG *last_val= tree->keys[idx]->last();
(gdb) p idx 
$2 = 0
(gdb) p param->keys
$3 = 1
(gdb) p tree->keys
$4 = {0x0 <repeats 64 times>}
(gdb) p tree2->keys
$5 = {0x13783b0, 0x0 <repeats 63 times>}
...
(gdb) up
#9  0x000000000055c0a9 in mysql_execute_command (thd=0x2aaac00973c0)
    at sql_parse.cc:2499
2499            res= handle_select(thd, lex, result, 0);
(gdb) p lex
$16 = (LEX *) 0x2aaac0097428
(gdb) p lex->buf
$17 = (
    uchar *) 0xfb3fa0 "SELECT forum.forumid\n\t\t\tFROM forum AS forum\n\t\t\tLEFT JOIN forumread AS forumread ON (forum.forumid = forumread.forumid AND forumread.userid = 1904)\n\t\t\tWHERE forum.forumid IN (51,13,31,-1)\n\t\t\t\tAND forum.forumid NOT IN (51, -1)\n\t\t\t\tAND (forum.lastpost = 0 OR\n\t\t\t\t\tIF(forumread.readtime IS NULL, 1163605189, forumread.readtime) > forum.lastpost\n\t\t\t\t)"

After the crash mysql continues to work 'normally', but the user that caused the crashs gets a error message from the forum - either that DB is down, or connection is broken and such.

How to repeat:
No idea how to do this without the full database :-( I hope the provided debug info is enough to make conclusions... And what's worse, it doesn't crash too often, say it can work 10 minutes under relatively heavy load (the forum is quite popular), and only after 10 minutes crash.

Thank you for the bug report. Could you please upgrade for the new release
version 5.0.27 and try again, if the problem persist please try to provide
a repeatable test case. Thanks in advance.

Update: I've downgraded mysql to version 5.0.18 and the problem vanished. The server is running more than a hour and there weren't any crashes during this period, and before the interval between crashes was never longer than 10 minutes, often much shorter (with 5.0.22 I've got 287 crashes between 14:15 and 17:12). So the bug was introduced somewhere between 5.0.18 and 5.0.22.

Okay I will install 5.0.27 when it will be available from Fedora upgrade repository. Until then I'll keep an eye on this report.

looks like bug #19618

Thank you for the feedback. Could you please re-open this bug report
when you will able to test the new release version. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can confirm that upgrading to mysql 5.0.27 fixes the problem.
Thank you guys.