Bug #71236 Deadlock between mysql_change_user(), SHOW VARIABLES and INSTALL PLUGIN
Submitted: 27 Dec 2013 10:18 Modified: 1 Aug 2014 16:43
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.73, 5.5.35, 5.6.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock, LOCK_global_system_variables, LOCK_plugin, LOCK_system_variables_hash

[27 Dec 2013 10:18] Laurynas Biveinis
Description:
This is a copy of https://mariadb.atlassian.net/browse/MDEV-5345, credit goes to Sergey Vojtovich.

A server may deadlock if three threads perform at the same time:
- INSTALL PLUGIN,
- SHOW VARIABLES, and
- mysql_change_user().

How to repeat:
delimiter |;
CREATE PROCEDURE p_install(x INT)
BEGIN
  DECLARE CONTINUE HANDLER FOR 1126 BEGIN END;
  WHILE x DO
    SET x= x - 1;
    INSTALL PLUGIN no_such_plugin SONAME 'no_such_object';
  END WHILE;
END|

CREATE PROCEDURE p_show_vars(x INT)
WHILE x DO
  SET x= x - 1;
  SHOW VARIABLES;
END WHILE|
delimiter ;|

connect(con1, localhost, root,,);
connect(con2, localhost, root,,);

connection con1;
--send CALL p_install(100);

connection con2;
--send CALL p_show_vars(100);

connection default;

disable_result_log;
let $i= 100;
while ($i)
{
  change_user;
  dec $i;
}

connection con1;
reap;
connection con2;
reap;
connection default;
enable_result_log;

disconnect con1;
disconnect con2;
DROP PROCEDURE p_install;
DROP PROCEDURE p_show_vars;

Suggested fix:
.
[27 Dec 2013 10:21] Laurynas Biveinis
Synchronization primitives involved are LOCK_plugin, LOCK_global_system_variables, and LOCK_system_variables_hash.

The deadlocked threads, as seen by GDB on the provided MTR testcase:

