1connect con1,localhost,root,,; 2connect con2,localhost,root,,; 3connection con1; 4drop table if exists t1,t2; 5drop database if exists mysqltest; 6create temporary table t1(n int not null primary key); 7create table t2(n int); 8insert into t2 values(3); 9connection con1; 10select * from t1; 11n 123 13connection con2; 14flush tables with read lock and disable checkpoint; 15drop table t2; 16ERROR HY000: Can't execute the query because you have a conflicting read lock 17connection con1; 18drop table t2; 19connection con2; 20unlock tables; 21connection con1; 22connection con1; 23create database mysqltest; 24create table mysqltest.t1(n int); 25insert into mysqltest.t1 values (23); 26flush tables with read lock; 27connection con2; 28drop database mysqltest; 29connection con1; 30select * from mysqltest.t1; 31n 3223 33unlock tables; 34connection con2; 35connection con1; 36create table t1 (n int); 37flush tables with read lock; 38disconnect con1; 39connection con2; 40insert into t1 values (345); 41select * from t1; 42n 43345 44drop table t1; 45create table t1 (c1 int); 46lock table t1 write; 47flush tables with read lock; 48ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 49lock table t1 read; 50flush tables with read lock; 51ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 52unlock tables; 53flush tables with read lock; 54lock table t1 write; 55ERROR HY000: Can't execute the query because you have a conflicting read lock 56lock table t1 read; 57lock table t1 write; 58ERROR HY000: Can't execute the query because you have a conflicting read lock 59unlock tables; 60create table t2 (c1 int); 61create table t3 (c1 int); 62lock table t1 read, t2 read, t3 write; 63flush tables with read lock; 64ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 65lock table t1 read, t2 read, t3 read; 66flush tables with read lock; 67ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 68unlock tables; 69drop table t1, t2, t3; 70create table t1 (c1 int); 71create table t2 (c1 int); 72connect con1,localhost,root,,; 73connect con3,localhost,root,,; 74connection con1; 75lock table t1 write; 76connection con2; 77flush tables with read lock; 78connection con3; 79insert into t2 values(1); 80connection con1; 81unlock tables; 82disconnect con1; 83connection con2; 84disconnect con2; 85connection con3; 86disconnect con3; 87connection default; 88drop table t1, t2; 89drop table if exists t1, t2; 90set session low_priority_updates=1; 91create table t1 (a int); 92create table t2 (b int); 93lock tables t1 write; 94flush tables with read lock; 95ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 96unlock tables; 97lock tables t1 read, t2 write; 98flush tables with read lock; 99ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 100unlock tables; 101lock tables t1 read; 102flush tables with read lock; 103ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 104unlock tables; 105drop table t1, t2; 106set session low_priority_updates=default; 107connect con1,localhost,root,,; 108select benchmark(200, (select sin(1))) > 1000; 109disconnect con1; 110connection default; 111End of 5.0 tests 112set @old_general_log= @@general_log; 113set @old_read_only= @@read_only; 114set global general_log= on; 115flush tables with read lock; 116flush logs; 117unlock tables; 118set global read_only=1; 119flush logs; 120unlock tables; 121flush tables with read lock; 122flush logs; 123unlock tables; 124set global general_log= @old_general_log; 125set global read_only= @old_read_only; 126End of 5.1 tests 127# 128# Additional test for bug #51136 "Crash in pthread_rwlock_rdlock 129# on TEMPORARY + HANDLER + LOCK + SP". 130# Also see the main test for this bug in include/handler.inc. 131# 132drop tables if exists t1, t2; 133create table t1 (i int); 134create temporary table t2 (j int); 135flush tables with read lock; 136lock table t2 read; 137# This commit should not release any MDL locks. 138commit; 139# The below statement crashed before the bug fix as it 140# has attempted to release global shared metadata lock 141# which was already released by commit. 142unlock tables; 143drop tables t1, t2; 144# 145# Tests for WL#5000 FLUSH TABLES|TABLE table_list WITH READ LOCK 146# 147# I. Check the incompatible changes in the grammar. 148# 149flush tables with read lock, hosts; 150ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' hosts' at line 1 151flush privileges, tables; 152ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tables' at line 1 153flush privileges, tables with read lock; 154ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tables with read lock' at line 1 155flush privileges, tables; 156ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tables' at line 1 157flush tables with read lock, tables; 158ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' tables' at line 1 159show tables; 160Tables_in_test 161# 162# II. Check the allowed syntax. 163# 164drop table if exists t1, t2, t3; 165create table t1 (a int); 166create table t2 (a int); 167create table t3 (a int); 168lock table t1 read, t2 read; 169flush tables with read lock; 170ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 171unlock tables; 172flush tables with read lock; 173flush tables t1, t2 with read lock; 174flush tables t1, t2 with read lock; 175ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 176flush tables with read lock; 177ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 178select * from t1; 179a 180select * from t2; 181a 182select * from t3; 183ERROR HY000: Table 't3' was not locked with LOCK TABLES 184insert into t1 (a) values (1); 185ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 186insert into t2 (a) values (1); 187ERROR HY000: Table 't2' was locked with a READ lock and can't be updated 188insert into t3 (a) values (1); 189ERROR HY000: Table 't3' was not locked with LOCK TABLES 190lock table no_such_table read; 191ERROR 42S02: Table 'test.no_such_table' doesn't exist 192# 193# We implicitly left the locked tables 194# mode but still have the read lock. 195# 196insert into t2 (a) values (1); 197ERROR HY000: Can't execute the query because you have a conflicting read lock 198unlock tables; 199insert into t1 (a) values (1); 200insert into t2 (a) values (1); 201flush table t1, t2 with read lock; 202select * from t1; 203a 2041 205select * from t2; 206a 2071 208select * from t3; 209ERROR HY000: Table 't3' was not locked with LOCK TABLES 210insert into t1 (a) values (2); 211ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 212insert into t2 (a) values (2); 213ERROR HY000: Table 't2' was locked with a READ lock and can't be updated 214insert into t3 (a) values (2); 215ERROR HY000: Table 't3' was not locked with LOCK TABLES 216lock table no_such_table read; 217ERROR 42S02: Table 'test.no_such_table' doesn't exist 218insert into t3 (a) values (2); 219# 220# III. Concurrent tests. 221# 222connect con1,localhost,root,,; 223# 224# Check that flush tables <list> with read lock 225# does not affect non-locked tables. 226connection default; 227# 228flush tables t1 with read lock; 229connection con1; 230select * from t1; 231a 2321 233select * from t2; 234a 2351 236insert into t2 (a) values (3); 237connection default; 238unlock tables; 239# 240# Check that "FLUSH TABLES <list> WITH READ LOCK" is 241# compatible with active "FLUSH TABLES WITH READ LOCK". 242# Vice versa it is not true, since tables read-locked by 243# "FLUSH TABLES <list> WITH READ LOCK" can't be flushed. 244flush tables with read lock; 245connection con1; 246flush table t1 with read lock; 247select * from t1; 248a 2491 250unlock tables; 251connection default; 252unlock tables; 253# 254# Check that FLUSH TABLES t1 WITH READ LOCK 255# does not conflict with an existing FLUSH TABLES t2 256# WITH READ LOCK. 257# 258flush table t1 with read lock; 259connection con1; 260flush table t2 with read lock; 261unlock tables; 262connection default; 263unlock tables; 264# 265# Check that FLUSH TABLES t1 WITH READ LOCK 266# does not conflict with SET GLOBAL read_only=1. 267# 268set global read_only=1; 269connection con1; 270flush table t1 with read lock; 271unlock tables; 272connection default; 273set global read_only=0; 274# 275# Check that it's possible to read-lock 276# tables locked with FLUSH TABLE <list> WITH READ LOCK. 277# 278flush tables t1, t2 with read lock; 279connection con1; 280lock table t1 read, t2 read; 281unlock tables; 282connection default; 283unlock tables; 284connection con1; 285disconnect con1; 286connection default; 287drop table t1, t2, t3; 288# 289# Bug#51710 FLUSH TABLES <view> WITH READ LOCK kills the server 290# 291drop view if exists v1, v2, v3; 292drop table if exists t1, v1; 293create table t1 (a int); 294create view v1 as select 1; 295create view v2 as select * from t1; 296create view v3 as select * from v2; 297flush table v1, v2, v3 with read lock; 298ERROR HY000: 'test.v1' is not of type 'BASE TABLE' 299flush table v1 with read lock; 300ERROR HY000: 'test.v1' is not of type 'BASE TABLE' 301flush table v2 with read lock; 302ERROR HY000: 'test.v2' is not of type 'BASE TABLE' 303flush table v3 with read lock; 304ERROR HY000: 'test.v3' is not of type 'BASE TABLE' 305create temporary table v1 (a int); 306flush table v1 with read lock; 307ERROR HY000: 'test.v1' is not of type 'BASE TABLE' 308drop view v1; 309create table v1 (a int); 310flush table v1 with read lock; 311drop temporary table v1; 312unlock tables; 313drop view v2, v3; 314drop table t1, v1; 315# 316# FLUSH TABLES <list> WITH READ LOCK and HANDLER 317# 318drop table if exists t1; 319create table t1 (a int, key a (a)); 320insert into t1 (a) values (1), (2), (3); 321handler t1 open; 322handler t1 read a next; 323a 3241 325handler t1 read a next; 326a 3272 328flush tables t1 with read lock; 329handler t1 read a next; 330ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 331unlock tables; 332handler t1 read a next; 333a 3343 335handler t1 close; 336drop table t1; 337# 338# Bug#52117 Pending FLUSH TALBES <list> aborts 339# transactions unnecessarily. 340# 341drop table if exists t1; 342connect con1,localhost,root,,; 343connect con2,localhost,root,,; 344connection default; 345create table t1 (a int); 346begin; 347select * from t1; 348a 349connection con1; 350# 351# Issue a LOCK TABLE t1 READ. We could use HANDLER t1 OPEN 352# or a long-running select -- anything that 353# prevents FLUSH TABLE t1 from immediate completion would do. 354# 355lock table t1 read; 356connection con2; 357# 358# FLUSH TABLE expels the table definition from the cache. 359# Sending 'flush table t1'... 360flush table t1; 361connection default; 362# Let flush table sync in. 363select * from t1; 364a 365connection con1; 366select * from t1; 367a 368unlock tables; 369connection default; 370select count(*) from information_schema.processlist where state = "Waiting for table metadata lock"; 371count(*) 3721 373commit; 374connection con2; 375# Reaping 'flush table t1'... 376connection default; 377# 378# Repeat the same test but with FLUSH TABLES 379# 380begin; 381select * from t1; 382a 383connection con1; 384# 385# Issue a LOCK TABLE t1 READ. 386# 387lock table t1 read; 388connection con2; 389flush tables; 390connection default; 391select * from t1; 392a 393connection con1; 394select * from t1; 395a 396unlock tables; 397connection con2; 398# Reaping 'flush tables'... 399connection default; 400commit; 401# Cleanup 402connection con1; 403disconnect con1; 404connection con2; 405disconnect con2; 406connection default; 407drop table t1; 408# 409# Test for bug #55273 "FLUSH TABLE tm WITH READ LOCK for Merge table 410# causes assert failure". 411# 412drop table if exists t1, t2, tm; 413create table t1 (i int); 414create table t2 (i int); 415create table tm (i int) engine=merge union=(t1, t2); 416insert into t1 values (1), (2); 417insert into t2 values (3), (4); 418# The below statement should succeed and lock merge 419# table for read. Only merge table gets flushed and 420# not underlying tables. 421flush tables tm with read lock; 422select * from tm; 423i 4241 4252 4263 4274 428# Check that underlying tables are locked. 429select * from t1; 430i 4311 4322 433select * from t2; 434i 4353 4364 437unlock tables; 438# This statement should succeed as well and flush 439# all tables in the list. 440flush tables tm, t1, t2 with read lock; 441select * from tm; 442i 4431 4442 4453 4464 447# Naturally, underlying tables should be locked in this case too. 448select * from t1; 449i 4501 4512 452select * from t2; 453i 4543 4554 456unlock tables; 457drop tables tm, t1, t2; 458# 459# Test for bug #57006 "Deadlock between HANDLER and 460# FLUSH TABLES WITH READ LOCK". 461# 462drop table if exists t1, t2; 463connect con1,localhost,root,,; 464connect con2,localhost,root,,; 465connection default; 466create table t1 (i int); 467create table t2 (i int); 468handler t1 open; 469connection con1; 470flush tables with read lock; 471connection default; 472# The below statement should not cause deadlock. 473# Sending: 474insert into t2 values (1); 475connection con2; 476# Wait until INSERT starts to wait for FTWRL to go away. 477connection con1; 478unlock tables; 479connection default; 480# Reap INSERT. 481handler t1 close; 482# Cleanup. 483connection con1; 484disconnect con1; 485connection con2; 486disconnect con2; 487connection default; 488drop tables t1, t2; 489# 490# Bug#57649 FLUSH TABLES under FLUSH TABLES <list> WITH READ LOCK leads 491# to assert failure. 492# 493DROP TABLE IF EXISTS t1; 494CREATE TABLE t1 (a INT); 495FLUSH TABLES t1 WITH READ LOCK; 496FLUSH TABLES; 497ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 498CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1; 499ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 500ALTER TABLE t1 COMMENT 'test'; 501ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 502UNLOCK TABLES; 503DROP TABLE t1; 504# 505# Test for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES 506# GRADUALLY IF A TRIGGER EXISTS". 507# 508# One of side-effects of this bug was that a transaction which 509# involved DML statements requiring prelocking blocked concurrent 510# FLUSH TABLES WITH READ LOCK for the whole its duration, while 511# correct behavior in this case is to block FTWRL only for duration 512# of individual DML statements. 513DROP TABLE IF EXISTS t1; 514CREATE TABLE t1 (id INT PRIMARY KEY, value INT); 515INSERT INTO t1 VALUES (1, 1); 516CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @var = "a"; 517BEGIN; 518UPDATE t1 SET value= value + 1 WHERE id = 1; 519connect con1, localhost, root; 520# The below FLUSH TABLES WITH READ LOCK should succeed and 521# should not be blocked by the transaction in default connection. 522FLUSH TABLES WITH READ LOCK; 523UNLOCK TABLES; 524disconnect con1; 525connection default; 526COMMIT; 527DROP TABLE t1; 528# 529# Test flushing slave or relay logs twice 530# 531flush relay logs,relay logs; 532ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS 533flush slave,slave; 534ERROR HY000: Incorrect usage of FLUSH and SLAVE 535# 536# MDEV-15890 Strange error message if you try to 537# FLUSH TABLES <view> after LOCK TABLES <view>. 538# 539CREATE TABLE t1 (qty INT, price INT); 540CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; 541LOCK TABLES v1 READ; 542FLUSH TABLES v1; 543ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 544UNLOCK TABLES; 545LOCK TABLES v1 WRITE; 546FLUSH TABLES v1; 547UNLOCK TABLES; 548LOCK TABLES v1 READ; 549FLUSH TABLES t1; 550ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 551UNLOCK TABLES; 552LOCK TABLES t1 READ; 553FLUSH TABLES v1; 554ERROR HY000: Table 'v1' was not locked with LOCK TABLES 555UNLOCK TABLES; 556DROP VIEW v1; 557DROP TABLE t1; 558# 559# Test FLUSH THREADS 560# 561flush threads; 562show status like "Threads_cached"; 563Variable_name Value 564Threads_cached 0 565