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
|
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.0.1 [ 11400 ] | |
| Fix Version/s | 5.5.29 [ 12102 ] | |
| Fix Version/s | 5.3.12 [ 12000 ] | |
| Fix Version/s | 5.2.14 [ 12101 ] | |
| Fix Version/s | 5.1.67 [ 12100 ] | |
| Affects Version/s | 5.1.66 [ 10801 ] | |
| Affects Version/s | 5.2.13 [ 10800 ] | |
| Affects Version/s | 5.3.11 [ 11700 ] | |
| Affects Version/s | 5.5.28a [ 11701 ] | |
| Affects Version/s | 10.0.1 [ 11400 ] | |
| Assignee | Sergei Golubchik [ serg ] | |
| 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 : {quote}SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime' + (SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime');{quote} |
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 : {quote}SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime' + (SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime');{quote} ==================== The query boils down to {code:sql} SELECT * FROM information_schema.session_status WHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status); {code} 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. {noformat} 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 {noformat} |
| Labels | upstream |
| Remote Link | This issue links to "Bug #58738 double locking mutex when querying i_s tables in subquery (Web Link)" [ 10101 ] |
I find it weird that it was only fixed in 5.7.0, doesn't seem to be usual approach to such bugs. Maybe it's because the bug was marked as debug-only, although in fact it affects release builds also, as Jean described.
| Attachment | mdev4029.patch [ 20602 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | defaullt [ 25900 ] | MariaDB v2 [ 46155 ] |
| Workflow | MariaDB v2 [ 46155 ] | MariaDB v3 [ 66833 ] |
| Workflow | MariaDB v3 [ 66833 ] | MariaDB v4 [ 146325 ] |
I think even though it's an upstream bug, it's worth fixing in the upcoming 5.5.29 release (next releases of 5.1+ might already pick up the upstream fix, but 5.5.29 won't).