1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
########################################################################
# Environment
########################################################################
CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb;
CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam;
CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW
BEGIN
INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) );
END |
INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');
########################################################################
# Testing ER_LOCK_WAIT_TIMEOUT
########################################################################
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'yellow 2' WHERE i = 3;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'magenta 2' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (5 + (2 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 2' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'magenta 2' WHERE f = 'red'
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (2 * 10),"brown")
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # ROLLBACK
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'gray 2' WHERE i = 3;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'dark blue 2' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (6 + (2 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
COMMIT;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 2' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'dark blue 2' WHERE f = 'red'
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (2 * 10),"brown")
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Xid # # COMMIT /* XID */
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'yellow 1' WHERE i = 3;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'magenta 1' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (5 + (1 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 1' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'magenta 1' WHERE f = 'red'
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (1 * 10),"brown")
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # ROLLBACK
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'gray 1' WHERE i = 3;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'dark blue 1' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (6 + (1 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
COMMIT;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 1' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'dark blue 1' WHERE f = 'red'
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (1 * 10),"brown")
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Xid # # COMMIT /* XID */
source include/diff_master_slave.inc;
source include/diff_master_slave.inc;
########################################################################
# Cleanup
########################################################################
DROP TRIGGER tr;
DROP TABLE t;
DROP TABLE n;
|
