Details
Critical Description
Doing a SELECT query involving a subquery on information_schema will lock the query and the whole information_schema (even if the query is done by an unpriviledged user).
All queries done afterwise on the information_schema (including the use of SHOW STATUS and SHOW VARIABLES) wont end and cannot be killed (Status: Killed and State: executing) until the mysqld process is stopped and the mysqld wont shutdown gracefully (a kill -9 must be done in order to stop it).
Here is an example of these problematic queries :
SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime' + (SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime');
====================
The query boils down to
SELECT * FROM information_schema.session_status |
WHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status); |
and one can add additional conditions of one's choice or extend it to make more realistic.
Reproducible on all current trees, MariaDB 5.1-10.0 and MySQL 5.1-5.6.
Stack trace from maria/5.5 revno 3607.
safe_mutex: Trying to lock mutex at maria-5.5/sql/sql_show.cc, line 6932, when the mutex was already locked at maria-5.5/sql/sql_show.cc, line 6932 in thread T@5
|
130114 5:39:03 [ERROR] mysqld got signal 6 ;
|
 |
#3 <signal handler called>
|
#4 0x00007f00f89b03a5 in __GI_raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
|
#5 0x00007f00f89b3b0b in __GI_abort () at abort.c:92
|
#6 0x0000000000cb1b85 in safe_mutex_lock (mp=0x149fba0, my_flags=0, file=0xd27cd0 "maria-5.5/sql/sql_show.cc", line=6932) at maria-5.5/mysys/thr_mutex.c:266
|
#7 0x000000000067c945 in inline_mysql_mutex_lock (that=0x149fba0, src_file=0xd27cd0 "maria-5.5/sql/sql_show.cc", src_line=6932) at maria-5.5/include/mysql/psi/mysql_thread.h:618
|
#8 0x0000000000694e4a in fill_status (thd=0x2b06390, tables=0x2c08720, cond=0x0) at maria-5.5/sql/sql_show.cc:6932
|
#9 0x0000000000697100 in do_fill_table (thd=0x2b06390, table_list=0x2c08720, join_table=0x2bfd138) at maria-5.5/sql/sql_show.cc:7682
|
#10 0x0000000000697512 in get_schema_tables_result (join=0x2bfc108, executed_place=PROCESSED_BY_JOIN_EXEC) at maria-5.5/sql/sql_show.cc:7789
|
#11 0x0000000000641f4d in JOIN::exec (this=0x2bfc108) at maria-5.5/sql/sql_select.cc:2313
|
#12 0x000000000085ca9c in subselect_single_select_engine::exec (this=0x2c08e30) at maria-5.5/sql/item_subselect.cc:3205
|
#13 0x0000000000855628 in Item_subselect::exec (this=0x2c08cf8) at maria-5.5/sql/item_subselect.cc:644
|
#14 0x0000000000856e8f in Item_singlerow_subselect::val_str (this=0x2c08cf8, str=0x2c08fb8) at maria-5.5/sql/item_subselect.cc:1233
|
#15 0x00000000007f3465 in Arg_comparator::compare_string (this=0x2c08f30) at maria-5.5/sql/item_cmpfunc.cc:987
|
#16 0x0000000000803919 in Arg_comparator::compare (this=0x2c08f30) at maria-5.5/sql/item_cmpfunc.h:78
|
#17 0x00000000007f5e58 in Item_func_eq::val_int (this=0x2c08e70) at maria-5.5/sql/item_cmpfunc.cc:1858
|
#18 0x00000000006847ab in show_status_array (thd=0x2b06390, wild=0x0, variables=0x29c5a00, value_type=OPT_SESSION, status_var=0x2b06d18, prefix=0xd2683b "", table=0x2c43028, ucase_names=true, cond=0x2c08e70) at maria-5.5/sql/sql_show.cc:2641
|
#19 0x0000000000694eb2 in fill_status (thd=0x2b06390, tables=0x2c07498, cond=0x2c08e70) at maria-5.5/sql/sql_show.cc:6938
|
#20 0x0000000000697100 in do_fill_table (thd=0x2b06390, table_list=0x2c07498, join_table=0x2bfe218) at maria-5.5/sql/sql_show.cc:7682
|
#21 0x0000000000697512 in get_schema_tables_result (join=0x2bfb868, executed_place=PROCESSED_BY_JOIN_EXEC) at maria-5.5/sql/sql_show.cc:7789
|
#22 0x0000000000641f4d in JOIN::exec (this=0x2bfb868) at maria-5.5/sql/sql_select.cc:2313
|
#23 0x0000000000644965 in mysql_select (thd=0x2b06390, rref_pointer_array=0x2b090f0, tables=0x2c07498, wild_num=1, fields=..., conds=0x2c08e70, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x2bfb848, unit=0x2b087c0, select_lex=0x2b08e98) at maria-5.5/sql/sql_select.cc:3054
|
#24 0x000000000063b551 in handle_select (thd=0x2b06390, lex=0x2b08710, result=0x2bfb848, setup_tables_done_option=0) at maria-5.5/sql/sql_select.cc:316
|
#25 0x00000000006147a4 in execute_sqlcom_select (thd=0x2b06390, all_tables=0x2c07498) at maria-5.5/sql/sql_parse.cc:4622
|
#26 0x000000000060d26f in mysql_execute_command (thd=0x2b06390) at maria-5.5/sql/sql_parse.cc:2182
|
#27 0x000000000061700b in mysql_parse (thd=0x2b06390, rawbuf=0x2c071a8 "SELECT * FROM information_schema.session_status \nWHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status)", length=132, parser_state=0x7f00ee08c500) at maria-5.5/sql/sql_parse.cc:5737
|
#28 0x000000000060a7e2 in dispatch_command (command=COM_QUERY, thd=0x2b06390, packet=0x2c88e01 "SELECT * FROM information_schema.session_status \nWHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status)", packet_length=132) at maria-5.5/sql/sql_parse.cc:1055
|
#29 0x0000000000609a99 in do_command (thd=0x2b06390) at maria-5.5/sql/sql_parse.cc:794
|
#30 0x000000000070c873 in do_handle_one_connection (thd_arg=0x2b06390) at maria-5.5/sql/sql_connect.cc:1253
|
#31 0x000000000070c25e in handle_one_connection (arg=0x2b06390) at maria-5.5/sql/sql_connect.cc:1168
|
#32 0x0000000000b921fb in pfs_spawn_thread (arg=0x2c87b60) at maria-5.5/storage/perfschema/pfs.cc:1015
|
#33 0x00007f00f96ecefc in start_thread (arg=0x7f00ee08d700) at pthread_create.c:304
|
#34 0x00007f00f8a5ef4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|