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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
include/master-slave.inc
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
########################################################################
# Environment
########################################################################
connection master;
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');
connect conn1, 127.0.0.1,root,,;
connect conn2, 127.0.0.1,root,,;
########################################################################
# Testing ER_LOCK_WAIT_TIMEOUT
########################################################################
connection conn1;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'yellow 2' WHERE i = 3;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
connection conn2;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE IGNORE 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");
connection conn1;
COMMIT;
connection conn2;
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 2' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE IGNORE 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 # # ROLLBACK
connection conn1;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'gray 2' WHERE i = 3;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
connection conn2;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE IGNORE 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");
connection conn1;
COMMIT;
connection conn2;
COMMIT;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 2' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE IGNORE 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 # Xid # # COMMIT /* XID */
connection conn1;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'yellow 1' WHERE i = 3;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
connection conn2;
SET AUTOCOMMIT = 0;
UPDATE IGNORE 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");
connection conn1;
COMMIT;
connection conn2;
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 1' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE IGNORE 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 # # ROLLBACK
connection conn1;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'gray 1' WHERE i = 3;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
connection conn2;
SET AUTOCOMMIT = 0;
UPDATE IGNORE 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");
connection conn1;
COMMIT;
connection conn2;
COMMIT;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 1' WHERE i = 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; UPDATE IGNORE 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 # Xid # # COMMIT /* XID */
connection master;
connection slave;
include/rpl_diff.inc
include/rpl_diff.inc
########################################################################
# Cleanup
########################################################################
connection master;
DROP TRIGGER tr;
DROP TABLE t;
DROP TABLE n;
connection slave;
connection master;
disconnect conn1;
disconnect conn2;
include/rpl_end.inc
|
