1set global sql_mode=""; 2set local sql_mode=""; 3# FTWRL takes two global metadata locks -- a global shared 4# metadata lock and the commit blocker lock. 5# The first lock prevents DDL from taking place. 6# Let's say that all DDL statements that take metadata 7# locks form class #1 -- incompatible with FTWRL because 8# take incompatible MDL table locks. 9# The first global lock doesn't, however, prevent standalone 10# COMMITs (or implicit COMMITs) from taking place, since a 11# COMMIT doesn't take table locks. It doesn't prevent 12# DDL on temporary tables either, since they don't 13# take any table locks either. 14# Most DDL statements do not perform an implicit commit 15# if operate on a temporary table. Examples are CREATE 16# TEMPORARY TABLE and DROP TEMPORARY TABLE. 17# Thus, these DDL statements can go through in presence 18# of FTWRL. This is class #2 -- compatible because 19# do not take incompatible MDL locks and do not issue 20# implicit commit.. 21# (Although these operations do not commit, their effects 22# cannot be rolled back either.) 23# ALTER TABLE, ANALYZE, OPTIMIZE and some others always 24# issue an implicit commit, even if its argument is a 25# temporary table. 26# *Howewer* an implicit commit is a no-op if all engines 27# used since the start of transactiona are non- 28# transactional. Thus, for non-transactional engines, 29# these operations are not blocked by FTWRL. 30# This is class #3 -- compatible because do not take 31# MDL table locks and are non-transactional. 32# On the contrary, for transactional engines, there 33# is always a commit, regardless of whether a table 34# is temporary or not. Thus, for example, ALTER TABLE 35# for a transactional engine will wait for FTWRL, 36# even if the subject table is temporary. 37# Thus ALTER TABLE <temporary> is incompatible 38# with FTWRL. This is class #4 -- incompatible 39# becuase issue implicit COMMIT which is not a no-op. 40# Finally, there are administrative statements (such as 41# RESET SLAVE) that do not take any locks and do not 42# issue COMMIT. 43# This is class #5. 44# The goal of this coverage is to test statements 45# of all classes. 46# @todo: documents the effects of @@autocommit, 47# DML and temporary transactional tables. 48# Use MyISAM engine for the most of the tables 49# used in this test in order to be able to 50# check that DDL statements on temporary tables 51# are compatible with FTRWL. 52drop tables if exists t1_base, t2_base, t3_trans; 53drop tables if exists tm_base, tm_base_temp; 54drop database if exists mysqltest1; 55# We're going to test ALTER DATABASE UPGRADE 56drop database if exists `#mysql50#mysqltest-2`; 57drop procedure if exists p1; 58drop function if exists f1; 59drop view if exists v1; 60drop procedure if exists p2; 61drop function if exists f2_base; 62drop function if exists f2_temp; 63drop event if exists e1; 64drop event if exists e2; 65create table t1_base(i int) engine=myisam; 66create table t2_base(j int) engine=myisam; 67create table t3_trans(i int) engine=innodb; 68create temporary table t1_temp(i int) engine=myisam; 69create temporary table t2_temp(j int) engine=myisam; 70create temporary table t3_temp_trans(i int) engine=innodb; 71create database mysqltest1; 72create database `#mysql50#mysqltest-2`; 73create procedure p1() begin end; 74create function f1() returns int return 0; 75create view v1 as select 1 as i; 76create procedure p2(i int) begin end; 77create function f2_base() returns int 78begin 79insert into t1_base values (1); 80return 0; 81end| 82create function f2_temp() returns int 83begin 84insert into t1_temp values (1); 85return 0; 86end| 87create event e1 on schedule every 1 minute do begin end; 88Warnings: 89Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 90connect con1,localhost,root,,; 91connect con2,localhost,root,,; 92connect con3,localhost,root,,; 93connection default; 94# 95# Test compatibility of FLUSH TABLES WITH READ LOCK 96# with various statements. 97# 98# These tests don't cover some classes of statements: 99# - Replication-related - CHANGE MASTER TO, START/STOP SLAVE and etc 100# (all compatible with FTWRL). 101# - Plugin-related - INSTALL/UNINSTALL (incompatible with FTWRL, 102# require plugin support). 103# 104# 1) ALTER variants. 105# 106# 1.1) ALTER TABLE 107# 108# 1.1.a) For base table should be incompatible with FTWRL. 109# 110Success: Was not able to run 'alter table t1_base add column c1 int' under FTWRL. 111Success: 'alter table t1_base add column c1 int' is blocked by FTWRL active in another connection. 112Success: FTWRL is blocked when 'alter table t1_base add column c1 int' is active in another connection. 113# 114# 1.1.b) For a temporary table should be compatible with FTWRL. 115# 116Success: Was able to run 'alter table t1_temp add column c1 int' under FTWRL. 117Success: Was able to run 'alter table t1_temp add column c1 int' with FTWRL active in another connection. 118Success: Was able to run FTWRL while 'alter table t1_temp add column c1 int' was active in another connection. 119# 120# 1.2) ALTER DATABASE should be incompatible with FTWRL. 121# 122Success: Was not able to run 'alter database mysqltest1 default character set utf8' under FTWRL. 123Success: 'alter database mysqltest1 default character set utf8' is blocked by FTWRL active in another connection. 124Success: FTWRL is blocked when 'alter database mysqltest1 default character set utf8' is active in another connection. 125# 126# 1.3) ALTER DATABASE UPGRADE DATA DIRECTORY NAME should be 127# incompatible with FTWRL. 128# 129Success: Was not able to run 'alter database `#mysql50#mysqltest-2` upgrade data directory name' under FTWRL. 130Success: 'alter database `#mysql50#mysqltest-2` upgrade data directory name' is blocked by FTWRL active in another connection. 131Success: FTWRL is blocked when 'alter database `#mysql50#mysqltest-2` upgrade data directory name' is active in another connection. 132# 133# 1.4) ALTER PROCEDURE should be incompatible with FTWRL. 134# 135Success: Was not able to run 'alter procedure p1 comment 'a'' under FTWRL. 136Success: 'alter procedure p1 comment 'a'' is blocked by FTWRL active in another connection. 137Success: FTWRL is blocked when 'alter procedure p1 comment 'a'' is active in another connection. 138# 139# 1.5) ALTER FUNCTION should be incompatible with FTWRL. 140# 141Success: Was not able to run 'alter function f1 comment 'a'' under FTWRL. 142Success: 'alter function f1 comment 'a'' is blocked by FTWRL active in another connection. 143Success: FTWRL is blocked when 'alter function f1 comment 'a'' is active in another connection. 144# 145# 1.6) ALTER VIEW should be incompatible with FTWRL. 146# 147Success: Was not able to run 'alter view v1 as select 2 as j' under FTWRL. 148Success: 'alter view v1 as select 2 as j' is blocked by FTWRL active in another connection. 149Success: FTWRL is blocked when 'alter view v1 as select 2 as j' is active in another connection. 150# 151# 1.7) ALTER EVENT should be incompatible with FTWRL. 152# 153Success: Was not able to run 'alter event e1 comment 'test'' under FTWRL. 154Success: 'alter event e1 comment 'test'' is blocked by FTWRL active in another connection. 155Success: FTWRL is blocked when 'alter event e1 comment 'test'' is active in another connection. 156# 157# 1.x) The rest of ALTER statements (ALTER TABLESPACE, 158# ALTER LOGFILE GROUP and ALTER SERVER) are too 159# special to be tested here. 160# 161# 162# 2) ANALYZE TABLE statement is compatible with FTWRL. 163# See Bug#43336 ANALYZE and OPTIMIZE do not honour 164# --read-only as they update status tables. 165# 166Success: Was not able to run 'analyze table t1_base' under FTWRL. 167Success: 'analyze table t1_base' is blocked by FTWRL active in another connection. 168# 169# 3) BEGIN, ROLLBACK and COMMIT statements. 170# BEGIN and ROLLBACK are compatible with FTWRL. 171# COMMIT is not. 172# 173# We need a special test for these statements as 174# FTWRL commits a transaction and because COMMIT 175# is handled in a special way. 176flush tables with read lock; 177begin; 178# ROLLBACK is allowed under FTWRL although there 179# no much sense in it. FTWRL commits any previous 180# changes and doesn't allows any DML after it. 181# So such a ROLLBACK is always a no-op. 182rollback; 183# Although COMMIT is incompatible with FTWRL in 184# other senses it is still allowed under FTWRL. 185# This fact relied upon by some versions of 186# innobackup tool. 187# Similarly to ROLLBACK it is a no-op in this situation. 188commit; 189unlock tables; 190# Check that BEGIN/ROLLBACK are not blocked and 191# COMMIT is blocked by active FTWRL in another 192# connection. 193# 194connection con1; 195flush tables with read lock; 196connection default; 197begin; 198connection con1; 199unlock tables; 200connection default; 201# Do some work so ROLLBACK is not a no-op. 202insert into t3_trans values (1); 203connection con1; 204flush tables with read lock; 205connection default; 206rollback; 207connection con1; 208unlock tables; 209connection default; 210begin; 211# Do some work so COMMIT is not a no-op. 212insert into t3_trans values (1); 213connection con1; 214flush tables with read lock; 215connection default; 216# Send: 217commit; 218connection con1; 219# Wait until COMMIT is blocked. 220unlock tables; 221connection default; 222# Reap COMMIT. 223delete from t3_trans; 224# 225# Check that COMMIT blocks FTWRL in another connection. 226begin; 227insert into t3_trans values (1); 228set debug_sync='RESET'; 229set debug_sync='ha_commit_trans_after_acquire_commit_lock SIGNAL parked WAIT_FOR go'; 230commit; 231connection con1; 232set debug_sync='now WAIT_FOR parked'; 233flush tables with read lock; 234connection con2; 235# Wait until FTWRL is blocked. 236set debug_sync='now SIGNAL go'; 237connection default; 238# Reap COMMIT. 239connection con1; 240# Reap FTWRL. 241unlock tables; 242connection default; 243delete from t3_trans; 244set debug_sync= "RESET"; 245# We don't run similar test for BEGIN and ROLLBACK as 246# they release metadata locks in non-standard place. 247# 248# 4) BINLOG statement should be incompatible with FTWRL. 249# 250# 251# Provide format description BINLOG statement first. 252BINLOG ' 253MfmqTA8BAAAAZwAAAGsAAAABAAQANS41LjctbTMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA 254AAAAAAAAAAAAAAAAAAAx+apMEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== 255'; 256# Now test compatibility for BINLOG statement which is 257# equivalent to INSERT INTO t1_base VALUES (1). 258# Skip last part of compatibility testing as this statement 259# releases metadata locks in non-standard place. 260Success: Was not able to run 'BINLOG ' 261MfmqTBMBAAAALgAAAN0AAAAAACgAAAAAAAEABHRlc3QAB3QxX2Jhc2UAAQMAAQ== 262MfmqTBcBAAAAIgAAAP8AAAAAACgAAAAAAAEAAf/+AQAAAA== 263'' under FTWRL. 264Success: 'BINLOG ' 265MfmqTBMBAAAALgAAAN0AAAAAACgAAAAAAAEABHRlc3QAB3QxX2Jhc2UAAQMAAQ== 266MfmqTBcBAAAAIgAAAP8AAAAAACgAAAAAAAEAAf/+AQAAAA== 267'' is blocked by FTWRL active in another connection. 268# 269# 5) CALL statement. This statement uses resources in two 270# ways: through expressions used as parameters and through 271# sub-statements. This test covers only usage through 272# parameters as sub-statements do locking individually. 273# 274# 5.a) In simple cases a parameter expression should be 275# compatible with FTWRL. 276# Skip last part of compatibility testing as this statement 277# releases metadata locks in non-standard place. 278Success: Was able to run 'call p2((select count(*) from t1_base))' under FTWRL. 279Success: Was able to run 'call p2((select count(*) from t1_base))' with FTWRL active in another connection. 280# 281# 5.b) In case when an expression uses function which updates 282# base tables CALL should be incompatible with FTWRL. 283# 284# Skip last part of compatibility testing as this statement 285# releases metadata locks in non-standard place. 286Success: Was not able to run 'call p2(f2_base())' under FTWRL. 287Success: 'call p2(f2_base())' is blocked by FTWRL active in another connection. 288# 289# 5.c) If function used as argument updates temporary tables 290# CALL statement should be compatible with FTWRL. 291# 292# Skip last part of compatibility testing as this statement 293# releases metadata locks in non-standard place. 294Success: Was able to run 'call p2(f2_temp())' under FTWRL. 295Success: Was able to run 'call p2(f2_temp())' with FTWRL active in another connection. 296# 297# 6) CHECK TABLE statement is compatible with FTWRL. 298# 299Success: Was able to run 'check table t1_base' under FTWRL. 300Success: Was able to run 'check table t1_base' with FTWRL active in another connection. 301Success: Was able to run FTWRL while 'check table t1_base' was active in another connection. 302# 303# 7) CHECKSUM TABLE statement is compatible with FTWRL. 304# 305Success: Was able to run 'checksum table t1_base' under FTWRL. 306Success: Was able to run 'checksum table t1_base' with FTWRL active in another connection. 307Success: Was able to run FTWRL while 'checksum table t1_base' was active in another connection. 308# 309# 8) CREATE variants. 310# 311# 8.1) CREATE TABLE statement. 312# 313# 8.1.a) CREATE TABLE is incompatible with FTWRL when 314# base table is created. 315Success: Was not able to run 'create table t3_base(i int)' under FTWRL. 316Success: 'create table t3_base(i int)' is blocked by FTWRL active in another connection. 317Success: FTWRL is blocked when 'create table t3_base(i int)' is active in another connection. 318# 8.1.b) CREATE TABLE is compatible with FTWRL when 319# temporary table is created. 320Success: Was able to run 'create temporary table t3_temp(i int)' under FTWRL. 321Success: Was able to run 'create temporary table t3_temp(i int)' with FTWRL active in another connection. 322Success: Was able to run FTWRL while 'create temporary table t3_temp(i int)' was active in another connection. 323# 8.1.c) CREATE TABLE LIKE is incompatible with FTWRL when 324# base table is created. 325Success: Was not able to run 'create table t3_base like t1_temp' under FTWRL. 326Success: 'create table t3_base like t1_temp' is blocked by FTWRL active in another connection. 327Success: FTWRL is blocked when 'create table t3_base like t1_temp' is active in another connection. 328# 8.1.d) CREATE TABLE LIKE is compatible with FTWRL when 329# temporary table is created. 330Success: Was able to run 'create temporary table t3_temp like t1_base' under FTWRL. 331Success: Was able to run 'create temporary table t3_temp like t1_base' with FTWRL active in another connection. 332Success: Was able to run FTWRL while 'create temporary table t3_temp like t1_base' was active in another connection. 333# 8.1.e) CREATE TABLE SELECT is incompatible with FTWRL when 334# base table is created. 335Success: Was not able to run 'create table t3_base select 1 as i' under FTWRL. 336Success: 'create table t3_base select 1 as i' is blocked by FTWRL active in another connection. 337Success: FTWRL is blocked when 'create table t3_base select 1 as i' is active in another connection. 338# 8.1.f) CREATE TABLE SELECT is compatible with FTWRL when 339# temporary table is created. 340Success: Was able to run 'create temporary table t3_temp select 1 as i' under FTWRL. 341Success: Was able to run 'create temporary table t3_temp select 1 as i' with FTWRL active in another connection. 342Success: Was able to run FTWRL while 'create temporary table t3_temp select 1 as i' was active in another connection. 343# 8.2) CREATE INDEX statement. 344# 345# 8.2.a) CREATE INDEX is incompatible with FTWRL when 346# applied to base table. 347Success: Was not able to run 'create index i on t1_base (i)' under FTWRL. 348Success: 'create index i on t1_base (i)' is blocked by FTWRL active in another connection. 349Success: FTWRL is blocked when 'create index i on t1_base (i)' is active in another connection. 350# 8.2.b) CREATE INDEX is compatible with FTWRL when 351# applied to temporary table. 352Success: Was able to run 'create index i on t1_temp (i)' under FTWRL. 353Success: Was able to run 'create index i on t1_temp (i)' with FTWRL active in another connection. 354Success: Was able to run FTWRL while 'create index i on t1_temp (i)' was active in another connection. 355# 356# 8.3) CREATE DATABASE is incompatible with FTWRL. 357# 358Success: Was not able to run 'create database mysqltest2' under FTWRL. 359Success: 'create database mysqltest2' is blocked by FTWRL active in another connection. 360Success: FTWRL is blocked when 'create database mysqltest2' is active in another connection. 361# 362# 8.4) CREATE VIEW is incompatible with FTWRL. 363# 364Success: Was not able to run 'create view v2 as select 1 as j' under FTWRL. 365Success: 'create view v2 as select 1 as j' is blocked by FTWRL active in another connection. 366Success: FTWRL is blocked when 'create view v2 as select 1 as j' is active in another connection. 367# 368# 8.5) CREATE TRIGGER is incompatible with FTWRL. 369# 370Success: Was not able to run 'create trigger t1_bi before insert on t1_base for each row begin end' under FTWRL. 371Success: 'create trigger t1_bi before insert on t1_base for each row begin end' is blocked by FTWRL active in another connection. 372Success: FTWRL is blocked when 'create trigger t1_bi before insert on t1_base for each row begin end' is active in another connection. 373# 374# 8.6) CREATE FUNCTION is incompatible with FTWRL. 375# 376Success: Was not able to run 'create function f2() returns int return 0' under FTWRL. 377Success: 'create function f2() returns int return 0' is blocked by FTWRL active in another connection. 378Success: FTWRL is blocked when 'create function f2() returns int return 0' is active in another connection. 379# 380# 8.7) CREATE PROCEDURE is incompatible with FTWRL. 381# 382Success: Was not able to run 'create procedure p3() begin end' under FTWRL. 383Success: 'create procedure p3() begin end' is blocked by FTWRL active in another connection. 384Success: FTWRL is blocked when 'create procedure p3() begin end' is active in another connection. 385# 386# 8.8) CREATE EVENT should be incompatible with FTWRL. 387# 388Success: Was not able to run 'create event e2 on schedule every 1 minute do begin end' under FTWRL. 389Success: 'create event e2 on schedule every 1 minute do begin end' is blocked by FTWRL active in another connection. 390Success: FTWRL is blocked when 'create event e2 on schedule every 1 minute do begin end' is active in another connection. 391# 392# 8.9) CREATE USER should be incompatible with FTWRL. 393# 394Success: Was not able to run 'create user mysqltest_u1' under FTWRL. 395Success: 'create user mysqltest_u1' is blocked by FTWRL active in another connection. 396Success: FTWRL is blocked when 'create user mysqltest_u1' is active in another connection. 397# 398# 8.x) The rest of CREATE variants (CREATE LOGFILE GROUP, 399# CREATE TABLESPACE and CREATE SERVER) are too special 400# to test here. 401# 402# 403# 9) PREPARE, EXECUTE and DEALLOCATE PREPARE statements. 404# 405# 9.1) PREPARE statement is compatible with FTWRL as it 406# doesn't change any data. 407# 408# 9.1.a) Prepare of simple INSERT statement. 409# 410# Skip last part of compatibility testing as this statement 411# releases metadata locks in non-standard place. 412Success: Was able to run 'prepare stmt1 from 'insert into t1_base values (1)'' under FTWRL. 413Success: Was able to run 'prepare stmt1 from 'insert into t1_base values (1)'' with FTWRL active in another connection. 414# 415# 9.1.b) Prepare of multi-UPDATE. At some point such statements 416# tried to acquire thr_lock.c locks during prepare phase. 417# This no longer happens and thus it is compatible with 418# FTWRL. 419# Skip last part of compatibility testing as this statement 420# releases metadata locks in non-standard place. 421Success: Was able to run 'prepare stmt1 from 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j'' under FTWRL. 422Success: Was able to run 'prepare stmt1 from 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j'' with FTWRL active in another connection. 423# 424# 9.1.c) Prepare of multi-DELETE. Again PREPARE of such 425# statement should be compatible with FTWRL. 426# Skip last part of compatibility testing as this statement 427# releases metadata locks in non-standard place. 428Success: Was able to run 'prepare stmt1 from 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j'' under FTWRL. 429Success: Was able to run 'prepare stmt1 from 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j'' with FTWRL active in another connection. 430# 431# 9.2) Compatibility of EXECUTE statement depends on statement 432# to be executed. 433# 434# 9.2.a) EXECUTE for statement which is itself compatible with 435# FTWRL should be compatible. 436prepare stmt1 from 'select * from t1_base'; 437Success: Was able to run 'execute stmt1' under FTWRL. 438Success: Was able to run 'execute stmt1' with FTWRL active in another connection. 439Success: Was able to run FTWRL while 'execute stmt1' was active in another connection. 440deallocate prepare stmt1; 441call mtr.add_suppression("Slave SQL.*Can.t execute the query because you have a conflicting read lock., error.* 1223"); 442# 443# 9.2.b) EXECUTE for statement which is incompatible with FTWRL 444# should be also incompatible. 445# 446# Check that EXECUTE is not allowed under FTWRL. 447prepare stmt1 from 'insert into t1_base values (1)'; 448flush tables with read lock; 449execute stmt1; 450ERROR HY000: Can't execute the query because you have a conflicting read lock 451unlock tables; 452# Check that active FTWRL in another connection 453# blocks EXECUTE which changes data. 454# 455connection con1; 456flush tables with read lock; 457connection default; 458execute stmt1 ; 459connection con1; 460# Check that EXECUTE is blocked. 461unlock tables; 462connection default; 463# Reap EXECUTE. 464set debug_sync='RESET'; 465set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 466execute stmt1; ; 467connection con1; 468set debug_sync='now WAIT_FOR parked'; 469flush tables with read lock; 470connection con2; 471# Wait until FTWRL is blocked. 472set debug_sync='now SIGNAL go'; 473connection default; 474# Reap EXECUTE. 475connection con1; 476# Reap FTWRL. 477unlock tables; 478connection default; 479set debug_sync= "RESET"; 480delete from t1_base; 481deallocate prepare stmt1; 482# 483# 9.3) DEALLOCATE PREPARE is compatible with FTWRL. 484# 485prepare stmt1 from 'insert into t1_base values (1)'; 486Success: Was able to run 'deallocate prepare stmt1' under FTWRL. 487Success: Was able to run 'deallocate prepare stmt1' with FTWRL active in another connection. 488Success: Was able to run FTWRL while 'deallocate prepare stmt1' was active in another connection. 489deallocate prepare stmt1; 490# 491# 10) DELETE variations. 492# 493# 10.1) Simple DELETE. 494# 495# 10.1.a) Simple DELETE on base table is incompatible with FTWRL. 496Success: Was not able to run 'delete from t1_base' under FTWRL. 497Success: 'delete from t1_base' is blocked by FTWRL active in another connection. 498Success: FTWRL is blocked when 'delete from t1_base' is active in another connection. 499# 500# 10.1.b) Simple DELETE on temporary table is compatible with FTWRL. 501Success: Was able to run 'delete from t1_temp' under FTWRL. 502Success: Was able to run 'delete from t1_temp' with FTWRL active in another connection. 503Success: Was able to run FTWRL while 'delete from t1_temp' was active in another connection. 504# 505# 10.2) Multi DELETE. 506# 507# 10.2.a) Multi DELETE on base tables is incompatible with FTWRL. 508Success: Was not able to run 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j' under FTWRL. 509Success: 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j' is blocked by FTWRL active in another connection. 510Success: FTWRL is blocked when 'delete t1_base from t1_base, t2_base where t1_base.i = t2_base.j' is active in another connection. 511# 512# 10.2.b) Multi DELETE on temporary tables is compatible with FTWRL. 513Success: Was able to run 'delete t1_temp from t1_temp, t2_temp where t1_temp.i = t2_temp.j' under FTWRL. 514Success: Was able to run 'delete t1_temp from t1_temp, t2_temp where t1_temp.i = t2_temp.j' with FTWRL active in another connection. 515Success: Was able to run FTWRL while 'delete t1_temp from t1_temp, t2_temp where t1_temp.i = t2_temp.j' was active in another connection. 516# 517# 11) DESCRIBE should be compatible with FTWRL. 518# 519Success: Was able to run 'describe t1_base' under FTWRL. 520Success: Was able to run 'describe t1_base' with FTWRL active in another connection. 521Success: Was able to run FTWRL while 'describe t1_base' was active in another connection. 522# 523# 12) Compatibility of DO statement with FTWRL depends on its 524# expression. 525# 526# 12.a) DO with expression which does not change base table 527# should be compatible with FTWRL. 528Success: Was able to run 'do (select count(*) from t1_base)' under FTWRL. 529Success: Was able to run 'do (select count(*) from t1_base)' with FTWRL active in another connection. 530Success: Was able to run FTWRL while 'do (select count(*) from t1_base)' was active in another connection. 531# 532# 12.b) DO which calls SF updating base table should be 533# incompatible with FTWRL. 534Success: Was not able to run 'do f2_base()' under FTWRL. 535Success: 'do f2_base()' is blocked by FTWRL active in another connection. 536Success: FTWRL is blocked when 'do f2_base()' is active in another connection. 537# 538# 12.c) DO which calls SF updating temporary table should be 539# compatible with FTWRL. 540Success: Was able to run 'do f2_temp()' under FTWRL. 541Success: Was able to run 'do f2_temp()' with FTWRL active in another connection. 542Success: Was able to run FTWRL while 'do f2_temp()' was active in another connection. 543# 544# 13) DROP variants. 545# 546# 13.1) DROP TABLES. 547# 548# 13.1.a) DROP TABLES which affects base tables is incompatible 549# with FTWRL. 550Success: Was not able to run 'drop table t2_base' under FTWRL. 551Success: 'drop table t2_base' is blocked by FTWRL active in another connection. 552Success: FTWRL is blocked when 'drop table t2_base' is active in another connection. 553# 13.1.b) DROP TABLES which affects only temporary tables 554# is compatible with FTWRL. 555Success: Was able to run 'drop table t2_temp' under FTWRL. 556Success: Was able to run 'drop table t2_temp' with FTWRL active in another connection. 557Success: Was able to run FTWRL while 'drop table t2_temp' was active in another connection. 558# 559# 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL. 560Success: Was able to run 'drop temporary table t2_temp' under FTWRL. 561Success: Was able to run 'drop temporary table t2_temp' with FTWRL active in another connection. 562Success: Was able to run FTWRL while 'drop temporary table t2_temp' was active in another connection. 563# 564# 13.2) DROP INDEX. 565# 566# 13.2.a) DROP INDEX on a base table is incompatible with FTWRL. 567create index i on t1_base (i); 568Success: Was not able to run 'drop index i on t1_base' under FTWRL. 569Success: 'drop index i on t1_base' is blocked by FTWRL active in another connection. 570Success: FTWRL is blocked when 'drop index i on t1_base' is active in another connection. 571drop index i on t1_base; 572# 573# 13.2.b) DROP INDEX on a temporary table is compatible with FTWRL. 574create index i on t1_temp (i); 575Success: Was able to run 'drop index i on t1_temp' under FTWRL. 576Success: Was able to run 'drop index i on t1_temp' with FTWRL active in another connection. 577Success: Was able to run FTWRL while 'drop index i on t1_temp' was active in another connection. 578drop index i on t1_temp; 579# 580# 13.3) DROP DATABASE is incompatible with FTWRL 581# 582Success: Was not able to run 'drop database mysqltest1' under FTWRL. 583Success: 'drop database mysqltest1' is blocked by FTWRL active in another connection. 584Success: FTWRL is blocked when 'drop database mysqltest1' is active in another connection. 585# 586# 13.4) DROP FUNCTION is incompatible with FTWRL. 587# 588Success: Was not able to run 'drop function f1' under FTWRL. 589Success: 'drop function f1' is blocked by FTWRL active in another connection. 590Success: FTWRL is blocked when 'drop function f1' is active in another connection. 591# 592# 13.5) DROP PROCEDURE is incompatible with FTWRL. 593# 594Success: Was not able to run 'drop procedure p1' under FTWRL. 595Success: 'drop procedure p1' is blocked by FTWRL active in another connection. 596Success: FTWRL is blocked when 'drop procedure p1' is active in another connection. 597# 598# 13.6) DROP USER should be incompatible with FTWRL. 599# 600create user mysqltest_u1; 601Success: Was not able to run 'drop user mysqltest_u1' under FTWRL. 602Success: 'drop user mysqltest_u1' is blocked by FTWRL active in another connection. 603Success: FTWRL is blocked when 'drop user mysqltest_u1' is active in another connection. 604drop user mysqltest_u1; 605# 606# 13.7) DROP VIEW should be incompatible with FTWRL. 607# 608Success: Was not able to run 'drop view v1' under FTWRL. 609Success: 'drop view v1' is blocked by FTWRL active in another connection. 610Success: FTWRL is blocked when 'drop view v1' is active in another connection. 611# 612# 13.8) DROP EVENT should be incompatible with FTWRL. 613# 614Success: Was not able to run 'drop event e1' under FTWRL. 615Success: 'drop event e1' is blocked by FTWRL active in another connection. 616Success: FTWRL is blocked when 'drop event e1' is active in another connection. 617# 618# 13.9) DROP TRIGGER is incompatible with FTWRL. 619# 620create trigger t1_bi before insert on t1_base for each row begin end; 621Success: Was not able to run 'drop trigger t1_bi' under FTWRL. 622Success: 'drop trigger t1_bi' is blocked by FTWRL active in another connection. 623Success: FTWRL is blocked when 'drop trigger t1_bi' is active in another connection. 624drop trigger t1_bi; 625# 626# 13.x) The rest of DROP variants (DROP TABLESPACE, DROP LOGFILE 627# GROUP and DROP SERVER) are too special to test here. 628# 629# 630# 14) FLUSH variants. 631# 632# Test compatibility of _some_ important FLUSH variants with FTWRL. 633# 634# 14.1) FLUSH TABLES WITH READ LOCK is compatible with itself. 635# 636# Check that FTWRL statements can be run while FTWRL 637# is active in another connection. 638# 639flush tables with read lock; 640# The second FTWRL in a row is allowed at the moment. 641# It does not make much sense as it does only flush. 642flush tables with read lock; 643unlock tables; 644connection con1; 645flush tables with read lock; 646connection default; 647flush tables with read lock; 648unlock tables; 649connection con1; 650unlock tables; 651connection default; 652# 653# 14.2) FLUSH TABLES <list> WITH READ LOCK is not blocked by 654# active FTWRL. But since the latter keeps tables open 655# FTWRL is blocked by FLUSH TABLES <list> WITH READ LOCK. 656# Fixed by MDEV-5336 657flush tables with read lock; 658# FT <list> WRL is allowed under FTWRL at the moment. 659# It does not make much sense though. 660flush tables t1_base, t2_base with read lock; 661unlock tables; 662connection con1; 663flush tables with read lock; 664connection default; 665flush tables t1_base, t2_base with read lock; 666unlock tables; 667connection con1; 668unlock tables; 669connection default; 670flush tables t1_base, t2_base with read lock; 671connection con1; 672flush tables with read lock; 673connection default; 674unlock tables; 675connection con1; 676unlock tables; 677connection default; 678# 679# 14.3) FLUSH TABLES is compatible with FTWRL. 680Success: Was able to run 'flush tables' under FTWRL. 681Success: Was able to run 'flush tables' with FTWRL active in another connection. 682Success: Was able to run FTWRL while 'flush tables' was active in another connection. 683# 684# 14.4) FLUSH TABLES <list> is compatible with FTWRL. 685Success: Was able to run 'flush table t1_base, t2_base' under FTWRL. 686Success: Was able to run 'flush table t1_base, t2_base' with FTWRL active in another connection. 687Success: Was able to run FTWRL while 'flush table t1_base, t2_base' was active in another connection. 688# 689# 14.5) FLUSH PRIVILEGES is compatible with FTWRL. 690Success: Was able to run 'flush privileges' under FTWRL. 691Success: Was able to run 'flush privileges' with FTWRL active in another connection. 692Success: Was able to run FTWRL while 'flush privileges' was active in another connection. 693# 694# 15) GRANT statement should be incompatible with FTWRL. 695# 696Success: Was not able to run 'grant all privileges on t1_base to mysqltest_u1' under FTWRL. 697Success: 'grant all privileges on t1_base to mysqltest_u1' is blocked by FTWRL active in another connection. 698Success: FTWRL is blocked when 'grant all privileges on t1_base to mysqltest_u1' is active in another connection. 699drop user mysqltest_u1; 700# 701# 16) All HANDLER variants are half-compatible with FTWRL. 702# I.e. they are not blocked by active FTWRL. But since open 703# HANDLER means open table instance FTWRL is blocked while 704# HANDLER is not closed. 705# 706# Check that HANDLER statements succeed under FTWRL. 707flush tables with read lock; 708handler t1_base open; 709handler t1_base read first; 710i 711handler t1_base close; 712unlock tables; 713# Check that HANDLER statements can be run while FTWRL 714# is active in another connection. 715# 716connection con1; 717flush tables with read lock; 718connection default; 719handler t1_base open; 720handler t1_base read first; 721i 722handler t1_base close; 723connection con1; 724unlock tables; 725connection default; 726# 727# 17) HELP statement is compatible with FTWRL. 728# 729Success: Was able to run 'help no_such_topic' under FTWRL. 730Success: Was able to run 'help no_such_topic' with FTWRL active in another connection. 731Success: Was able to run FTWRL while 'help no_such_topic' was active in another connection. 732# 733# 18) INSERT statement. 734# 735# 18.a) Ordinary INSERT into base table is incompatible with FTWRL. 736Success: Was not able to run 'insert into t1_base values (1)' under FTWRL. 737Success: 'insert into t1_base values (1)' is blocked by FTWRL active in another connection. 738Success: FTWRL is blocked when 'insert into t1_base values (1)' is active in another connection. 739# 740# 18.b) Ordinary INSERT into temp table is compatible with FTWRL. 741Success: Was able to run 'insert into t1_temp values (1)' under FTWRL. 742Success: Was able to run 'insert into t1_temp values (1)' with FTWRL active in another connection. 743Success: Was able to run FTWRL while 'insert into t1_temp values (1)' was active in another connection. 744# 745# 18.c) INSERT DELAYED is incompatible with FTWRL. 746Success: Was not able to run 'insert delayed into t1_base values (1)' under FTWRL. 747Success: 'insert delayed into t1_base values (1)' is blocked by FTWRL active in another connection. 748Success: FTWRL is blocked when 'insert delayed into t1_base values (1)' is active in another connection. 749delete from t1_base; 750# 751# 18.d) INSERT SELECT into base table is incompatible with FTWRL. 752Success: Was not able to run 'insert into t1_base select * from t1_temp' under FTWRL. 753Success: 'insert into t1_base select * from t1_temp' is blocked by FTWRL active in another connection. 754Success: FTWRL is blocked when 'insert into t1_base select * from t1_temp' is active in another connection. 755# 756# 18.e) INSERT SELECT into temp table is compatible with FTWRL. 757Success: Was able to run 'insert into t1_temp select * from t1_base' under FTWRL. 758Success: Was able to run 'insert into t1_temp select * from t1_base' with FTWRL active in another connection. 759Success: Was able to run FTWRL while 'insert into t1_temp select * from t1_base' was active in another connection. 760# 761# 19) KILL statement is compatible with FTWRL. 762# 763# Check that KILL can be run under FTWRL. 764flush tables with read lock; 765set @id:= connection_id(); 766kill query @id; 767ERROR 70100: Query execution was interrupted 768unlock tables; 769# Check that KILL statements can be run while FTWRL 770# is active in another connection. 771# 772connection con1; 773flush tables with read lock; 774connection default; 775kill query @id; 776ERROR 70100: Query execution was interrupted 777connection con1; 778unlock tables; 779connection default; 780# Finally check that KILL doesn't block FTWRL 781set debug_sync='RESET'; 782set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 783kill query @id; 784connection con1; 785set debug_sync='now WAIT_FOR parked'; 786flush tables with read lock; 787unlock tables; 788set debug_sync='now SIGNAL go'; 789connection default; 790# Reap KILL. 791ERROR 70100: Query execution was interrupted 792set debug_sync='RESET'; 793# 794# 20) LOAD DATA statement. 795# 796# 20.a) LOAD DATA into base table is incompatible with FTWRL. 797Success: Was not able to run 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_base (@dummy, i)' under FTWRL. 798Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_base (@dummy, i)' is blocked by FTWRL active in another connection. 799Success: FTWRL is blocked when 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_base (@dummy, i)' is active in another connection. 800# 801# 20.b) LOAD DATA into temporary table is compatible with FTWRL. 802Success: Was able to run 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_temp (@dummy, i)' under FTWRL. 803Success: Was able to run 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_temp (@dummy, i)' with FTWRL active in another connection. 804Success: Was able to run FTWRL while 'load data infile '../../std_data/rpl_loaddata.dat' into table t1_temp (@dummy, i)' was active in another connection. 805# 806# 21) LOCK/UNLOCK TABLES statements. 807# 808# LOCK TABLES statement always (almost) blocks FTWRL as it 809# keeps tables open until UNLOCK TABLES. 810# Active FTWRL on the other hand blocks only those 811# LOCK TABLES which allow updating of base tables. 812# 813# 21.a) LOCK TABLES READ is allowed under FTWRL and 814# is not blocked by active FTWRL. 815flush tables with read lock; 816lock tables t1_base read; 817unlock tables; 818# 819connection con1; 820flush tables with read lock; 821connection default; 822lock tables t1_base read; 823unlock tables; 824connection con1; 825unlock tables; 826connection default; 827# 828# 21.b) LOCK TABLES WRITE on a base table is disallowed 829# under FTWRL and should be blocked by active FTWRL. 830flush tables with read lock; 831lock tables t1_base write; 832ERROR HY000: Can't execute the query because you have a conflicting read lock 833unlock tables; 834# 835connection con1; 836flush tables with read lock; 837connection default; 838lock tables t1_base write ; 839connection con1; 840# Check that LOCK TABLES WRITE is blocked. 841unlock tables; 842connection default; 843# Reap LOCK TABLES WRITE 844unlock tables; 845# 846# 21.c) LOCK TABLES WRITE on temporary table doesn't 847# make much sense but is allowed under FTWRL 848# and should not be blocked by active FTWRL. 849flush tables with read lock; 850lock tables t1_temp write; 851unlock tables; 852# 853connection con1; 854flush tables with read lock; 855connection default; 856lock tables t1_temp write; 857unlock tables; 858connection con1; 859unlock tables; 860connection default; 861# 862# 22) OPTIMIZE TABLE statement. 863# 864# 22.a) OPTIMIZE TABLE of base table is incompatible with FTWRL. 865flush tables with read lock; 866# OPTIMIZE statement returns errors as part of result-set. 867optimize table t1_base; 868Table Op Msg_type Msg_text 869test.t1_base optimize Error Can't execute the query because you have a conflicting read lock 870test.t1_base optimize error Corrupt 871unlock tables; 872# 873connection con1; 874flush tables with read lock; 875connection default; 876optimize table t1_base; 877connection con1; 878# Check that OPTIMIZE TABLE is blocked. 879unlock tables; 880connection default; 881# Reap OPTIMIZE TABLE 882Table Op Msg_type Msg_text 883test.t1_base optimize status OK 884# We don't check that active OPTIMIZE TABLE blocks 885# FTWRL as this one of statements releasing metadata 886# locks in non-standard place. 887# 888# 22.b) OPTIMIZE TABLE of temporary table is compatible with FTWRL. 889# Skip last part of compatibility testing as this statement 890# releases metadata locks in non-standard place. 891Success: Was able to run 'optimize table t1_temp' under FTWRL. 892Success: Was able to run 'optimize table t1_temp' with FTWRL active in another connection. 893# 894# 23) CACHE statement is compatible with FTWRL. 895# 896# Skip last part of compatibility testing as this statement 897# releases metadata locks in non-standard place. 898Success: Was able to run 'cache index t1_base in default' under FTWRL. 899Success: Was able to run 'cache index t1_base in default' with FTWRL active in another connection. 900# 901# 24) LOAD INDEX statement is compatible with FTWRL. 902# 903# Skip last part of compatibility testing as this statement 904# releases metadata locks in non-standard place. 905Success: Was able to run 'load index into cache t1_base' under FTWRL. 906Success: Was able to run 'load index into cache t1_base' with FTWRL active in another connection. 907# 908# 25) SAVEPOINT/RELEASE SAVEPOINT/ROLLBACK TO SAVEPOINT are 909# compatible with FTWRL. 910# 911# Since manipulations on savepoint have to be done 912# inside transaction and FTWRL commits transaction we 913# need a special test for these statements. 914flush tables with read lock; 915begin; 916savepoint sv1; 917rollback to savepoint sv1; 918release savepoint sv1; 919unlock tables; 920commit; 921# Check that these statements are not blocked by 922# active FTWRL in another connection. 923# 924connection con1; 925flush tables with read lock; 926connection default; 927begin; 928connection con1; 929unlock tables; 930connection default; 931# Do some changes to avoid SAVEPOINT and friends 932# being almost no-ops. 933insert into t3_trans values (1); 934connection con1; 935flush tables with read lock; 936connection default; 937savepoint sv1; 938connection con1; 939unlock tables; 940connection default; 941insert into t3_trans values (2); 942connection con1; 943flush tables with read lock; 944connection default; 945rollback to savepoint sv1; 946release savepoint sv1; 947connection con1; 948unlock tables; 949connection default; 950rollback; 951# Check that these statements don't block FTWRL in 952# another connection. 953begin; 954# Do some changes to avoid SAVEPOINT and friends 955# being almost no-ops. 956insert into t3_trans values (1); 957set debug_sync='RESET'; 958set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 959savepoint sv1; 960connection con1; 961set debug_sync='now WAIT_FOR parked'; 962flush tables with read lock; 963unlock tables; 964set debug_sync='now SIGNAL go'; 965connection default; 966# Reap SAVEPOINT 967insert into t3_trans values (2); 968set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 969rollback to savepoint sv1; 970connection con1; 971set debug_sync='now WAIT_FOR parked'; 972flush tables with read lock; 973unlock tables; 974set debug_sync='now SIGNAL go'; 975connection default; 976# Reap ROLLBACK TO SAVEPOINT 977set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 978release savepoint sv1; 979connection con1; 980set debug_sync='now WAIT_FOR parked'; 981flush tables with read lock; 982unlock tables; 983set debug_sync='now SIGNAL go'; 984connection default; 985# Reap RELEASE SAVEPOINT 986rollback; 987set debug_sync= "RESET"; 988# 989# 26) RENAME variants. 990# 991# 26.1) RENAME TABLES is incompatible with FTWRL. 992Success: Was not able to run 'rename table t1_base to t3_base' under FTWRL. 993Success: 'rename table t1_base to t3_base' is blocked by FTWRL active in another connection. 994Success: FTWRL is blocked when 'rename table t1_base to t3_base' is active in another connection. 995# 996# 26.2) RENAME USER is incompatible with FTWRL. 997create user mysqltest_u1; 998Success: Was not able to run 'rename user mysqltest_u1 to mysqltest_u2' under FTWRL. 999Success: 'rename user mysqltest_u1 to mysqltest_u2' is blocked by FTWRL active in another connection. 1000Success: FTWRL is blocked when 'rename user mysqltest_u1 to mysqltest_u2' is active in another connection. 1001drop user mysqltest_u1; 1002# 1003# 27) REPAIR TABLE statement. 1004# 1005# 27.a) REPAIR TABLE of base table is incompatible with FTWRL. 1006flush tables with read lock; 1007# REPAIR statement returns errors as part of result-set. 1008repair table t1_base; 1009Table Op Msg_type Msg_text 1010test.t1_base repair Error Can't execute the query because you have a conflicting read lock 1011test.t1_base repair error Corrupt 1012unlock tables; 1013# 1014connection con1; 1015flush tables with read lock; 1016connection default; 1017repair table t1_base; 1018connection con1; 1019# Check that REPAIR TABLE is blocked. 1020unlock tables; 1021connection default; 1022# Reap REPAIR TABLE 1023Table Op Msg_type Msg_text 1024test.t1_base repair status OK 1025# We don't check that active REPAIR TABLE blocks 1026# FTWRL as this one of statements releasing metadata 1027# locks in non-standard place. 1028# 1029# 27.b) REPAIR TABLE of temporary table is compatible with FTWRL. 1030# Skip last part of compatibility testing as this statement 1031# releases metadata locks in non-standard place. 1032Success: Was able to run 'repair table t1_temp' under FTWRL. 1033Success: Was able to run 'repair table t1_temp' with FTWRL active in another connection. 1034# 1035# 28) REPLACE statement. 1036# 1037# 28.a) Ordinary REPLACE into base table is incompatible with FTWRL. 1038Success: Was not able to run 'replace into t1_base values (1)' under FTWRL. 1039Success: 'replace into t1_base values (1)' is blocked by FTWRL active in another connection. 1040Success: FTWRL is blocked when 'replace into t1_base values (1)' is active in another connection. 1041# 1042# 28.b) Ordinary REPLACE into temp table is compatible with FTWRL. 1043Success: Was able to run 'replace into t1_temp values (1)' under FTWRL. 1044Success: Was able to run 'replace into t1_temp values (1)' with FTWRL active in another connection. 1045Success: Was able to run FTWRL while 'replace into t1_temp values (1)' was active in another connection. 1046# 1047# 28.c) REPLACE SELECT into base table is incompatible with FTWRL. 1048Success: Was not able to run 'replace into t1_base select * from t1_temp' under FTWRL. 1049Success: 'replace into t1_base select * from t1_temp' is blocked by FTWRL active in another connection. 1050Success: FTWRL is blocked when 'replace into t1_base select * from t1_temp' is active in another connection. 1051# 1052# 28.d) REPLACE SELECT into temp table is compatible with FTWRL. 1053Success: Was able to run 'replace into t1_temp select * from t1_base' under FTWRL. 1054Success: Was able to run 'replace into t1_temp select * from t1_base' with FTWRL active in another connection. 1055Success: Was able to run FTWRL while 'replace into t1_temp select * from t1_base' was active in another connection. 1056# 1057# 29) REVOKE variants. 1058# 1059# 29.1) REVOKE privileges is incompatible with FTWRL. 1060grant all privileges on t1_base to mysqltest_u1; 1061Success: Was not able to run 'revoke all privileges on t1_base from mysqltest_u1' under FTWRL. 1062Success: 'revoke all privileges on t1_base from mysqltest_u1' is blocked by FTWRL active in another connection. 1063Success: FTWRL is blocked when 'revoke all privileges on t1_base from mysqltest_u1' is active in another connection. 1064# 1065# 29.2) REVOKE ALL PRIVILEGES, GRANT OPTION is incompatible with FTWRL. 1066Success: Was not able to run 'revoke all privileges, grant option from mysqltest_u1' under FTWRL. 1067Success: 'revoke all privileges, grant option from mysqltest_u1' is blocked by FTWRL active in another connection. 1068Success: FTWRL is blocked when 'revoke all privileges, grant option from mysqltest_u1' is active in another connection. 1069drop user mysqltest_u1; 1070# 1071# 30) Compatibility of SELECT statement with FTWRL depends on 1072# locking mode used and on functions being invoked by it. 1073# 1074# 30.a) Simple SELECT which does not change tables should be 1075# compatible with FTWRL. 1076Success: Was able to run 'select count(*) from t1_base' under FTWRL. 1077Success: Was able to run 'select count(*) from t1_base' with FTWRL active in another connection. 1078Success: Was able to run FTWRL while 'select count(*) from t1_base' was active in another connection. 1079# 30.b) SELECT ... FOR UPDATE is incompatible with FTWRL. 1080Success: Was not able to run 'select count(*) from t1_base for update' under FTWRL. 1081Success: 'select count(*) from t1_base for update' is blocked by FTWRL active in another connection. 1082Success: FTWRL is blocked when 'select count(*) from t1_base for update' is active in another connection. 1083# 30.c) SELECT ... LOCK IN SHARE MODE is compatible with FTWRL. 1084Success: Was able to run 'select count(*) from t1_base lock in share mode' under FTWRL. 1085Success: Was able to run 'select count(*) from t1_base lock in share mode' with FTWRL active in another connection. 1086Success: Was able to run FTWRL while 'select count(*) from t1_base lock in share mode' was active in another connection. 1087# 1088# 30.d) SELECT which calls SF updating base table should be 1089# incompatible with FTWRL. 1090Success: Was not able to run 'select f2_base()' under FTWRL. 1091Success: 'select f2_base()' is blocked by FTWRL active in another connection. 1092Success: FTWRL is blocked when 'select f2_base()' is active in another connection. 1093# 1094# 30.e) SELECT which calls SF updating temporary table should be 1095# compatible with FTWRL. 1096Success: Was able to run 'select f2_temp()' under FTWRL. 1097Success: Was able to run 'select f2_temp()' with FTWRL active in another connection. 1098Success: Was able to run FTWRL while 'select f2_temp()' was active in another connection. 1099# 1100# 31) Compatibility of SET statement with FTWRL depends on its 1101# expression and on whether it is a special SET statement. 1102# 1103# 31.a) Ordinary SET with expression which does not 1104# changes base table should be compatible with FTWRL. 1105# Skip last part of compatibility testing as our helper debug 1106# sync-point doesn't work for SET statements. 1107Success: Was able to run 'set @a:= (select count(*) from t1_base)' under FTWRL. 1108Success: Was able to run 'set @a:= (select count(*) from t1_base)' with FTWRL active in another connection. 1109# 1110# 31.b) Ordinary SET which calls SF updating base table should 1111# be incompatible with FTWRL. 1112# Skip last part of compatibility testing as our helper debug 1113# sync-point doesn't work for SET statements. 1114Success: Was not able to run 'set @a:= f2_base()' under FTWRL. 1115Success: 'set @a:= f2_base()' is blocked by FTWRL active in another connection. 1116# 1117# 31.c) Ordinary SET which calls SF updating temporary table 1118# should be compatible with FTWRL. 1119# Skip last part of compatibility testing as our helper debug 1120# sync-point doesn't work for SET statements. 1121Success: Was able to run 'set @a:= f2_temp()' under FTWRL. 1122Success: Was able to run 'set @a:= f2_temp()' with FTWRL active in another connection. 1123# 1124# 31.d) Special SET variants have different compatibility with FTWRL. 1125# 1126# 31.d.I) SET PASSWORD is incompatible with FTWRL as it changes data. 1127create user mysqltest_u1; 1128# Skip last part of compatibility testing as our helper debug 1129# sync-point doesn't work for SET statements. 1130Success: Was not able to run 'set password for 'mysqltest_u1' = password('')' under FTWRL. 1131Success: 'set password for 'mysqltest_u1' = password('')' is blocked by FTWRL active in another connection. 1132drop user mysqltest_u1; 1133# 1134# 31.d.II) SET READ_ONLY is compatible with FTWRL (but has no 1135# effect when executed under it). 1136# Skip last part of compatibility testing as our helper debug 1137# sync-point doesn't work for SET statements. 1138Success: Was able to run 'set global read_only= 1' under FTWRL. 1139Success: Was able to run 'set global read_only= 1' with FTWRL active in another connection. 1140# 1141# 31.d.III) Situation with SET AUTOCOMMIT is complex. 1142# Turning auto-commit off is always compatible with FTWRL. 1143# Turning auto-commit on causes implicit commit and so 1144# is incompatible with FTWRL if there are changes to be 1145# committed. 1146flush tables with read lock; 1147set autocommit= 0; 1148# Turning auto-commit on causes implicit commit so can 1149# be incompatible with FTWRL if there is something to 1150# commit. But since even in this case we allow commits 1151# under active FTWRL such statement should always succeed. 1152insert into t3_temp_trans values (1); 1153set autocommit= 1; 1154unlock tables; 1155delete from t3_temp_trans; 1156# Check that SET AUTOCOMMIT=0 is not blocked and 1157# SET AUTOCOMMIT=1 is blocked by active FTWRL in 1158# another connection. 1159# 1160connection con1; 1161flush tables with read lock; 1162connection default; 1163set autocommit= 0; 1164connection con1; 1165unlock tables; 1166connection default; 1167# Do some work so implicit commit in SET AUTOCOMMIT=1 1168# is not a no-op. 1169insert into t3_trans values (1); 1170connection con1; 1171flush tables with read lock; 1172connection default; 1173# Send: 1174set autocommit= 1; 1175connection con1; 1176# Wait until SET AUTOCOMMIT=1 is blocked. 1177unlock tables; 1178connection default; 1179# Reap SET AUTOCOMMIT=1. 1180delete from t3_trans; 1181# 1182# Check that SET AUTOCOMMIT=1 blocks FTWRL in another connection. 1183set autocommit= 0; 1184insert into t3_trans values (1); 1185set debug_sync='RESET'; 1186set debug_sync='ha_commit_trans_after_acquire_commit_lock SIGNAL parked WAIT_FOR go'; 1187set autocommit= 1; 1188connection con1; 1189set debug_sync='now WAIT_FOR parked'; 1190flush tables with read lock; 1191connection con2; 1192# Wait until FTWRL is blocked. 1193set debug_sync='now SIGNAL go'; 1194connection default; 1195# Reap SET AUTOCOMMIT=1. 1196connection con1; 1197# Reap FTWRL. 1198unlock tables; 1199connection default; 1200delete from t3_trans; 1201set debug_sync= "RESET"; 1202# 1203# 32) SHOW statements are compatible with FTWRL. 1204# Let us test _some_ of them. 1205# 1206# 32.1) SHOW TABLES. 1207Success: Was able to run 'show tables from test' under FTWRL. 1208Success: Was able to run 'show tables from test' with FTWRL active in another connection. 1209Success: Was able to run FTWRL while 'show tables from test' was active in another connection. 1210# 1211# 32.1) SHOW TABLES. 1212Success: Was able to run 'show tables from test' under FTWRL. 1213Success: Was able to run 'show tables from test' with FTWRL active in another connection. 1214Success: Was able to run FTWRL while 'show tables from test' was active in another connection. 1215# 1216# 32.2) SHOW EVENTS. 1217Success: Was able to run 'show events from test' under FTWRL. 1218Success: Was able to run 'show events from test' with FTWRL active in another connection. 1219Success: Was able to run FTWRL while 'show events from test' was active in another connection. 1220# 1221# 32.3) SHOW GRANTS. 1222create user mysqltest_u1; 1223Success: Was able to run 'show grants for mysqltest_u1' under FTWRL. 1224Success: Was able to run 'show grants for mysqltest_u1' with FTWRL active in another connection. 1225Success: Was able to run FTWRL while 'show grants for mysqltest_u1' was active in another connection. 1226drop user mysqltest_u1; 1227# 1228# 32.4) SHOW CREATE TABLE. 1229Success: Was able to run 'show create table t1_base' under FTWRL. 1230Success: Was able to run 'show create table t1_base' with FTWRL active in another connection. 1231Success: Was able to run FTWRL while 'show create table t1_base' was active in another connection. 1232# 1233# 32.5) SHOW CREATE FUNCTION. 1234Success: Was able to run 'show create function f1' under FTWRL. 1235Success: Was able to run 'show create function f1' with FTWRL active in another connection. 1236Success: Was able to run FTWRL while 'show create function f1' was active in another connection. 1237# 1238# 33) SIGNAL statement is compatible with FTWRL. 1239# 1240# Note that we don't cover RESIGNAL as it requires 1241# active handler context. 1242Success: Was able to run 'signal sqlstate '01000'' under FTWRL. 1243Success: Was able to run 'signal sqlstate '01000'' with FTWRL active in another connection. 1244Success: Was able to run FTWRL while 'signal sqlstate '01000'' was active in another connection. 1245# 1246# 34) TRUNCATE TABLE statement. 1247# 1248# 34.a) TRUNCATE of base table is incompatible with FTWRL. 1249Success: Was not able to run 'truncate table t1_base' under FTWRL. 1250Success: 'truncate table t1_base' is blocked by FTWRL active in another connection. 1251Success: FTWRL is blocked when 'truncate table t1_base' is active in another connection. 1252# 1253# 34.b) TRUNCATE of temporary table is compatible with FTWRL. 1254Success: Was able to run 'truncate table t1_temp' under FTWRL. 1255Success: Was able to run 'truncate table t1_temp' with FTWRL active in another connection. 1256Success: Was able to run FTWRL while 'truncate table t1_temp' was active in another connection. 1257# 1258# 35) UPDATE variants. 1259# 1260# 35.1) Simple UPDATE. 1261# 1262# 35.1.a) Simple UPDATE on base table is incompatible with FTWRL. 1263Success: Was not able to run 'update t1_base set i= 1 where i = 0' under FTWRL. 1264Success: 'update t1_base set i= 1 where i = 0' is blocked by FTWRL active in another connection. 1265Success: FTWRL is blocked when 'update t1_base set i= 1 where i = 0' is active in another connection. 1266# 1267# 35.1.b) Simple UPDATE on temporary table is compatible with FTWRL. 1268Success: Was able to run 'update t1_temp set i= 1 where i = 0' under FTWRL. 1269Success: Was able to run 'update t1_temp set i= 1 where i = 0' with FTWRL active in another connection. 1270Success: Was able to run FTWRL while 'update t1_temp set i= 1 where i = 0' was active in another connection. 1271# 1272# 35.2) Multi UPDATE. 1273# 1274# 35.2.a) Multi UPDATE on base tables is incompatible with FTWRL. 1275Success: Was not able to run 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j' under FTWRL. 1276Success: 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j' is blocked by FTWRL active in another connection. 1277Success: FTWRL is blocked when 'update t1_base, t2_base set t1_base.i= 1 where t1_base.i = t2_base.j' is active in another connection. 1278# 1279# 35.2.b) Multi UPDATE on temporary tables is compatible with FTWRL. 1280Success: Was able to run 'update t1_temp, t2_temp set t1_temp.i= 1 where t1_temp.i = t2_temp.j' under FTWRL. 1281Success: Was able to run 'update t1_temp, t2_temp set t1_temp.i= 1 where t1_temp.i = t2_temp.j' with FTWRL active in another connection. 1282Success: Was able to run FTWRL while 'update t1_temp, t2_temp set t1_temp.i= 1 where t1_temp.i = t2_temp.j' was active in another connection. 1283# 1284# 36) USE statement is compatible with FTWRL. 1285# 1286Success: Was able to run 'use mysqltest1' under FTWRL. 1287Success: Was able to run 'use mysqltest1' with FTWRL active in another connection. 1288Success: Was able to run FTWRL while 'use mysqltest1' was active in another connection. 1289# 1290# 37) XA statements. 1291# 1292# XA statements are similar to BEGIN/COMMIT/ROLLBACK. 1293# 1294# XA BEGIN, END, PREPARE, ROLLBACK and RECOVER are compatible 1295# with FTWRL. XA COMMIT is not. 1296flush tables with read lock; 1297# Although all below statements are allowed under FTWRL they 1298# are almost no-ops as FTWRL does commit and does not allows 1299# any non-temporary DML under it. 1300xa start 'test1'; 1301xa end 'test1'; 1302xa prepare 'test1'; 1303xa rollback 'test1'; 1304xa start 'test1'; 1305xa end 'test1'; 1306xa prepare 'test1'; 1307xa commit 'test1'; 1308xa recover; 1309unlock tables; 1310# Check that XA non-COMMIT statements are not and COMMIT is 1311# blocked by active FTWRL in another connection 1312# 1313connection con1; 1314flush tables with read lock; 1315connection default; 1316xa start 'test1'; 1317connection con1; 1318unlock tables; 1319connection default; 1320insert into t3_trans values (1); 1321connection con1; 1322flush tables with read lock; 1323connection default; 1324xa end 'test1'; 1325xa prepare 'test1'; 1326xa rollback 'test1'; 1327connection con1; 1328unlock tables; 1329connection default; 1330xa start 'test1'; 1331insert into t3_trans values (1); 1332connection con1; 1333flush tables with read lock; 1334connection default; 1335connection default; 1336xa end 'test1'; 1337xa prepare 'test1'; 1338# Send: 1339xa commit 'test1';; 1340connection con1; 1341# Wait until XA COMMIT is blocked. 1342unlock tables; 1343connection default; 1344# Reap XA COMMIT. 1345delete from t3_trans; 1346# 1347# Check that XA COMMIT blocks FTWRL in another connection. 1348xa start 'test1'; 1349insert into t3_trans values (1); 1350xa end 'test1'; 1351xa prepare 'test1'; 1352set debug_sync='RESET'; 1353set debug_sync='trans_xa_commit_after_acquire_commit_lock SIGNAL parked WAIT_FOR go'; 1354xa commit 'test1'; 1355connection con1; 1356set debug_sync='now WAIT_FOR parked'; 1357flush tables with read lock; 1358connection con2; 1359# Wait until FTWRL is blocked. 1360set debug_sync='now SIGNAL go'; 1361connection default; 1362# Reap XA COMMIT. 1363connection con1; 1364# Reap FTWRL. 1365unlock tables; 1366connection default; 1367delete from t3_trans; 1368set debug_sync= "RESET"; 1369# 1370# 38) Test effect of auto-commit mode for DML on transactional 1371# temporary tables. 1372# 1373# 38.1) When auto-commit is on each such a statement ends with commit 1374# of changes to temporary tables. But since transactions doing 1375# such changes are considered read only [sic!/QQ] this commit 1376# is compatible with FTWRL. 1377# 1378# Let us demostrate this fact for some common DML statements. 1379Success: Was able to run 'delete from t3_temp_trans' under FTWRL. 1380Success: Was able to run 'delete from t3_temp_trans' with FTWRL active in another connection. 1381Success: Was able to run FTWRL while 'delete from t3_temp_trans' was active in another connection. 1382Success: Was able to run 'insert into t3_temp_trans values (1)' under FTWRL. 1383Success: Was able to run 'insert into t3_temp_trans values (1)' with FTWRL active in another connection. 1384Success: Was able to run FTWRL while 'insert into t3_temp_trans values (1)' was active in another connection. 1385Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' under FTWRL. 1386Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' with FTWRL active in another connection. 1387Success: Was able to run FTWRL while 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' was active in another connection. 1388# 1389# 38.2) When auto-commit is off DML on transaction temporary tables 1390# is compatible with FTWRL. 1391# 1392set autocommit= 0; 1393Success: Was able to run 'delete from t3_temp_trans' under FTWRL. 1394Success: Was able to run 'delete from t3_temp_trans' with FTWRL active in another connection. 1395Success: Was able to run FTWRL while 'delete from t3_temp_trans' was active in another connection. 1396Success: Was able to run 'insert into t3_temp_trans values (1)' under FTWRL. 1397Success: Was able to run 'insert into t3_temp_trans values (1)' with FTWRL active in another connection. 1398Success: Was able to run FTWRL while 'insert into t3_temp_trans values (1)' was active in another connection. 1399Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' under FTWRL. 1400Success: Was able to run 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' with FTWRL active in another connection. 1401Success: Was able to run FTWRL while 'update t3_temp_trans, t2_temp set t3_temp_trans.i= 1 where t3_temp_trans.i = t2_temp.j' was active in another connection. 1402set autocommit= 1; 1403# 1404# 39) Test effect of DDL on transactional tables. 1405# 1406# 39.1) Due to implicit commit at the end of statement some of DDL 1407# statements which are compatible with FTWRL in non-transactional 1408# case are not compatible in case of transactional tables. 1409# 1410# 39.1.a) ANALYZE TABLE for transactional table is incompatible with 1411# FTWRL. 1412flush tables with read lock; 1413analyze table t3_trans; 1414ERROR HY000: Can't execute the query because you have a conflicting read lock 1415unlock tables; 1416# 1417connection con1; 1418flush tables with read lock; 1419connection default; 1420analyze table t3_trans; 1421connection con1; 1422# Check that ANALYZE TABLE is blocked. 1423unlock tables; 1424connection default; 1425# Reap ANALYZE TABLE 1426Table Op Msg_type Msg_text 1427test.t3_trans analyze status Engine-independent statistics collected 1428test.t3_trans analyze status OK 1429# 1430# 39.1.b) CHECK TABLE for transactional table is compatible with FTWRL. 1431# Although it does implicit commit at the end of statement it 1432# is considered to be read-only operation. 1433# Skip last part of compatibility testing as this statement 1434# releases metadata locks in non-standard place. 1435Success: Was able to run 'check table t3_trans' under FTWRL. 1436Success: Was able to run 'check table t3_trans' with FTWRL active in another connection. 1437# 1438# 39.2) Situation with DDL on temporary transactional tables is 1439# complex. 1440# 1441# 39.2.a) Some statements compatible with FTWRL since they don't 1442# do implicit commit. 1443# 1444# For example, CREATE TEMPORARY TABLE: 1445Success: Was able to run 'create temporary table t4_temp_trans(i int) engine=innodb' under FTWRL. 1446Success: Was able to run 'create temporary table t4_temp_trans(i int) engine=innodb' with FTWRL active in another connection. 1447Success: Was able to run FTWRL while 'create temporary table t4_temp_trans(i int) engine=innodb' was active in another connection. 1448# 1449# Or DROP TEMPORARY TABLE: 1450Success: Was able to run 'drop temporary tables t3_temp_trans' under FTWRL. 1451Success: Was able to run 'drop temporary tables t3_temp_trans' with FTWRL active in another connection. 1452Success: Was able to run FTWRL while 'drop temporary tables t3_temp_trans' was active in another connection. 1453# 1454# 39.2.b) Some statements do implicit commit but are considered 1455# read-only and so are compatible with FTWRL. 1456# 1457# For example, REPAIR TABLE: 1458Success: Was able to run 'repair table t3_temp_trans' under FTWRL. 1459Success: Was able to run 'repair table t3_temp_trans' with FTWRL active in another connection. 1460Success: Was able to run FTWRL while 'repair table t3_temp_trans' was active in another connection. 1461# 1462# And ANALYZE TABLE: 1463Error: Wasn't able to run 'analyze table t3_temp_trans' under FTWRL! 1464Success: Was able to run 'analyze table t3_temp_trans' with FTWRL active in another connection. 1465Success: Was able to run FTWRL while 'analyze table t3_temp_trans' was active in another connection. 1466# 1467# And ALTER TABLE: 1468Success: Was able to run 'alter table t3_temp_trans add column c1 int' under FTWRL. 1469Success: Was able to run 'alter table t3_temp_trans add column c1 int' with FTWRL active in another connection. 1470Success: Was able to run FTWRL while 'alter table t3_temp_trans add column c1 int' was active in another connection. 1471# 1472# 40) Test effect of implicit commit for DDL which is otherwise 1473# compatible with FTWRL. Implicit commit at the start of DDL 1474# statement can make it incompatible with FTWRL if there are 1475# some changes to be commited even in case when DDL statement 1476# itself is compatible with FTWRL. 1477# 1478# For example CHECK TABLE for base non-transactional tables and 1479# ALTER TABLE for temporary non-transactional tables are affected. 1480begin; 1481insert into t3_trans values (1); 1482# 1483connection con1; 1484flush tables with read lock; 1485connection default; 1486check table t1_base; 1487connection con1; 1488# Check that CHECK TABLE is blocked. 1489unlock tables; 1490connection default; 1491# Reap CHECK TABLE 1492Table Op Msg_type Msg_text 1493test.t1_base check status OK 1494begin; 1495delete from t3_trans; 1496# 1497connection con1; 1498flush tables with read lock; 1499connection default; 1500alter table t1_temp add column c1 int; 1501connection con1; 1502# Check that ALTER TABLE is blocked. 1503unlock tables; 1504connection default; 1505# Reap ALTER TABLE 1506alter table t1_temp drop column c1; 1507# 1508# Check that FLUSH TABLES WITH READ LOCK is blocked by individual 1509# statements and is not blocked in the presence of transaction which 1510# has done some changes earlier but is idle now (or does only reads). 1511# This allows to use this statement even on systems which has long 1512# running transactions. 1513# 1514begin; 1515insert into t1_base values (1); 1516insert into t3_trans values (1); 1517connection con1; 1518# The below FTWRL should not be blocked by transaction in 'default'. 1519flush tables with read lock; 1520connection default; 1521# Transaction still is able to read even with FTWRL active in another 1522# connection. 1523select * from t1_base; 1524i 15251 1526select * from t2_base; 1527j 1528select * from t3_trans; 1529i 15301 1531connection con1; 1532unlock tables; 1533connection default; 1534commit; 1535delete from t1_base; 1536delete from t3_trans; 1537# 1538# Check that impending FTWRL blocks new DML statements and 1539# so can't be starved by a constant flow of DML. 1540# (a.k.a. test for bug #54673 "It takes too long to get 1541# readlock for 'FLUSH TABLES WITH READ LOCK'"). 1542# 1543set debug_sync='RESET'; 1544set debug_sync='execute_command_after_close_tables SIGNAL parked WAIT_FOR go'; 1545insert into t1_base values (1); 1546connection con1; 1547set debug_sync='now WAIT_FOR parked'; 1548flush tables with read lock; 1549connection con2; 1550# Wait until FTWRL is blocked. 1551# Try to run another INSERT and see that it is blocked. 1552insert into t2_base values (1);; 1553connection con3; 1554# Wait until new INSERT is blocked. 1555# Unblock INSERT in the first connection. 1556set debug_sync='now SIGNAL go'; 1557connection default; 1558# Reap first INSERT. 1559connection con1; 1560# Reap FTWRL. 1561unlock tables; 1562connection con2; 1563# Reap second INSERT. 1564connection default; 1565set debug_sync= "RESET"; 1566delete from t1_base; 1567delete from t2_base; 1568 1569# Check that COMMIT thas is issued after 1570# FLUSH TABLES WITH READ LOCK is not blocked by 1571# FLUSH TABLES WITH READ LOCK from another connection. 1572# This scenario is used in innobackup.pl. The COMMIT goes 1573# through because the transaction started by FTWRL does 1574# not modify any tables, and the commit blocker lock is 1575# only taken when there were such modifications. 1576 1577flush tables with read lock; 1578connection con1; 1579# The below FTWRL should not be blocked by transaction in 'default'. 1580flush tables with read lock; 1581connection default; 1582select * from t1_base; 1583i 1584select * from t3_trans; 1585i 1586commit; 1587connection con1; 1588select * from t1_base; 1589i 1590select * from t3_trans; 1591i 1592commit; 1593unlock tables; 1594connection default; 1595unlock tables; 1596# 1597# Check how FLUSH TABLE WITH READ LOCK is handled for MERGE tables. 1598# As usual there are tricky cases related to this type of tables. 1599# 1600# 1601# 1) Most typical case - base MERGE table with base underlying tables. 1602# 1603# 1.a) DML statements which change data should be incompatible with FTWRL. 1604create table tm_base (i int) engine=merge union=(t1_base) insert_method=last; 1605Success: Was not able to run 'insert into tm_base values (1)' under FTWRL. 1606Success: 'insert into tm_base values (1)' is blocked by FTWRL active in another connection. 1607Success: FTWRL is blocked when 'insert into tm_base values (1)' is active in another connection. 1608# 1609# 1.b) DDL statement on such table should be incompatible with FTWRL as well. 1610Success: Was not able to run 'alter table tm_base insert_method=first' under FTWRL. 1611Success: 'alter table tm_base insert_method=first' is blocked by FTWRL active in another connection. 1612Success: FTWRL is blocked when 'alter table tm_base insert_method=first' is active in another connection. 1613drop table tm_base; 1614# 1615# 2) Temporary MERGE table with base underlying tables. 1616# 1617# 2.a) DML statements which change data should be incompatible with FTWRL 1618# as they affect base tables. 1619create temporary table tm_temp_base (i int) engine=merge union=(t1_base) insert_method=last; 1620Success: Was not able to run 'insert into tm_temp_base values (1)' under FTWRL. 1621Success: 'insert into tm_temp_base values (1)' is blocked by FTWRL active in another connection. 1622Success: FTWRL is blocked when 'insert into tm_temp_base values (1)' is active in another connection. 1623# 1624# 2.b) Some of DDL statements on such table can be compatible with FTWRL 1625# as they don't affect base tables. 1626Success: Was able to run 'drop temporary tables tm_temp_base' under FTWRL. 1627Success: Was able to run 'drop temporary tables tm_temp_base' with FTWRL active in another connection. 1628Success: Was able to run FTWRL while 'drop temporary tables tm_temp_base' was active in another connection. 1629# 1630# 2.c) ALTER statement is incompatible with FTWRL. Even though it does 1631# not change data in base table it still acquires strong metadata 1632# locks on them. 1633Success: Was not able to run 'alter table tm_temp_base insert_method=first' under FTWRL. 1634Success: 'alter table tm_temp_base insert_method=first' is blocked by FTWRL active in another connection. 1635Success: FTWRL is blocked when 'alter table tm_temp_base insert_method=first' is active in another connection. 1636drop table tm_temp_base; 1637# 1638# 3) Temporary MERGE table with temporary underlying tables. 1639# 1640# 3.a) DML statements should be compatible with FTWRL as 1641# no base table is going to be affected. 1642create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last; 1643Success: Was able to run 'insert into tm_temp_temp values (1)' under FTWRL. 1644Success: Was able to run 'insert into tm_temp_temp values (1)' with FTWRL active in another connection. 1645Success: Was able to run FTWRL while 'insert into tm_temp_temp values (1)' was active in another connection. 1646# 1647# 3.b) DDL statements should be compatible with FTWRL as well 1648# as no base table is going to be affected too. 1649Success: Was able to run 'alter table tm_temp_temp union=(t1_temp) insert_method=first' under FTWRL. 1650Success: Was able to run 'alter table tm_temp_temp union=(t1_temp) insert_method=first' with FTWRL active in another connection. 1651Success: Was able to run FTWRL while 'alter table tm_temp_temp union=(t1_temp) insert_method=first' was active in another connection. 1652drop table tm_temp_temp; 1653# 1654# 4) For the sake of completeness let us check that base MERGE tables 1655# with temporary underlying tables are not functional. 1656create table tm_base_temp (i int) engine=merge union=(t1_temp) insert_method=last; 1657select * from tm_base_temp; 1658ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 1659drop table tm_base_temp; 1660# 1661# Clean-up. 1662# 1663drop event e1; 1664drop function f2_temp; 1665drop function f2_base; 1666drop procedure p2; 1667drop view v1; 1668drop function f1; 1669drop procedure p1; 1670drop database `#mysql50#mysqltest-2`; 1671drop database mysqltest1; 1672drop temporary tables t1_temp, t2_temp; 1673drop tables t1_base, t2_base, t3_trans; 1674disconnect con1; 1675disconnect con2; 1676disconnect con3; 1677set global sql_mode=default; 1678# 1679# Deadlock between FTWRL under open handler and DDL/LOCK TABLES 1680# 1681CREATE TABLE t1(a INT); 1682# 1683connect con3,localhost,root,,; 1684HANDLER t1 OPEN; 1685# 1686connect con1,localhost,root,,; 1687SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL ready'; 1688LOCK TABLE t1 WRITE; 1689# 1690# we need to do it in a separate connection, 1691# because SET DEBUG_SYNC call open_tables()/mysql_ha_flush() :( 1692connect con2,localhost,root,,; 1693SET DEBUG_SYNC= 'now WAIT_FOR ready'; 1694disconnect con2; 1695# 1696connection default; 1697FLUSH TABLES WITH READ LOCK; 1698UNLOCK TABLES; 1699# 1700connection con3; 1701HANDLER t1 CLOSE; 1702disconnect con3; 1703# 1704connection con1; 1705UNLOCK TABLES; 1706disconnect con1; 1707# 1708connection default; 1709DROP TABLE t1; 1710SET DEBUG_SYNC= 'RESET'; 1711# 1712# Make sure pending LOCK TABLES doesn't block FTWRL 1713# 1714CREATE TABLE t1(a INT); 1715LOCK TABLE t1 READ; 1716# 1717connect con1,localhost,root,,; 1718SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL ready'; 1719LOCK TABLE t1 WRITE; 1720# 1721connect con2,localhost,root,,; 1722SET DEBUG_SYNC= 'now WAIT_FOR ready'; 1723FLUSH TABLES WITH READ LOCK; 1724UNLOCK TABLES; 1725disconnect con2; 1726# 1727connection default; 1728UNLOCK TABLES; 1729# 1730connection con1; 1731UNLOCK TABLES; 1732disconnect con1; 1733# 1734connection default; 1735DROP TABLE t1; 1736SET DEBUG_SYNC= 'RESET'; 1737# 1738# MDEV-19384 Deadlock between FTWRL under open HANDLER, LOCK TABLE 1739# and DROP DATABASE 1740# 1741SET DEBUG_SYNC= 'ftwrl_before_lock SIGNAL ready WAIT_FOR go'; 1742CREATE DATABASE mysqltest; 1743CREATE TABLE mysqltest.t1(a INT); 1744HANDLER mysqltest.t1 OPEN as t1; 1745connect con1,localhost,root,,; 1746SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL ready1'; 1747LOCK TABLE mysqltest.t1 WRITE; 1748connect con2,localhost,root,,; 1749SET DEBUG_SYNC= 'now WAIT_FOR ready1'; 1750SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL ready2'; 1751DROP DATABASE mysqltest; 1752connect con3,localhost,root,,; 1753SET DEBUG_SYNC= 'now WAIT_FOR ready2'; 1754connection default; 1755FLUSH TABLES WITH READ LOCK; 1756connection con3; 1757SET DEBUG_SYNC= 'now WAIT_FOR ready'; 1758disconnect con3; 1759connection con1; 1760SET DEBUG_SYNC= 'now SIGNAL go'; 1761disconnect con1; 1762connection default; 1763UNLOCK TABLES; 1764HANDLER t1 CLOSE; 1765connection con2; 1766disconnect con2; 1767connection default; 1768SET DEBUG_SYNC= 'RESET'; 1769