Description:
Read-Locking a table in another thread followed by a long-running select on an InnoDB table will cause set global read_only=1 to hang waiting for COND_refresh.
Without a select, and using a MyISAM table I got random luck in reproducing the results sometimes getting an error:
1192: Can't execute the given command because you have active locked tables or an active transaction
.
The wait happens here (sql_base.cc/close_cached_tables()):
988 if (table->needs_reopen_or_name_lock() && (table->db_stat ||
989 (table->open_placeholder && wait_for_placeholders)))
990 {
991 found=1;
992 DBUG_PRINT("signal", ("Waiting for COND_refresh"));
993 pthread_cond_wait(&COND_refresh,&LOCK_open);
994 break;
995 }
with this backtrace:
#0 0x000000319da0a899 in pthread_cond_wait@@GLIBC_2.3.2 ()
from /lib64/libpthread.so.0
#1 0x00000000005de324 in close_cached_tables (thd=0xdb3cb00,
tables=<value optimized out>, have_lock=false, wait_for_refresh=true,
wait_for_placeholders=true) at sql_base.cc:993
#2 0x00000000005a4ce0 in sys_var_opt_readonly::update (this=0xc3bcc0,
thd=0xdb3cb00, var=0xdb477b8) at set_var.cc:4618
#3 0x00000000005a416f in set_var::update (this=0xdb477b8, thd=0xdb3cb00)
at set_var.cc:3810
#4 0x00000000005b027d in sql_set_variables (thd=0xdb3cb00,
var_list=<value optimized out>) at set_var.cc:3675
#5 0x000000000059ed45 in mysql_execute_command (thd=0xdb3cb00)
at sql_parse.cc:3592
#6 0x00000000005a1bc9 in mysql_parse (thd=0xdb3cb00,
inBuf=0xdb47680 "set global read_only=1", length=22,
found_semicolon=0x4aa4de38) at sql_parse.cc:6148
#7 0x00000000005a25f9 in dispatch_command (command=COM_QUERY, thd=0xdb3cb00,
packet=<value optimized out>, packet_length=<value optimized out>)
at sql_parse.cc:1270
#8 0x00000000005a3290 in do_command (thd=0xdb3cb00) at sql_parse.cc:895
#9 0x0000000000595117 in handle_one_connection (arg=0xdb3cb00)
at sql_connect.cc:1738
#10 0x000000319da06367 in start_thread () from /lib64/libpthread.so.0
How to repeat:
--source include/have_innodb.inc
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection con1;
use test;
create table t1(n int) engine=innodb;
insert into t1 values(10000);
lock tables t1 read;
let $id=`select connection_id()`
send select sleep(n) from t1;
connection con2;
use test;
set global read_only=1;
set global read_only=0;
eval kill $id;
drop table t1;
Suggested fix:
Have not tried to fix it yet. A workaround is to not run SET GLOBAL READ_ONLY=1 and anything that could lock the tables (e.g. mysqldump). This issue is relevant for MMM role switching. MMM executes SET GLOBAL READ_ONLY=1 when switch roles, and luck would have it that a mysqldump job was active at that time, thus I discovered this bug. Fortunately it was in a testing environment.
Description: Read-Locking a table in another thread followed by a long-running select on an InnoDB table will cause set global read_only=1 to hang waiting for COND_refresh. Without a select, and using a MyISAM table I got random luck in reproducing the results sometimes getting an error: 1192: Can't execute the given command because you have active locked tables or an active transaction . The wait happens here (sql_base.cc/close_cached_tables()): 988 if (table->needs_reopen_or_name_lock() && (table->db_stat || 989 (table->open_placeholder && wait_for_placeholders))) 990 { 991 found=1; 992 DBUG_PRINT("signal", ("Waiting for COND_refresh")); 993 pthread_cond_wait(&COND_refresh,&LOCK_open); 994 break; 995 } with this backtrace: #0 0x000000319da0a899 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x00000000005de324 in close_cached_tables (thd=0xdb3cb00, tables=<value optimized out>, have_lock=false, wait_for_refresh=true, wait_for_placeholders=true) at sql_base.cc:993 #2 0x00000000005a4ce0 in sys_var_opt_readonly::update (this=0xc3bcc0, thd=0xdb3cb00, var=0xdb477b8) at set_var.cc:4618 #3 0x00000000005a416f in set_var::update (this=0xdb477b8, thd=0xdb3cb00) at set_var.cc:3810 #4 0x00000000005b027d in sql_set_variables (thd=0xdb3cb00, var_list=<value optimized out>) at set_var.cc:3675 #5 0x000000000059ed45 in mysql_execute_command (thd=0xdb3cb00) at sql_parse.cc:3592 #6 0x00000000005a1bc9 in mysql_parse (thd=0xdb3cb00, inBuf=0xdb47680 "set global read_only=1", length=22, found_semicolon=0x4aa4de38) at sql_parse.cc:6148 #7 0x00000000005a25f9 in dispatch_command (command=COM_QUERY, thd=0xdb3cb00, packet=<value optimized out>, packet_length=<value optimized out>) at sql_parse.cc:1270 #8 0x00000000005a3290 in do_command (thd=0xdb3cb00) at sql_parse.cc:895 #9 0x0000000000595117 in handle_one_connection (arg=0xdb3cb00) at sql_connect.cc:1738 #10 0x000000319da06367 in start_thread () from /lib64/libpthread.so.0 How to repeat: --source include/have_innodb.inc connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; use test; create table t1(n int) engine=innodb; insert into t1 values(10000); lock tables t1 read; let $id=`select connection_id()` send select sleep(n) from t1; connection con2; use test; set global read_only=1; set global read_only=0; eval kill $id; drop table t1; Suggested fix: Have not tried to fix it yet. A workaround is to not run SET GLOBAL READ_ONLY=1 and anything that could lock the tables (e.g. mysqldump). This issue is relevant for MMM role switching. MMM executes SET GLOBAL READ_ONLY=1 when switch roles, and luck would have it that a mysqldump job was active at that time, thus I discovered this bug. Fortunately it was in a testing environment.