Thread 4 (Thread 0x7fd61ca64700 (LWP 18007)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007fd62910b192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007fd62910b110 in __GI___pthread_mutex_lock (mutex=0x17fc528 <LOCK_plugin+40>) at pthread_mutex_lock.c:104
#3  0x0000000000aacdff in safe_mutex_lock (mp=0x17fc500 <LOCK_plugin>, try_lock=0 '\000', file=0xf180d0 "/home/laurynas/percona/src/5.6/sql/sql_plugin.cc", line=2764)
    at /home/laurynas/percona/src/5.6/mysys/thr_mutex.c:152
#4  0x00000000007dbe0c in inline_mysql_mutex_lock (that=0x17fc500 <LOCK_plugin>, src_file=0xf180d0 "/home/laurynas/percona/src/5.6/sql/sql_plugin.cc", src_line=2764)
    at /home/laurynas/percona/src/5.6/include/mysql/psi/mysql_thread.h:669
#5  0x00000000007e3208 in plugin_thdvar_init (thd=0x2fb8bc0, enable_plugins=true) at /home/laurynas/percona/src/5.6/sql/sql_plugin.cc:2764
#6  0x000000000078133d in THD::init (this=0x2fb8bc0) at /home/laurynas/percona/src/5.6/sql/sql_class.cc:1357
#7  0x0000000000781705 in THD::change_user (this=0x2fb8bc0) at /home/laurynas/percona/src/5.6/sql/sql_class.cc:1451
#8  0x00000000007c8e49 in dispatch_command (command=COM_CHANGE_USER, thd=0x2fb8bc0, packet=0x308ebf1 "root", packet_length=142) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1236
#9  0x00000000007c87ba in do_command (thd=0x2fb8bc0) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1036
#10 0x000000000078f438 in do_handle_one_connection (thd_arg=0x2fb8bc0) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:982
#11 0x000000000078ef4e in handle_one_connection (arg=0x2fb8bc0) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:898
#12 0x0000000000b7e4c8 in pfs_spawn_thread (arg=0x3009280) at /home/laurynas/percona/src/5.6/storage/perfschema/pfs.cc:1858
#13 0x00007fd629108f6e in start_thread (arg=0x7fd61ca64700) at pthread_create.c:311
#14 0x00007fd6286159cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113
[27 Dec 2013 10:21] Laurynas Biveinis
Thread 3 (Thread 0x7fd61ca23700 (LWP 18011)):
#0  pthread_rwlock_wrlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_wrlock.S:85
#1  0x00000000007dbfce in inline_mysql_rwlock_wrlock (that=0x17f3ca0 <LOCK_system_variables_hash>, src_file=0xf180d0 "/home/laurynas/percona/src/5.6/sql/sql_plugin.cc", src_line=1860)
    at /home/laurynas/percona/src/5.6/include/mysql/psi/mysql_thread.h:937
#2  0x00000000007e093b in mysql_install_plugin (thd=0x3092d30, name=0x7fd600015970, dl=0x7fd600015980) at /home/laurynas/percona/src/5.6/sql/sql_plugin.cc:1860
#3  0x00000000007d2d8e in mysql_execute_command (thd=0x3092d30) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:4861
#4  0x000000000098417a in sp_instr_stmt::exec_core (this=0x7fd6000094b8, thd=0x3092d30, nextp=0x7fd61ca20438) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:889
#5  0x0000000000983028 in sp_lex_instr::reset_lex_and_exec_core (this=0x7fd6000094b8, thd=0x3092d30, nextp=0x7fd61ca20438, open_tables=false) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:390
#6  0x0000000000983938 in sp_lex_instr::validate_lex_and_execute_core (this=0x7fd6000094b8, thd=0x3092d30, nextp=0x7fd61ca20438, open_tables=false) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:626
#7  0x0000000000983e62 in sp_instr_stmt::execute (this=0x7fd6000094b8, thd=0x3092d30, nextp=0x7fd61ca20438) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:796
#8  0x0000000000723a00 in sp_head::execute (this=0x7fd600008070, thd=0x3092d30, merge_da_on_success=true) at /home/laurynas/percona/src/5.6/sql/sp_head.cc:645
#9  0x000000000072591c in sp_head::execute_procedure (this=0x7fd600008070, thd=0x3092d30, args=0x3095bb0) at /home/laurynas/percona/src/5.6/sql/sp_head.cc:1315
#10 0x00000000007d1ed3 in mysql_execute_command (thd=0x3092d30) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:4560
#11 0x00000000007d61f0 in mysql_parse (thd=0x3092d30, rawbuf=0x7fd600004c70 "CALL p_install(100)", length=19, parser_state=0x7fd61ca21e40) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:6235
#12 0x00000000007c966a in dispatch_command (command=COM_QUERY, thd=0x3092d30, packet=0x3096e21 "CALL p_install(100);", packet_length=20) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1334
#13 0x00000000007c87ba in do_command (thd=0x3092d30) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1036
#14 0x000000000078f438 in do_handle_one_connection (thd_arg=0x3092d30) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:982
#15 0x000000000078ef4e in handle_one_connection (arg=0x3092d30) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:898
#16 0x0000000000b7e4c8 in pfs_spawn_thread (arg=0x3009ee0) at /home/laurynas/percona/src/5.6/storage/perfschema/pfs.cc:1858
#17 0x00007fd629108f6e in start_thread (arg=0x7fd61ca23700) at pthread_create.c:311
#18 0x00007fd6286159cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

Thread 2 (Thread 0x7fd61c9e2700 (LWP 18012)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007fd62910b192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007fd62910b110 in __GI___pthread_mutex_lock (mutex=0x17f3608 <LOCK_global_system_variables+40>) at pthread_mutex_lock.c:104
#3  0x0000000000aacdff in safe_mutex_lock (mp=0x17f35e0 <LOCK_global_system_variables>, try_lock=0 '\000', file=0xf1f728 "/home/laurynas/percona/src/5.6/sql/sql_show.cc", line=2501)
    at /home/laurynas/percona/src/5.6/mysys/thr_mutex.c:152
#4  0x000000000080e6ff in inline_mysql_mutex_lock (that=0x17f35e0 <LOCK_global_system_variables>, src_file=0xf1f728 "/home/laurynas/percona/src/5.6/sql/sql_show.cc", src_line=2501)
    at /home/laurynas/percona/src/5.6/include/mysql/psi/mysql_thread.h:669
#5  0x00000000008161ec in show_status_array (thd=0x309af60, wild=0x0, variables=0x7fd604022398, value_type=OPT_SESSION, status_var=0x0, prefix=0xf203f9 "", table=0x7fd60401c930, ucase_names=false, cond=
    0x0) at /home/laurynas/percona/src/5.6/sql/sql_show.cc:2501
#6  0x0000000000825d37 in fill_variables (thd=0x309af60, tables=0x7fd60400f8c8, cond=0x0) at /home/laurynas/percona/src/5.6/sql/sql_show.cc:6471
#7  0x0000000000828303 in do_fill_table (thd=0x309af60, table_list=0x7fd60400f8c8, join_table=0x7fd60401b9d0) at /home/laurynas/percona/src/5.6/sql/sql_show.cc:7184
#8  0x00000000008286ed in get_schema_tables_result (join=0x7fd60401ad98, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/laurynas/percona/src/5.6/sql/sql_show.cc:7285
#9  0x00000000007fbc90 in JOIN::prepare_result (this=0x7fd60401ad98, columns_list=0x7fd61c9ddb00) at /home/laurynas/percona/src/5.6/sql/sql_select.cc:823
#10 0x0000000000799a5a in JOIN::exec (this=0x7fd60401ad98) at /home/laurynas/percona/src/5.6/sql/sql_executor.cc:116
#11 0x00000000007fc7d3 in mysql_execute_select (thd=0x309af60, select_lex=0x7fd604012ab8, free_join=true) at /home/laurynas/percona/src/5.6/sql/sql_select.cc:1100
#12 0x00000000007fcacf in mysql_select (thd=0x309af60, tables=0x7fd60400f8c8, wild_num=0, fields=..., conds=0x0, order=0x7fd604012c80, group=0x7fd604012bb8, having=0x0, select_options=2684620544, 
    result=0x7fd60401ad70, unit=0x7fd604012470, select_lex=0x7fd604012ab8) at /home/laurynas/percona/src/5.6/sql/sql_select.cc:1221
