We're updating the issue view to help you get more done. 

SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling

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

1 2 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 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

Environment

Debian 6.0.6 amd64 on both machines : one running 5.5.28a-MariaDB-a1~squeeze-l and one running MySQL 5.1.66-0+squeeze1-log

Status

Assignee

Sergei Golubchik

Reporter

Jean Weisbuch

Labels

Fix versions

Affects versions

10.0.1
5.5.28a
5.3.11
5.2.13
5.1.66

Priority

Critical