[#MDEV-4029] SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling

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).

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

[MDEV-4029] SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling Created: 2013-01-14 Updated: 2013-01-21 Resolved: 2013-01-21
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.1, 5.5.28a, 5.3.11, 5.2.13, 5.1.66
Fix Version/s:	10.0.1, 5.5.29, 5.1.67, 5.2.14, 5.3.12

[MDEV-4029] SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling Created: 2013-01-14 Updated: 2013-01-21 Resolved: 2013-01-21