#13 0x00000000007fa942 in handle_select (thd=0x309af60, result=0x7fd60401ad70, setup_tables_done_option=0) at /home/laurynas/percona/src/5.6/sql/sql_select.cc:110
#14 0x00000000007d3a6b in execute_sqlcom_select (thd=0x309af60, all_tables=0x7fd60400f8c8) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:5094
#15 0x00000000007cc65b in mysql_execute_command (thd=0x309af60) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:2642
#16 0x000000000098417a in sp_instr_stmt::exec_core (this=0x7fd60400fe38, thd=0x309af60, nextp=0x7fd61c9df438) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:889
#17 0x0000000000983028 in sp_lex_instr::reset_lex_and_exec_core (this=0x7fd60400fe38, thd=0x309af60, nextp=0x7fd61c9df438, open_tables=false) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:390
#18 0x0000000000983938 in sp_lex_instr::validate_lex_and_execute_core (this=0x7fd60400fe38, thd=0x309af60, nextp=0x7fd61c9df438, open_tables=false) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:626
#19 0x0000000000983e62 in sp_instr_stmt::execute (this=0x7fd60400fe38, thd=0x309af60, nextp=0x7fd61c9df438) at /home/laurynas/percona/src/5.6/sql/sp_instr.cc:796
#20 0x0000000000723a00 in sp_head::execute (this=0x7fd604008010, thd=0x309af60, merge_da_on_success=true) at /home/laurynas/percona/src/5.6/sql/sp_head.cc:645
#21 0x000000000072591c in sp_head::execute_procedure (this=0x7fd604008010, thd=0x309af60, args=0x309dde0) at /home/laurynas/percona/src/5.6/sql/sp_head.cc:1315
#22 0x00000000007d1ed3 in mysql_execute_command (thd=0x309af60) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:4560
#23 0x00000000007d61f0 in mysql_parse (thd=0x309af60, rawbuf=0x7fd604004c70 "CALL p_show_vars(100)", length=21, parser_state=0x7fd61c9e0e40) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:6235
#24 0x00000000007c966a in dispatch_command (command=COM_QUERY, thd=0x309af60, packet=0x309f051 "CALL p_show_vars(100);", packet_length=22) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1334
#25 0x00000000007c87ba in do_command (thd=0x309af60) at /home/laurynas/percona/src/5.6/sql/sql_parse.cc:1036
#26 0x000000000078f438 in do_handle_one_connection (thd_arg=0x309af60) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:982
#27 0x000000000078ef4e in handle_one_connection (arg=0x309af60) at /home/laurynas/percona/src/5.6/sql/sql_connect.cc:898
#28 0x0000000000b7e4c8 in pfs_spawn_thread (arg=0x3053750) at /home/laurynas/percona/src/5.6/storage/perfschema/pfs.cc:1858
#29 0x00007fd629108f6e in start_thread (arg=0x7fd61c9e2700) at pthread_create.c:311
#30 0x00007fd6286159cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113
[27 Dec 2013 10:27] Laurynas Biveinis
On 5.5:

