1--source include/have_metadata_lock_info.inc 2-- source include/have_innodb.inc 3 4# Save the initial number of concurrent sessions. 5--source include/count_sessions.inc 6 7set @old_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout; 8set global innodb_lock_wait_timeout=300; 9set session innodb_lock_wait_timeout=300; 10 11call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); 12 13--echo # 14--echo # Bug #22876 Four-way deadlock 15--echo # 16 17--disable_warnings 18DROP TABLE IF EXISTS t1; 19--enable_warnings 20 21connect (con1,localhost,root,,); 22connect (con2,localhost,root,,); 23connect (con3,localhost,root,,); 24 25connection con1; 26set @@autocommit=0; 27CREATE TABLE t1(s1 INT UNIQUE) ENGINE=innodb; 28INSERT INTO t1 VALUES (1); 29 30connection con2; 31set @@autocommit=0; 32INSERT INTO t1 VALUES (2); 33--send INSERT INTO t1 VALUES (1) 34 35connection con3; 36set @@autocommit=0; 37--send DROP TABLE t1 38 39connection con1; 40--echo # Waiting for until transaction will be locked inside innodb subsystem 41let $wait_condition= 42 SELECT COUNT(*) = 1 FROM information_schema.innodb_trx 43 WHERE trx_query = 'INSERT INTO t1 VALUES (1)' AND 44 trx_operation_state = 'inserting' AND 45 trx_state = 'LOCK WAIT'; 46--source include/wait_condition.inc 47let $wait_condition= 48 SELECT COUNT(*) = 1 FROM information_schema.processlist 49 WHERE info = "DROP TABLE t1" and 50 state = "Waiting for table metadata lock"; 51--source include/wait_condition.inc 52--echo # Connection 1 is now holding the lock. 53--echo # Issuing insert from connection 1 while connection 2&3 54--echo # is waiting for the lock should give a deadlock error. 55--error ER_LOCK_DEADLOCK 56INSERT INTO t1 VALUES (2); 57 58--echo # Cleanup 59connection con2; 60--reap 61commit; 62set @@autocommit=1; 63connection con1; 64commit; 65set @@autocommit=1; 66connection con3; 67--reap 68set @@autocommit=1; 69connection default; 70 71disconnect con1; 72disconnect con2; 73disconnect con3; 74 75 76--echo # 77--echo # Test for bug #37346 "innodb does not detect deadlock between update 78--echo # and alter table". 79--echo # 80--disable_warnings 81drop table if exists t1; 82--enable_warnings 83create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB; 84insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); 85begin; 86--echo # Run statement which acquires X-lock on one of table's rows. 87update t1 set c3=c3+1 where c2=3; 88 89--echo # 90connect (con37346,localhost,root,,test,,); 91connection con37346; 92--echo # The below ALTER TABLE statement should wait till transaction 93--echo # in connection 'default' is complete and then succeed. 94--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error. 95--echo # Sending: 96--send alter table t1 add column c4 int; 97 98--echo # 99connection default; 100--echo # Wait until the above ALTER TABLE gets blocked because this 101--echo # connection holds SW metadata lock on table to be altered. 102let $wait_condition= 103 select count(*) = 1 from information_schema.processlist 104 where state = "Waiting for table metadata lock" and 105 info = "alter table t1 add column c4 int"; 106--source include/wait_condition.inc 107 108--echo # The below statement should succeed. It should not 109--echo # deadlock or end with ER_LOCK_DEADLOCK error. 110update t1 set c3=c3+1 where c2=4; 111 112--echo # Unblock ALTER TABLE by committing transaction. 113commit; 114 115--echo # 116connection con37346; 117--echo # Reaping ALTER TABLE. 118--reap 119 120--echo # 121connection default; 122disconnect con37346; 123drop table t1; 124 125--echo # 126--echo # Bug#53798 OPTIMIZE TABLE breaks repeatable read 127--echo # 128 129--disable_warnings 130DROP TABLE IF EXISTS t1; 131--enable_warnings 132 133CREATE TABLE t1 (a INT) engine=innodb; 134INSERT INTO t1 VALUES (1), (2), (3); 135 136connect (con1, localhost, root); 137START TRANSACTION WITH CONSISTENT SNAPSHOT; 138SELECT * FROM t1; 139 140connection default; 141--echo # This should block 142--echo # Sending: 143--send OPTIMIZE TABLE t1 144 145connection con1; 146let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 147 WHERE state='Waiting for table metadata lock' AND info='OPTIMIZE TABLE t1'; 148--source include/wait_condition.inc 149SELECT * FROM t1; 150COMMIT; 151 152connection default; 153--echo # Reaping OPTIMIZE TABLE t1 154--reap 155disconnect con1; 156DROP TABLE t1; 157 158 159--echo # 160--echo # Bug#49891 View DDL breaks REPEATABLE READ 161--echo # 162 163--disable_warnings 164DROP TABLE IF EXISTS t1, t2; 165DROP VIEW IF EXISTS v2; 166--enable_warnings 167 168CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb; 169CREATE TABLE t2 ( f1 INTEGER ); 170CREATE VIEW v1 AS SELECT 1 FROM t1; 171 172connect (con2, localhost, root); 173connect (con3, localhost, root); 174 175connection con3; 176LOCK TABLE t1 WRITE; 177 178connection default; 179START TRANSACTION; 180# This should block due to t1 being locked. 181--echo # Sending: 182--send SELECT * FROM v1 183 184connection con2; 185--echo # Waiting for 'SELECT * FROM v1' to sync in. 186let $wait_condition= 187 SELECT COUNT(*) = 1 FROM information_schema.processlist 188 WHERE state = "Waiting for table metadata lock" AND info = "SELECT * FROM v1"; 189--source include/wait_condition.inc 190# This should block due to v1 being locked. 191--echo # Sending: 192--send ALTER VIEW v1 AS SELECT 2 FROM t2 193 194connection con3; 195--echo # Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in. 196let $wait_condition= 197 SELECT COUNT(*) = 1 FROM information_schema.processlist 198 WHERE state = "Waiting for table metadata lock" AND 199 info = "ALTER VIEW v1 AS SELECT 2 FROM t2"; 200--source include/wait_condition.inc 201# Unlock t1 allowing SELECT * FROM v1 to proceed. 202UNLOCK TABLES; 203 204connection default; 205--echo # Reaping: SELECT * FROM v1 206--reap 207SELECT * FROM v1; 208COMMIT; 209 210connection con2; 211--echo # Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2 212--reap 213 214connection default; 215DROP TABLE t1, t2; 216DROP VIEW v1; 217disconnect con2; 218disconnect con3; 219 220 221--echo # 222--echo # Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY 223--echo # KEY NO 0 FOR TABLE IN ERROR LOG 224--echo # 225 226--disable_warnings 227DROP TABLE IF EXISTS t1; 228--enable_warnings 229 230--connect (con1,localhost,root) 231 232connection default; 233CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB; 234INSERT INTO t1 VALUES (1, 12345); 235START TRANSACTION; 236SELECT * FROM t1; 237 238--connection con1 239SET lock_wait_timeout=1; 240# Test with two timeouts, as the first version of this patch 241# only worked with one timeout. 242--error ER_LOCK_WAIT_TIMEOUT 243ALTER TABLE t1 ADD INDEX idx(value); 244--error ER_LOCK_WAIT_TIMEOUT 245ALTER TABLE t1 ADD INDEX idx(value); 246 247--connection default 248SELECT * FROM t1; 249COMMIT; 250DROP TABLE t1; 251disconnect con1; 252 253 254# Check that all connections opened by test cases in this file are really 255# gone so execution of other tests won't be affected by their presence. 256--source include/wait_until_count_sessions.inc 257 258set global innodb_lock_wait_timeout=@old_innodb_lock_wait_timeout; 259 260