|
Thanks for the report and the test case.
|
Stack trace from 5.5 b3f7a8019d
|
#3 <signal handler called>
|
#4 0x00000000008464df in Item_subselect::const_item (this=0x7fda95949f28) at /data/src/5.5/sql/item_subselect.cc:861
|
#5 0x00000000005f8d38 in st_select_lex::optimize_unflattened_subqueries (this=0x7fda96a77a70, const_only=true) at /data/src/5.5/sql/sql_lex.cc:3502
|
#6 0x0000000000744b0d in JOIN::optimize_constant_subqueries (this=0x7fda959813a8) at /data/src/5.5/sql/opt_subselect.cc:5018
|
#7 0x00000000006327b3 in JOIN::optimize (this=0x7fda959813a8) at /data/src/5.5/sql/sql_select.cc:1020
|
#8 0x0000000000639d69 in mysql_select (thd=0x7fda96a74060, rref_pointer_array=0x7fda96a77ce0, tables=0x7fda95949788, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7fda95981298, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fda95981388, unit=0x7fda96a77390, select_lex=0x7fda96a77a70) at /data/src/5.5/sql/sql_select.cc:3080
|
#9 0x00000000006306e4 in handle_select (thd=0x7fda96a74060, lex=0x7fda96a772e0, result=0x7fda95981388, setup_tables_done_option=0) at /data/src/5.5/sql/sql_select.cc:319
|
#10 0x0000000000609be7 in execute_sqlcom_select (thd=0x7fda96a74060, all_tables=0x7fda95949788) at /data/src/5.5/sql/sql_parse.cc:4689
|
#11 0x0000000000602f38 in mysql_execute_command (thd=0x7fda96a74060) at /data/src/5.5/sql/sql_parse.cc:2234
|
#12 0x000000000060c7b2 in mysql_parse (thd=0x7fda96a74060, rawbuf=0x7fda95948078 "select round((select 1 from dummy limit 1))\nfrom dummy\ngroup by round((select 1 from dummy limit 1))", length=100, parser_state=0x7fda9d01d650) at /data/src/5.5/sql/sql_parse.cc:5934
|
#13 0x00000000006004c7 in dispatch_command (command=COM_QUERY, thd=0x7fda96a74060, packet=0x7fda9774e061 "select round((select 1 from dummy limit 1))\nfrom dummy\ngroup by round((select 1 from dummy limit 1))", packet_length=100) at /data/src/5.5/sql/sql_parse.cc:1079
|
#14 0x00000000005ff681 in do_command (thd=0x7fda96a74060) at /data/src/5.5/sql/sql_parse.cc:793
|
#15 0x00000000007018ed in do_handle_one_connection (thd_arg=0x7fda96a74060) at /data/src/5.5/sql/sql_connect.cc:1270
|
#16 0x000000000070167a in handle_one_connection (arg=0x7fda96a74060) at /data/src/5.5/sql/sql_connect.cc:1186
|
#17 0x0000000000943b53 in pfs_spawn_thread (arg=0x7fda9777a300) at /data/src/5.5/storage/perfschema/pfs.cc:1015
|
#18 0x00007fda9cc5a0a4 in start_thread (arg=0x7fda9d01e700) at pthread_create.c:309
|
#19 0x00007fda9b08087d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
|
The problem appeared in 5.5 tree after this commit:
commit 79f852a069fb6ba5e18fd66ea2a24fa91c245c24
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Wed Jun 22 14:17:06 2016 +0200
|
|
MDEV-10050: Crash in subselect
|
|
thd should not be taken earlier then fix_field and reset on fix_fields if it is needed.
|
10.2 is also affected.
|
|
Oh! it asks const_item() on unprepared Item and const_item() uses thd to check global settings.
|
|
revision-id: e1bd675446ccee64c81a0dd79dfb2b75f58c3726 (mariadb-5.5.52-2-ge1bd675)
parent(s): b3f7a8019dae01ed03353856f62543248e6f9cd9
committer: Oleksandr Byelkin
timestamp: 2016-09-21 18:36:34 +0200
message:
MDEV-10776: Server crash on query
Item_subselect::const_item() now can work before fix_fields.
Revision of thd usage in Item_sunselect*.
—
|
|
If I apply the patch to 10.2, I still get a crash here:
Program received signal SIGSEGV, Segmentation fault.
|
0x0000555555c5ee6c in Explain_node::print_explain_for_children (this=0x7fff50029358, query=0x7fff500188c0, output=0x7ffff42b5370, explain_flags=0 '\000', is_analyze=true) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:612
|
(gdb) wher
|
#0 0x0000555555c5ee6c in Explain_node::print_explain_for_children (this=0x7fff50029358, query=0x7fff500188c0, output=0x7ffff42b5370, explain_flags=0 '\000', is_analyze=true) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:612
|
#1 0x0000555555c5f70a in Explain_select::print_explain (this=0x7fff50029358, query=0x7fff500188c0, output=0x7ffff42b5370, explain_flags=0 '\000', is_analyze=true) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:796
|
#2 0x0000555555c5d323 in Explain_query::print_explain (this=0x7fff500188c0, output=0x7ffff42b5370, explain_flags=0 '\000', is_analyze=true) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:203
|
#3 0x0000555555c5d6f0 in Explain_query::print_explain_str (this=0x7fff500188c0, thd=0x7fff50000b00, out_str=0x7ffff42b54f0, is_analyze=true) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:257
|
#4 0x0000555555c5d643 in print_explain_for_slow_log (lex=0x7fff500044f8, thd=0x7fff50000b00, str=0x7ffff42b54f0) at /home/psergey/dev-git/10.2/sql/sql_explain.cc:241
|
#5 0x0000555555e44227 in MYSQL_QUERY_LOG::write (this=0x555557709f00, thd=0x7fff50000b00, current_time=1475087033, user_host=0x7ffff42b5990 "root[root] @ localhost []", user_host_len=25, query_utime=3870282, lock_utime=12834, is_command=false, sql_text=0x7fff50013108 "select round((select 1 from dummy limit 1)) from dummy group by round((select 1 from dummy limit 1))", sql_text_len=100) at /home/psergey/dev-git/10.2/sql/log.cc:3047
|
#6 0x0000555555e3edc2 in Log_to_file_event_handler::log_slow (this=0x5555577099d0, thd=0x7fff50000b00, current_time=..., user_host=0x7ffff42b5990 "root[root] @ localhost []", user_host_len=25, query_utime=3870282, lock_utime=12834, is_command=false, sql_text=0x7fff50013108 "select round((select 1 from dummy limit 1)) from dummy group by round((select 1 from dummy limit 1))", sql_text_len=100) at /home/psergey/dev-git/10.2/sql/log.cc:1043
|
#7 0x0000555555e3f911 in LOGGER::slow_log_print (this=0x555556eadf40, thd=0x7fff50000b00, query=0x7fff50013108 "select round((select 1 from dummy limit 1)) from dummy group by round((select 1 from dummy limit 1))", query_length=100, current_utime=234533213572) at /home/psergey/dev-git/10.2/sql/log.cc:1334
|
#8 0x0000555555e4d3a1 in slow_log_print (thd=0x7fff50000b00, query=0x7fff50013108 "select round((select 1 from dummy limit 1)) from dummy group by round((select 1 from dummy limit 1))", query_length=100, current_utime=234533213572) at /home/psergey/dev-git/10.2/sql/log.cc:6398
|
#9 0x0000555555ae072c in log_slow_statement (thd=0x7fff50000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:2447
|
#10 0x0000555555ae0339 in dispatch_command (command=COM_QUERY, thd=0x7fff50000b00, packet=0x7fff50008771 "", packet_length=100, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:2365
|
#11 0x0000555555add10b in do_command (thd=0x7fff50000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1365
|
#12 0x0000555555c20f35 in do_handle_one_connection (connect=0x555557e7a940) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1354
|
#13 0x0000555555c20cb7 in handle_one_connection (arg=0x555557e7a940) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1260
|
#14 0x00007ffff691de9a in start_thread (arg=0x7ffff42b7300) at pthread_create.c:308
|
#15 0x00007ffff5e383fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
My extra settings in my.cnf:
slow-query-log
|
long-query-time=0.01
|
log-slow-verbosity=query_plan,explain
|
|
|
Discussed the patch, agreed to change it.
|
|
revision-id: 267a1428f19258ec620cadaaefbf3636dc9fe43a (mariadb-5.5.52-2-g267a142)
parent(s): b3f7a8019dae01ed03353856f62543248e6f9cd9
committer: Oleksandr Byelkin
timestamp: 2016-09-28 22:27:17 +0200
message:
MDEV-10776: Server crash on query
Exclude untouched in prepare phese subqueries from the select/unit tree
because they became unreachable by execution.
—
|
|
We are affected by this bug. Actually, this is the simplest way we found to reproduce it which looks pretty similar:
CREATE TABLE d (i integer);
SELECT concat((SELECT "a" from d),"b")
FROM dual
GROUP BY concat((SELECT "a" from d),"b");
And maybe this can help: the GROUP BY clause string must be exactly the very same as the one in the SELECT clause, in this case:
concat((SELECT "a" from d),"b")
If any of those instances are subtly changed, as for example simply adding a useless space, the crash does not happen any more.
|
|
Ok to push. Sorry for the delay.
|
|
Note: the above example from gschulman actually doesn't parse due to the way how FROM dual is handled in MySQL. If one replaces dual with some other table, it works.
|
|
Thanks psergey for working on this.
Not sure if I understand your comment about my example, but I can reproduce the bug both ways, using dual or using a table. Let me share it from my console:
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 39
|
Server version: 10.1.17-MariaDB-1~precise mariadb.org binary distribution
|
|
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> SELECT concat((SELECT "a" from d),"b")
|
-> FROM dual
|
-> GROUP BY concat((SELECT "a" from d),"b");
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [test]>
|
Here the server crashed and restarted.
MariaDB [test]> create table a (i integer);
|
Query OK, 0 rows affected (0.01 sec)
|
MariaDB [test]>
|
MariaDB [test]> SELECT concat((SELECT "a" from d),"b")
|
-> FROM a
|
-> GROUP BY concat((SELECT "a" from d),"b");
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 2
|
Current database: test
|
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [test]>
|
|
|
gschulman, thanks for the note.
The patch was made against MariaDB-10.0. I can confirm that our example with FROM a works on the patched 10.0.
However, 10.0 gives a parse error for the FROM dual variant. The patch for this issue hasn't been merged into MariaDB-10.1 yet, but I think we have all reasons to believe that the FROM dual variant will work too when the patch is merged into 10.1.
|
|
Oh, ok, now it's clear what you meant, psergey.
BTW, even if the fix seems to be already pushed, let me add:
1 - it can be reproduced by just running the EXPLAIN for those queries, clearly meaning that the problem was in the planner.
2 - replacing GROUP BY by ORDER BY in those queries also triggers the problem.
Thanks again.
|