Thread 4 (Thread 0x7ff63c080700 (LWP 29244)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007ff64003a192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007ff64003a110 in __GI___pthread_mutex_lock (mutex=0x12c18c8 <LOCK_plugin+40>) at pthread_mutex_lock.c:104
#3  0x00000000008d9933 in safe_mutex_lock (mp=0x12c18a0 <LOCK_plugin>, try_lock=0 '\000', file=0xb98e78 "/home/laurynas/percona/src/5.5/sql/sql_plugin.cc", line=2689)
    at /home/laurynas/percona/src/5.5/mysys/thr_mutex.c:152
#4  0x00000000005f9e99 in inline_mysql_mutex_lock (that=0x12c18a0 <LOCK_plugin>, src_file=0xb98e78 "/home/laurynas/percona/src/5.5/sql/sql_plugin.cc", src_line=2689)
#5  0x0000000000600e27 in plugin_thdvar_init (thd=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_plugin.cc:2689
#6  0x00000000005b7bf9 in THD::init (this=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_class.cc:1211
#7  0x00000000005b7e93 in THD::change_user (this=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_class.cc:1287
#8  0x00000000005e7cb2 in dispatch_command (command=COM_CHANGE_USER, thd=0x22ad4b0, packet=0x2332231 "root", packet_length=35) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:944
#9  0x00000000005e7725 in do_command (thd=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:773
#10 0x00000000006d2c60 in do_handle_one_connection (thd_arg=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:862
#11 0x00000000006d27a6 in handle_one_connection (arg=0x22ad4b0) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:781
#12 0x000000000096a652 in pfs_spawn_thread (arg=0x2314790) at /home/laurynas/percona/src/5.5/storage/perfschema/pfs.cc:1015
#13 0x00007ff640037f6e in start_thread (arg=0x7ff63c080700) at pthread_create.c:311
#14 0x00007ff63f31d9cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113
[27 Dec 2013 10:27] Laurynas Biveinis
Thread 3 (Thread 0x7ff6357fa700 (LWP 29245)):
#0  pthread_rwlock_wrlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_wrlock.S:85
#1  0x00000000005fa0ec in inline_mysql_rwlock_wrlock (that=0x12be1c0 <LOCK_system_variables_hash>, src_file=0xb98e78 "/home/laurynas/percona/src/5.5/sql/sql_plugin.cc", src_line=1798)
    at /home/laurynas/percona/src/5.5/include/mysql/psi/mysql_thread.h:814
#2  0x00000000005fe6c5 in mysql_install_plugin (thd=0x2336240, name=0x7ff624013600, dl=0x7ff624013610) at /home/laurynas/percona/src/5.5/sql/sql_plugin.cc:1798
#3  0x00000000005f1507 in mysql_execute_command (thd=0x2336240) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:4367
#4  0x0000000000865fe2 in sp_instr_stmt::exec_core (this=0x7ff624010238, thd=0x2336240, nextp=0x7ff6357f7954) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:3208
#5  0x0000000000865849 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7ff624010278, thd=0x2336240, nextp=0x7ff6357f7954, open_tables=false, instr=0x7ff624010238)
    at /home/laurynas/percona/src/5.5/sql/sp_head.cc:2997
#6  0x0000000000865d9a in sp_instr_stmt::execute (this=0x7ff624010238, thd=0x2336240, nextp=0x7ff6357f7954) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:3140
#7  0x00000000008619b6 in sp_head::execute (this=0x7ff624007fa0, thd=0x2336240, merge_da_on_success=true) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:1434
#8  0x00000000008638c4 in sp_head::execute_procedure (this=0x7ff624007fa0, thd=0x2336240, args=0x2338890) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:2198
#9  0x00000000005f06b4 in mysql_execute_command (thd=0x2336240) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:4071
#10 0x00000000005f43c7 in mysql_parse (thd=0x2336240, rawbuf=0x7ff624004c30 "CALL p_install(100)", length=19, parser_state=0x7ff6357f9250) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:5664
#11 0x00000000005e841d in dispatch_command (command=COM_QUERY, thd=0x2336240, packet=0x2339a91 "CALL p_install(100);", packet_length=20) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:1040
#12 0x00000000005e7725 in do_command (thd=0x2336240) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:773
#13 0x00000000006d2c60 in do_handle_one_connection (thd_arg=0x2336240) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:862
#14 0x00000000006d27a6 in handle_one_connection (arg=0x2336240) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:781
#15 0x000000000096a652 in pfs_spawn_thread (arg=0x22fdc30) at /home/laurynas/percona/src/5.5/storage/perfschema/pfs.cc:1015
#16 0x00007ff640037f6e in start_thread (arg=0x7ff6357fa700) at pthread_create.c:311
#17 0x00007ff63f31d9cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

Thread 2 (Thread 0x7ff6357b9700 (LWP 29246)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007ff64003a192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007ff64003a110 in __GI___pthread_mutex_lock (mutex=0x12c18c8 <LOCK_plugin+40>) at pthread_mutex_lock.c:104
#3  0x00000000008d9933 in safe_mutex_lock (mp=0x12c18a0 <LOCK_plugin>, try_lock=0 '\000', file=0xb98e78 "/home/laurynas/percona/src/5.5/sql/sql_plugin.cc", line=1066)
    at /home/laurynas/percona/src/5.5/mysys/thr_mutex.c:152
#4  0x00000000005f9e99 in inline_mysql_mutex_lock (that=0x12c18a0 <LOCK_plugin>, src_file=0xb98e78 "/home/laurynas/percona/src/5.5/sql/sql_plugin.cc", src_line=1066)
    at /home/laurynas/percona/src/5.5/include/mysql/psi/mysql_thread.h:615
#5  0x00000000005fc707 in plugin_unlock (thd=0x0, plugin=0x7ff62801a5f0) at /home/laurynas/percona/src/5.5/sql/sql_plugin.cc:1066
#6  0x0000000000635db4 in create_myisam_from_heap (thd=0x233dbd0, table=0x7ff62801c620, param=0x7ff62801a868, error=135, ignore_last_dupp_key_error=false)
    at /home/laurynas/percona/src/5.5/sql/sql_select.cc:11371
#7  0x0000000000653143 in schema_table_store_record (thd=0x233dbd0, table=0x7ff62801c620) at /home/laurynas/percona/src/5.5/sql/sql_show.cc:2423
#8  0x0000000000652f85 in show_status_array (thd=0x233dbd0, wild=0x0, variables=0x7ff628024350, value_type=OPT_SESSION, status_var=0x0, prefix=0xb9fd21 "", table=0x7ff62801c620, ucase_names=false, cond=
    0x0) at /home/laurynas/percona/src/5.5/sql/sql_show.cc:2356
#9  0x0000000000661f0d in fill_variables (thd=0x233dbd0, tables=0x7ff62800f4f8, cond=0x0) at /home/laurynas/percona/src/5.5/sql/sql_show.cc:6184
#10 0x00000000006642ab in do_fill_table (thd=0x233dbd0, table_list=0x7ff62800f4f8, join_table=0x7ff628020328) at /home/laurynas/percona/src/5.5/sql/sql_show.cc:6886
#11 0x00000000006646eb in get_schema_tables_result (join=0x7ff62801aa48, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/laurynas/percona/src/5.5/sql/sql_show.cc:6992
#12 0x000000000061cfc2 in JOIN::exec (this=0x7ff62801aa48) at /home/laurynas/percona/src/5.5/sql/sql_select.cc:1919
#13 0x000000000061f70e in mysql_select (thd=0x233dbd0, rref_pointer_array=0x7ff628012b08, tables=0x7ff62800f4f8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, 
    proc_param=0x0, select_options=2684620544, result=0x7ff62801aa28, unit=0x7ff628012300, select_lex=0x7ff628012920) at /home/laurynas/percona/src/5.5/sql/sql_select.cc:2604
