Running RQG optimizer_no_subquery crashes MariaDB

[Hakan Küçükyılmaz]

Running RQG optimizer_no_subquery crashes MariaDB. The crash happens on Linux 64-bit and Mac OS X Intel 64-bit. However, the crash does not happen with MySQL sources on the same machines with the same compile script. I attached the full stack trace from a Linux run.

How to repeat

* Get latest Random Query Generator from lp:randgen
* Get latest MariaDB from lp:maria
I tested with revno: 2818, timestamp: Wed 2010-02-17 21:10:02 +0100
* Compile MariaDB with BUILD/compile-amd64-max
* Run the RQG test like

perl runall.pl \
  --basedir=${HOME}/work/monty_program/maria \
  --engine=InnoDB \
  --grammar=conf/optimizer_no_subquery.yy \
  --queries=100000000 \
  --threads=1 \
  --duration=86400

Stack trace on Linux:
thd: 0x7fd9489089f0
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...
stack_bottom = 0x7fd937a22100 thread_stack 0x48000
/home/hakan/work/monty_program/maria/sql/mysqld(my_print_stacktrace+0x35)[0x9e9cb5]
/home/hakan/work/monty_program/maria/sql/mysqld(handle_segfault+0x331)[0x618981]
/lib64/libpthread.so.0[0x7fd95b630a90]
/home/hakan/work/monty_program/maria/sql/mysqld[0x79a5d2]
/home/hakan/work/monty_program/maria/sql/mysqld[0x79ad69]
/home/hakan/work/monty_program/maria/sql/mysqld[0x79b07b]
/home/hakan/work/monty_program/maria/sql/mysqld[0x68a79a]
/home/hakan/work/monty_program/maria/sql/mysqld(_ZN4JOIN8optimizeEv+0x56b)[0x68c95b]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x94)[0x6950f4]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x171)[0x695c01]
/home/hakan/work/monty_program/maria/sql/mysqld[0x626559]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z21mysql_execute_commandP3THD+0x1da0)[0x62b000]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x3b1)[0x62ee91]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe59)[0x62fcf9]
/home/hakan/work/monty_program/maria/sql/mysqld(_Z10do_commandP3THD+0xee)[0x63056e]
/home/hakan/work/monty_program/maria/sql/mysqld(handle_one_connection+0x1b9)[0x6221c9]
/lib64/libpthread.so.0[0x7fd95b629070]
/lib64/libc.so.6(clone+0x6d)[0x7fd95a6cf11d]

Stack trace on Mac OS X:
0 mysqld 0x00000001004f7829 my_print_stacktrace + 57
1 mysqld 0x00000001000f24c2 handle_segfault + 802
2 libSystem.B.dylib 0x00007fff86eaaeaa _sigtramp + 26
3 ??? 0x0000000000000001 0x0 + 1
4 mysqld 0x0000000100288b1a _ZL21check_func_dependencyP4JOINyP13List_iteratorI10TABLE_LISTEPS2_P4Item + 538
5 mysqld 0x00000001002894b4 _ZL25eliminate_tables_for_listP4JOINP4ListI10TABLE_LISTEyP4Itemy + 340
6 mysqld 0x0000000100171fd1 _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array + 2497
7 mysqld 0x0000000100173e3e _ZN4JOIN8optimizeEv + 1374
8 mysqld 0x000000010017da54 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 148
9 mysqld 0x000000010017e572 _Z13handle_selectP3THDP6st_lexP13select_resultm + 370
10 mysqld 0x0000000100100383 _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 179
11 mysqld 0x00000001001062d5 _Z21mysql_execute_commandP3THD + 8293
12 mysqld 0x000000010010b048 _Z11mysql_parseP3THDPKcjPS2_ + 536
13 mysqld 0x000000010010bbe1 _Z16dispatch_command19enum_server_commandP3THDPcj + 2961
14 mysqld 0x000000010010c813 _Z10do_commandP3THD + 243
15 mysqld 0x00000001000fcdae handle_one_connection + 302
16 libSystem.B.dylib 0x00007fff86e83f8e _pthread_start + 331
17 libSystem.B.dylib 0x00007fff86e83e41 thread_start + 13

[Igor Babaev (igorb-seattle)]

Hakan,

IMHO, it does not make sense to report such bugs for the following reasons:
1. there are zillions of edge cases where the server crashes.
2. we, in MariaDB development, cannot afford ourselves spending time to
extract test cases for these crashes.
3. reporting a bag as a MariaDB bug when it's actually a bug of the
mainline MySQL code does not look right.

Regards,

