1set @old_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout;
2set global innodb_lock_wait_timeout=300;
3set session innodb_lock_wait_timeout=300;
4call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction");
5#
6# Bug #22876 Four-way deadlock
7#
8DROP TABLE IF EXISTS t1;
9connect  con1,localhost,root,,;
10connect  con2,localhost,root,,;
11connect  con3,localhost,root,,;
12connection con1;
13set @@autocommit=0;
14CREATE TABLE t1(s1 INT UNIQUE) ENGINE=innodb;
15INSERT INTO t1 VALUES (1);
16connection con2;
17set @@autocommit=0;
18INSERT INTO t1 VALUES (2);
19INSERT INTO t1 VALUES (1);
20connection con3;
21set @@autocommit=0;
22DROP TABLE t1;
23connection con1;
24# Waiting for until transaction will be locked inside innodb subsystem
25# Connection 1 is now holding the lock.
26# Issuing insert from connection 1 while connection 2&3
27# is waiting for the lock should give a deadlock error.
28INSERT INTO t1 VALUES (2);
29ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
30# Cleanup
31connection con2;
32commit;
33set @@autocommit=1;
34connection con1;
35commit;
36set @@autocommit=1;
37connection con3;
38set @@autocommit=1;
39connection default;
40disconnect con1;
41disconnect con2;
42disconnect con3;
43#
44# Test for bug #37346 "innodb does not detect deadlock between update
45#                      and alter table".
46#
47drop table if exists t1;
48create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB;
49insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
50begin;
51# Run statement which acquires X-lock on one of table's rows.
52update t1 set c3=c3+1 where c2=3;
53#
54connect  con37346,localhost,root,,test,,;
55connection con37346;
56# The below ALTER TABLE statement should wait till transaction
57# in connection 'default' is complete and then succeed.
58# It should not deadlock or fail with ER_LOCK_DEADLOCK error.
59# Sending:
60alter table t1 add column c4 int;;
61#
62connection default;
63# Wait until the above ALTER TABLE gets blocked because this
64# connection holds SW metadata lock on table to be altered.
65# The below statement should succeed. It should not
66# deadlock or end with ER_LOCK_DEADLOCK error.
67update t1 set c3=c3+1 where c2=4;
68# Unblock ALTER TABLE by committing transaction.
69commit;
70#
71connection con37346;
72# Reaping ALTER TABLE.
73#
74connection default;
75disconnect con37346;
76drop table t1;
77#
78# Bug#53798 OPTIMIZE TABLE breaks repeatable read
79#
80DROP TABLE IF EXISTS t1;
81CREATE TABLE t1 (a INT) engine=innodb;
82INSERT INTO t1 VALUES (1), (2), (3);
83connect  con1, localhost, root;
84START TRANSACTION WITH CONSISTENT SNAPSHOT;
85SELECT * FROM t1;
86a
871
882
893
90connection default;
91# This should block
92# Sending:
93OPTIMIZE TABLE t1;
94connection con1;
95SELECT * FROM t1;
96a
971
982
993
100COMMIT;
101connection default;
102# Reaping OPTIMIZE TABLE t1
103Table	Op	Msg_type	Msg_text
104test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
105test.t1	optimize	status	OK
106disconnect con1;
107DROP TABLE t1;
108#
109# Bug#49891 View DDL breaks REPEATABLE READ
110#
111DROP TABLE IF EXISTS t1, t2;
112DROP VIEW IF EXISTS v2;
113CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb;
114CREATE TABLE t2 ( f1 INTEGER );
115CREATE VIEW v1 AS SELECT 1 FROM t1;
116connect  con2, localhost, root;
117connect  con3, localhost, root;
118connection con3;
119LOCK TABLE t1 WRITE;
120connection default;
121START TRANSACTION;
122# Sending:
123SELECT * FROM v1;
124connection con2;
125# Waiting for 'SELECT * FROM v1' to sync in.
126# Sending:
127ALTER VIEW v1 AS SELECT 2 FROM t2;
128connection con3;
129# Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in.
130UNLOCK TABLES;
131connection default;
132# Reaping: SELECT * FROM v1
1331
134SELECT * FROM v1;
1351
136COMMIT;
137connection con2;
138# Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2
139connection default;
140DROP TABLE t1, t2;
141DROP VIEW v1;
142disconnect con2;
143disconnect con3;
144#
145# Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY
146#              KEY NO 0 FOR TABLE IN ERROR LOG
147#
148DROP TABLE IF EXISTS t1;
149connect  con1,localhost,root;
150connection default;
151CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB;
152INSERT INTO t1 VALUES (1, 12345);
153START TRANSACTION;
154SELECT * FROM t1;
155id	value
1561	12345
157connection con1;
158SET lock_wait_timeout=1;
159ALTER TABLE t1 ADD INDEX idx(value);
160ERROR HY000: Lock wait timeout exceeded; try restarting transaction
161ALTER TABLE t1 ADD INDEX idx(value);
162ERROR HY000: Lock wait timeout exceeded; try restarting transaction
163connection default;
164SELECT * FROM t1;
165id	value
1661	12345
167COMMIT;
168DROP TABLE t1;
169disconnect con1;
170set global innodb_lock_wait_timeout=@old_innodb_lock_wait_timeout;
171