#14 0x0000000000617627 in handle_select (thd=0x233dbd0, lex=0x7ff628012250, result=0x7ff62801aa28, setup_tables_done_option=0) at /home/laurynas/percona/src/5.5/sql/sql_select.cc:297
---Type <return> to continue, or q <return> to quit---
#15 0x00000000005f21b8 in execute_sqlcom_select (thd=0x233dbd0, all_tables=0x7ff62800f4f8) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:4627
#16 0x00000000005eae5d in mysql_execute_command (thd=0x233dbd0) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:2178
#17 0x0000000000865fe2 in sp_instr_stmt::exec_core (this=0x7ff62800fa20, thd=0x233dbd0, nextp=0x7ff6357b6954) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:3208
#18 0x0000000000865849 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7ff62800fa60, thd=0x233dbd0, nextp=0x7ff6357b6954, open_tables=false, instr=0x7ff62800fa20)
    at /home/laurynas/percona/src/5.5/sql/sp_head.cc:2997
#19 0x0000000000865d9a in sp_instr_stmt::execute (this=0x7ff62800fa20, thd=0x233dbd0, nextp=0x7ff6357b6954) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:3140
#20 0x00000000008619b6 in sp_head::execute (this=0x7ff628007f40, thd=0x233dbd0, merge_da_on_success=true) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:1434
#21 0x00000000008638c4 in sp_head::execute_procedure (this=0x7ff628007f40, thd=0x233dbd0, args=0x2340220) at /home/laurynas/percona/src/5.5/sql/sp_head.cc:2198
#22 0x00000000005f06b4 in mysql_execute_command (thd=0x233dbd0) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:4071
#23 0x00000000005f43c7 in mysql_parse (thd=0x233dbd0, rawbuf=0x7ff628004c30 "CALL p_show_vars(100)", length=21, parser_state=0x7ff6357b8250) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:5664
#24 0x00000000005e841d in dispatch_command (command=COM_QUERY, thd=0x233dbd0, packet=0x2341421 "CALL p_show_vars(100);", packet_length=22) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:1040
#25 0x00000000005e7725 in do_command (thd=0x233dbd0) at /home/laurynas/percona/src/5.5/sql/sql_parse.cc:773
#26 0x00000000006d2c60 in do_handle_one_connection (thd_arg=0x233dbd0) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:862
#27 0x00000000006d27a6 in handle_one_connection (arg=0x233dbd0) at /home/laurynas/percona/src/5.5/sql/sql_connect.cc:781
#28 0x000000000096a652 in pfs_spawn_thread (arg=0x2314790) at /home/laurynas/percona/src/5.5/storage/perfschema/pfs.cc:1015
#29 0x00007ff640037f6e in start_thread (arg=0x7ff6357b9700) at pthread_create.c:311
#30 0x00007ff63f31d9cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113
[27 Dec 2013 10:31] Laurynas Biveinis
On 5.5 it appears to be a two-way deadlock between LOCK_plugin and LOCK_system_variables_hash as opposed to a three-way deadlock in 5.6. Thread 4 and Thread 3 above.
[27 Dec 2013 10:48] Laurynas Biveinis
Appears to be three-way on 5.1:

Thread 4 (Thread 0x7fe22fc69700 (LWP 28101)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007fe22f9a5192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007fe22f9a5110 in __GI___pthread_mutex_lock (mutex=0xeef0c8 <LOCK_plugin+40>) at pthread_mutex_lock.c:104
#3  0x0000000000940c33 in safe_mutex_lock (mp=0xeef0a0 <LOCK_plugin>, try_lock=0 '\000', file=0xadab36 "sql_plugin.cc", line=2533) at thr_mutex.c:151
#4  0x0000000000877d60 in plugin_thdvar_init (thd=0x13772b8) at sql_plugin.cc:2533
#5  0x0000000000630825 in THD::init (this=0x13772b8) at sql_class.cc:845
#6  0x0000000000630b71 in THD::change_user (this=0x13772b8) at sql_class.cc:933
#7  0x000000000065ca2c in dispatch_command (command=COM_CHANGE_USER, thd=0x13772b8, packet=0x13c9bf9 "root", packet_length=13) at sql_parse.cc:1114
#8  0x000000000065c13f in do_command (thd=0x13772b8) at sql_parse.cc:893
#9  0x000000000065a331 in handle_one_connection (arg=0x13772b8) at sql_connect.cc:1506
#10 0x00007fe22f9a2f6e in start_thread (arg=0x7fe22fc69700) at pthread_create.c:311
#11 0x00007fe22ed739cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

Thread 3 (Thread 0x7fe22fc28700 (LWP 28102)):
#0  pthread_rwlock_wrlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_wrlock.S:85
#1  0x00000000008758df in mysql_install_plugin (thd=0x13cdc38, name=0x7fe2240144f0, dl=0x7fe224014500) at sql_plugin.cc:1714
#2  0x000000000066819b in mysql_execute_command (thd=0x13cdc38) at sql_parse.cc:5045
#3  0x000000000084ee70 in sp_instr_stmt::exec_core (this=0x7fe224014c10, thd=0x13cdc38, nextp=0x7fe22fc25e94) at sp_head.cc:2980
#4  0x000000000084e797 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fe224014c50, thd=0x13cdc38, nextp=0x7fe22fc25e94, open_tables=false, instr=0x7fe224014c10) at sp_head.cc:2801
#5  0x000000000084ec28 in sp_instr_stmt::execute (this=0x7fe224014c10, thd=0x13cdc38, nextp=0x7fe22fc25e94) at sp_head.cc:2923
#6  0x000000000084a923 in sp_head::execute (this=0x7fe22400bc68, thd=0x13cdc38) at sp_head.cc:1292
#7  0x000000000084c7a2 in sp_head::execute_procedure (this=0x7fe22400bc68, thd=0x13cdc38, args=0x13d00c0) at sp_head.cc:2023
#8  0x000000000066677d in mysql_execute_command (thd=0x13cdc38) at sql_parse.cc:4551
#9  0x000000000066ae61 in mysql_parse (thd=0x13cdc38, rawbuf=0x7fe224004b88 "CALL p_install(100)", length=19, found_semicolon=0x7fe22fc27400) at sql_parse.cc:6138
#10 0x000000000065d32f in dispatch_command (command=COM_QUERY, thd=0x13cdc38, packet=0x13d06c9 "CALL p_install(100);", packet_length=20) at sql_parse.cc:1288
#11 0x000000000065c13f in do_command (thd=0x13cdc38) at sql_parse.cc:893
#12 0x000000000065a331 in handle_one_connection (arg=0x13cdc38) at sql_connect.cc:1506
#13 0x00007fe22f9a2f6e in start_thread (arg=0x7fe22fc28700) at pthread_create.c:311
#14 0x00007fe22ed739cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

Thread 2 (Thread 0x7fe22e261700 (LWP 28103)):
#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x00007fe22f9a5192 in _L_lock_1142 () from /lib/x86_64-linux-gnu/libpthread.so.0
#2  0x00007fe22f9a5110 in __GI___pthread_mutex_lock (mutex=0xee32e8 <LOCK_global_system_variables+40>) at pthread_mutex_lock.c:104
#3  0x0000000000940c33 in safe_mutex_lock (mp=0xee32c0 <LOCK_global_system_variables>, try_lock=0 '\000', file=0xac0d80 "sql_show.cc", line=2274) at thr_mutex.c:151
#4  0x00000000007e9432 in show_status_array (thd=0x13d4708, wild=0x0, variables=0x7fe21801f300, value_type=OPT_SESSION, status_var=0x0, prefix=0xabf9e2 "", table=0x7fe218003238, ucase_names=false, cond=
    0x0) at sql_show.cc:2274
