Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.51, 10.1.16, 10.1.17, 10.0.27, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
We reproduce crash on multiple server/configs with:
10.0.27 on debian wheezy, debian jessie
10.1.16, 10.1.17 on debian jessie
Query work fine on 10.0.26
-
10.1.18
Description
You can use this query to reproduce crash:
drop table if exists dummy; |
|
|
create table dummy ( |
field1 int |
) engine=innodb default charset=utf8; |
|
|
insert into dummy values (1); |
|
|
select round((select 1 from dummy limit 1)) |
from dummy |
group by round((select 1 from dummy limit 1)); |
Regards,
Vincent
Attachments
Activity
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
|
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.
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.
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:111The problem appeared in 5.5 tree after this commit:
commit 79f852a069fb6ba5e18fd66ea2a24fa91c245c24Author: Oleksandr Byelkin <sanja@mariadb.com>Date: Wed Jun 22 14:17:06 2016 +0200MDEV-10050: Crash in subselectthd should not be taken earlier then fix_field and reset on fix_fields if it is needed.10.2 is also affected.