Hakan Küçükyılmaz wrote:
> Public bug reported:
>
> Running RQG optimizer_no_subquery crashes MariaDB. The crash happens on
> Linux 64-bit and Mac OS X Intel 64-bit. However, the crash does not
> happen with MySQL sources on the same machines with the same compile
> script. I attached the full stack trace from a Linux run.
>
> How to repeat
>
> * Get latest Random Query Generator from lp:randgen
> * Get latest MariaDB from lp:maria
> I tested with revno: 2818, timestamp: Wed 2010-02-17 21:10:02 +0100
> * Compile MariaDB with BUILD/compile-amd64-max
> * Run the RQG test like
>
> perl runall.pl \
> --basedir=${HOME}/work/monty_program/maria \
> --engine=InnoDB \
> --grammar=conf/optimizer_no_subquery.yy \
> --queries=100000000 \
> --threads=1 \
> --duration=86400
>
> Stack trace on Linux:
> thd: 0x7fd9489089f0
> 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...
> stack_bottom = 0x7fd937a22100 thread_stack 0x48000
> /home/hakan/work/monty_program/maria/sql/mysqld(my_print_stacktrace+0x35)[0x9e9cb5]
> /home/hakan/work/monty_program/maria/sql/mysqld(handle_segfault+0x331)[0x618981]
> /lib64/libpthread.so.0[0x7fd95b630a90]
> /home/hakan/work/monty_program/maria/sql/mysqld[0x79a5d2]
> /home/hakan/work/monty_program/maria/sql/mysqld[0x79ad69]
> /home/hakan/work/monty_program/maria/sql/mysqld[0x79b07b]
> /home/hakan/work/monty_program/maria/sql/mysqld[0x68a79a]
> /home/hakan/work/monty_program/maria/sql/mysqld(_ZN4JOIN8optimizeEv+0x56b)[0x68c95b]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x94)[0x6950f4]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x171)[0x695c01]
> /home/hakan/work/monty_program/maria/sql/mysqld[0x626559]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z21mysql_execute_commandP3THD+0x1da0)[0x62b000]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x3b1)[0x62ee91]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe59)[0x62fcf9]
> /home/hakan/work/monty_program/maria/sql/mysqld(_Z10do_commandP3THD+0xee)[0x63056e]
> /home/hakan/work/monty_program/maria/sql/mysqld(handle_one_connection+0x1b9)[0x6221c9]
> /lib64/libpthread.so.0[0x7fd95b629070]
> /lib64/libc.so.6(clone+0x6d)[0x7fd95a6cf11d]
>
> Stack trace on Mac OS X:
> 0 mysqld 0x00000001004f7829 my_print_stacktrace + 57
> 1 mysqld 0x00000001000f24c2 handle_segfault + 802
> 2 libSystem.B.dylib 0x00007fff86eaaeaa _sigtramp + 26
> 3 ??...

[Kurt von Finck (mneptok)]

1. If there are so many edge cases that cause a server to crash, then our goal should be to reduce that number. Dismissing a bug out of hand because it's an edge case is unproductive and does not inspire confidence.

2. Maria development can be done by anyone with an interest, not just employees of Monty Program. If someone is interested in checking out the code and fixing a bug, we welcome that. But it takes open bugs in the bug tracker to generate such interest.

3. AFAICT, it's not a bug for MySQL.

[Sergey Petrunia (sergefp)]

My opinion is that we still need to report the crashes that can be observed in MariaDB while cannot be observed in MySQL.

When I try to repeat, I also get the crash, albeit in a different location:

# 12:52:05 Query: SELECT table2 . `col_int_key` AS field1 FROM ( CC AS table1 RIGHT OUTER JOIN ( ( C AS table2 STRAIGHT_JOIN C AS table3 ON (( table3 . `col_varchar_nokey` = table2 . `col_varchar_key` ) AND ( table3 . `pk` = table2 . `col_int_key` ) ) ) ) ON (( table3 . `col_varchar_key` = table2 . `col_varchar_key` ) OR ( table3 . `col_int_key` = table2 . `pk` ) ) ) HAVING field1 < 216 failed: 2013 Lost connection to MySQL server during query
\# 12:52:08 Current language: auto; currently c
# 12:52:08 #0 __pthread_kill (threadid=<value optimized out>, signo=<value optimized out>)
# 12:52:08 at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:75
# 12:52:08 #1 0x000000000061a73d in handle_segfault (sig=11) at mysqld.cc:2655
# 12:52:08 #2 <signal handler called>
# 12:52:08 #3 0x00000000007a31c2 in build_eq_mods_for_cond (ctx=0x7fc95721e320, eq_mod=0x7fc95721e580, and_level=0x7fc95721e58c,
# 12:52:08 cond=0x37e3af0) at opt_table_elimination.cc:1315
# 12:52:08 #4 0x00000000007a3959 in check_func_dependency (join=<value optimized out>, dep_tables=1, it=0x6, oj_tbl=0x3759f00,
# 12:52:08 cond=0x37e3af0) at opt_table_elimination.cc:815
# 12:52:08 #5 0x00000000007a3c73 in eliminate_tables_for_list (join=0x37687b8, join_list=<value optimized out>, list_tables=7,
# 12:52:08 on_expr=0x0, tables_used_elsewhere=6) at opt_table_elimination.cc:715
# 12:52:08 #6 0x000000000068dc4a in make_join_statistics (join=0x37687b8, tables_arg=0x36c77f0, conds=0x37e2ec8,
# 12:52:08 keyuse_array=0x3769d80) at sql_select.cc:2708
# 12:52:08 #7 0x000000000068fe4b in JOIN::optimize (this=0x37687b8) at sql_select.cc:988
# 12:52:08 #8 0x00000000006985c4 in mysql_select (thd=0x7fc97495b5f0, rref_pointer_array=0x7fc97495d6b8, tables=0x36c77f0, wild_num=0,
# 12:52:08 fields=@0x0, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x37e4118, proc_param=0x0, select_options=1,
# 12:52:08 result=0x37e4328, unit=0x7fc97495d0c0, select_lex=0x7fc97495d4e8) at sql_select.cc:2453
# 12:52:08 #9 0x00000000006990d1 in handle_select (thd=0x7fc97495b5f0, lex=0x7fc97495d020, result=0x37e4328,
# 12:52:08 setup_tables_done_option=0) at sql_select.cc:279
# 12:52:08 #10 0x00000000006287d8 in execute_sqlcom_select (thd=0x7fc97495b5f0, all_tables=0x36c77f0) at sql_parse.cc:5112
# 12:52:08 #11 0x000000000062d486 in mysql_execute_command (thd=0x7fc97495b5f0) at sql_parse.cc:2297
# 12:52:08 #12 0x0000000000631351 in mysql_parse (thd=0x7fc97495b5f0,
# 12:52:08 inBuf=0x36c6f78 "SELECT table2 . `col_int_key` AS field1 FROM ( CC AS table1 RIGHT OUTER JOIN ( ( C AS table2 STRAIGHT_JOIN C AS table3 ON (( table3 . `col_varchar_nokey` = table2 . `col_varchar_key` ) AND ( table3"..., length=376,
# 12:52:08 found_semicolon=0x7fc957220ac0) at sql_parse.cc:6033

[Sergey Petrunia (sergefp)]

And after I have disabled table elimination, it has been running for half an hour without crashes.

Hakan, could you please try running with this patch? Do you still observe the crash? (we need to patch the source as table_elimination has @@optimizer_switch flag only in debug builds)

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2010-01-15 15:27:55 +0000
+++ sql/sql_select.cc 2010-02-18 09:58:30 +0000
@@ -2705,7 +2705,7 @@ make_join_statistics(JOIN *join, TABLE_L

   join->const_table_map= 0;
   join->const_tables= const_count;
- eliminate_tables(join);
+ // eliminate_tables(join);
   const_count= join->const_tables;
   found_const_table_map= join->const_table_map;

[Sergey Petrunia (sergefp)]

Igor's point has some value in that we can't spend tons of time chasing race conditions or difficult-to-repeat problems.

I think we could avoid the argument altogether if the bug report contains info about whether the issue is easily repeatable. Rangden prints the query that has caused the crash, so that one can re-start the server and see if it can be crashed by running that query. If it does, then it is much easier to determine whether the problem is on Maria side or MySQL side, whose code this is, etc.

[Sergey Petrunia (sergefp)]

Ok, there is a repeatable single-query scenario:

One needs to load the attached dataset, then run this query:

SELECT table2 . `col_int_key` AS field1
FROM (
  CC AS table1
  RIGHT OUTER JOIN
  (
    ( C AS table2 STRAIGHT_JOIN
      C AS table3 ON (
               (table3.col_varchar_nokey = table2.col_varchar_key ) AND
               (table3.pk = table2.col_int_key))
    )
  ) ON
    (
      (table3.col_varchar_key = table2.col_varchar_key) OR
      (table3.col_int_key = table2.pk)
    )
)
HAVING field1 < 216;

[Sergey Petrunia (sergefp)]

Please review: https://lists.launchpad.net/maria-developers/msg02202.html

[Sergey Petrunia (sergefp)]

The fix was pushed into 5.2 some time ago