#5  0x00000000007f696e in fill_variables (thd=0x13d4708, tables=0x7fe218014c10, cond=0x0) at sql_show.cc:5538
#6  0x00000000007f91ad in get_schema_tables_result (join=0x7fe21801a100, executed_place=PROCESSED_BY_JOIN_EXEC) at sql_show.cc:6260
#7  0x00000000006d1419 in JOIN::exec (this=0x7fe21801a100) at sql_select.cc:1874
#8  0x00000000006d3c2b in mysql_select (thd=0x13d4708, rref_pointer_array=0x7fe218014388, tables=0x7fe218014c10, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, 
    proc_param=0x0, select_options=2684636672, result=0x7fe21801a0e0, unit=0x7fe218013d78, select_lex=0x7fe2180141a0) at sql_select.cc:2564
#9  0x00000000006cb63e in handle_select (thd=0x13d4708, lex=0x7fe218013cd8, result=0x7fe21801a0e0, setup_tables_done_option=0) at sql_select.cc:269
#10 0x0000000000668a3a in execute_sqlcom_select (thd=0x13d4708, all_tables=0x7fe218014c10) at sql_parse.cc:5221
#11 0x000000000065fb5e in mysql_execute_command (thd=0x13d4708) at sql_parse.cc:2351
#12 0x000000000084ee70 in sp_instr_stmt::exec_core (this=0x7fe21800d998, thd=0x13d4708, nextp=0x7fe22e25ee94) at sp_head.cc:2980
---Type <return> to continue, or q <return> to quit---
#13 0x000000000084e797 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fe21800d9d8, thd=0x13d4708, nextp=0x7fe22e25ee94, open_tables=false, instr=0x7fe21800d998) at sp_head.cc:2801
#14 0x000000000084ec28 in sp_instr_stmt::execute (this=0x7fe21800d998, thd=0x13d4708, nextp=0x7fe22e25ee94) at sp_head.cc:2923
#15 0x000000000084a923 in sp_head::execute (this=0x7fe21800bc68, thd=0x13d4708) at sp_head.cc:1292
#16 0x000000000084c7a2 in sp_head::execute_procedure (this=0x7fe21800bc68, thd=0x13d4708, args=0x13d6b90) at sp_head.cc:2023
#17 0x000000000066677d in mysql_execute_command (thd=0x13d4708) at sql_parse.cc:4551
#18 0x000000000066ae61 in mysql_parse (thd=0x13d4708, rawbuf=0x7fe218004b88 "CALL p_show_vars(100)", length=21, found_semicolon=0x7fe22e260400) at sql_parse.cc:6138
#19 0x000000000065d32f in dispatch_command (command=COM_QUERY, thd=0x13d4708, packet=0x13d7199 "CALL p_show_vars(100);", packet_length=22) at sql_parse.cc:1288
#20 0x000000000065c13f in do_command (thd=0x13d4708) at sql_parse.cc:893
#21 0x000000000065a331 in handle_one_connection (arg=0x13d4708) at sql_connect.cc:1506
#22 0x00007fe22f9a2f6e in start_thread (arg=0x7fe22e261700) at pthread_create.c:311
#23 0x00007fe22ed739cd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113
[27 Dec 2013 10:51] MySQL Verification Team
Hello Laurynas,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[27 Dec 2013 11:06] MySQL Verification Team
5.6.15

