Re: Running RQG optimizer_no_subquery crashes MariaDB

optimizer_no_subquery.trace
LPexportBug523593_optimizer_no_subquery.trace

Re: [Bug 523593] [NEW] Running RQG optimizer_no_subquery crashes MariaDB
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 ??? 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
>
> ** Affects: maria
> Importance: Undecided
> Status: New
>
>
> ** Tags: rqg
>

Re: Running RQG optimizer_no_subquery crashes MariaDB
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.

Re: Running RQG optimizer_no_subquery crashes MariaDB
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:

1. 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
2. 12:52:08 #0 __pthread_kill (threadid=<value optimized out>, signo=<value optimized out>)
3. 12:52:08 at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:75
4. 12:52:08 #1 0x000000000061a73d in handle_segfault (sig=11) at mysqld.cc:2655
5. 12:52:08 #2 <signal handler called>
6. 12:52:08 #3 0x00000000007a31c2 in build_eq_mods_for_cond (ctx=0x7fc95721e320, eq_mod=0x7fc95721e580, and_level=0x7fc95721e58c,
7. 12:52:08 cond=0x37e3af0) at opt_table_elimination.cc:1315
8. 12:52:08 #4 0x00000000007a3959 in check_func_dependency (join=<value optimized out>, dep_tables=1, it=0x6, oj_tbl=0x3759f00,
9. 12:52:08 cond=0x37e3af0) at opt_table_elimination.cc:815
10. 12:52:08 #5 0x00000000007a3c73 in eliminate_tables_for_list (join=0x37687b8, join_list=<value optimized out>, list_tables=7,
11. 12:52:08 on_expr=0x0, tables_used_elsewhere=6) at opt_table_elimination.cc:715
12. 12:52:08 #6 0x000000000068dc4a in make_join_statistics (join=0x37687b8, tables_arg=0x36c77f0, conds=0x37e2ec8,
13. 12:52:08 keyuse_array=0x3769d80) at sql_select.cc:2708
14. 12:52:08 #7 0x000000000068fe4b in JOIN::optimize (this=0x37687b8) at sql_select.cc:988
15. 12:52:08 #8 0x00000000006985c4 in mysql_select (thd=0x7fc97495b5f0, rref_pointer_array=0x7fc97495d6b8, tables=0x36c77f0, wild_num=0,
16. 12:52:08 fields=@0x0, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x37e4118, proc_param=0x0, select_options=1,
17. 12:52:08 result=0x37e4328, unit=0x7fc97495d0c0, select_lex=0x7fc97495d4e8) at sql_select.cc:2453
18. 12:52:08 #9 0x00000000006990d1 in handle_select (thd=0x7fc97495b5f0, lex=0x7fc97495d020, result=0x37e4328,
19. 12:52:08 setup_tables_done_option=0) at sql_select.cc:279
20. 12:52:08 #10 0x00000000006287d8 in execute_sqlcom_select (thd=0x7fc97495b5f0, all_tables=0x36c77f0) at sql_parse.cc:5112
21. 12:52:08 #11 0x000000000062d486 in mysql_execute_command (thd=0x7fc97495b5f0) at sql_parse.cc:2297
22. 12:52:08 #12 0x0000000000631351 in mysql_parse (thd=0x7fc97495b5f0,
23. 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,
24. 12:52:08 found_semicolon=0x7fc957220ac0) at sql_parse.cc:6033

Re: Running RQG optimizer_no_subquery crashes MariaDB
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;

Re: Running RQG optimizer_no_subquery crashes MariaDB
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.

Re: Running RQG optimizer_no_subquery crashes MariaDB
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;

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;
Test dataset
LPexportBug523593_bug523593.sql.gz

Re: Running RQG optimizer_no_subquery crashes MariaDB
Please review: https://lists.launchpad.net/maria-developers/msg02202.html

Re: Running RQG optimizer_no_subquery crashes MariaDB
The fix was pushed into 5.2 some time ago

Launchpad bug id: 523593