Attachment: 5_6_16.txt (text/plain), 28.35 KiB.

[27 Dec 2013 11:19] MySQL Verification Team
// 5.6.16 too affected
[27 Dec 2013 11:22] MySQL Verification Team
5.5.35 traces

Attachment: 5_5_35.txt (text/plain), 22.70 KiB.

[30 Dec 2013 4:42] Laurynas Biveinis
bug 63203
[21 Mar 2014 22:06] Paul Dubois
Noted in 5.7.5 changelog.

Deadlock could occur if three threads simultaneously performed
INSTALL PLUGIN, SHOW VARIABLES, and mysql_change_user().
[20 May 2014 9:34] Roel Van de Paar
Any possibility for a 5.6 fix? Thank you...
[4 Jun 2014 12:05] Praveenkumar Hulakund
Posted by developer:
 
Patch for this bug will be backported to 5.5+ as part of oracle bug#18903155.
[27 Jun 2014 13:53] Praveenkumar Hulakund
This issue is backported to 5.5 and 5.6 now.
Patch will be available in 5.5.39 and 5.6.20.
[30 Jun 2014 13:38] Paul Dubois
Noted in 5.5.39, 5.6.20 changelogs.
[1 Aug 2014 16:42] Laurynas Biveinis
5.5 $ bzr log -r 4672
------------------------------------------------------------
revno: 4672
committer: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>
branch nick: mysql-5.5
timestamp: Fri 2014-06-27 17:04:08 +0530
message:
  Bug#18903155: BACKPORT BUG-18008907 TO 5.5+ VERSIONS.
  
  Backporting patch committed for bug 18008907 to 5.5
  and 5.6.
[1 Aug 2014 16:43] Laurynas Biveinis
5.5 laurynas$ bzr log -r 4673
------------------------------------------------------------
revno: 4673
committer: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>
branch nick: mysql-5.5
timestamp: Fri 2014-06-27 17:17:04 +0530
message:
  Bug#18903155: BACKPORT BUG-18008907 TO 5.5+ VERSIONS.
  
  Post-push patch. Changing file permission of "scripts/mysqlaccess.conf".