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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
--source include/galera_cluster.inc
--source include/have_innodb.inc
--source include/have_debug_sync.inc
--source include/galera_have_debug_sync.inc
# Open connection node_1a here, will use it later
--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
#
# Test the scenario where a foreign key is added to an existing child table, and
# concurrently UPDATE the parent table so that it violates the constraint.
#
# We expect that ALTER TABLE ADD FOREIGN KEY adds a table level key on both
# parent and child table. And therefore we also expect the UPDATE to fail
# certification.
#
--connection node_1
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER) ENGINE=INNODB;
INSERT INTO p VALUES (1, 0);
INSERT INTO p VALUES (2, 0);
INSERT INTO c VALUES (1, 1);
INSERT INTO c VALUES (2, 2);
--let $mw_369_parent_query = UPDATE p SET f1 = f1 + 100
--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id) REFERENCES p(f1)
--connection node_1
SET AUTOCOMMIT=ON;
START TRANSACTION;
--eval $mw_369_parent_query
#
# Block the $mw_369_child_query from node_2
#
# --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connection node_1a
SET SESSION wsrep_sync_wait = 0;
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_set_sync_point.inc
#
# insert client row, which will make it impossible to replay the
# delete on parent
#
--connection node_2
--eval $mw_369_child_query
#
# Wait until $mw_369_child_query from node_2 reaches the sync point and
# block the 'COMMIT' from node_1 before it certifies.
#
--connection node_1a
--source include/galera_wait_sync_point.inc
--source include/galera_clear_sync_point.inc
--connection node_1
--send COMMIT
--connection node_1a
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_signal_sync_point.inc
--source include/galera_clear_sync_point.inc
# Expect certification failure
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p;
#
# Same as above, except that two foreign keys pointing to different parent
# tables are added, p1 and p2. Concurrently UPDATE p1.
#
# Expect certification error on UPDATE.
#
--connection node_1
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
INSERT INTO p1 VALUES (1, 0), (2, 0);
INSERT INTO p2 VALUES (1, 0), (2, 0);
INSERT INTO c VALUES (1, 1, 1);
INSERT INTO c VALUES (2, 2, 2);
--let $mw_369_parent_query = UPDATE p1 SET f1 = f1 + 100
--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1)
--connection node_1
SET AUTOCOMMIT=ON;
START TRANSACTION;
--eval $mw_369_parent_query
#
# Block the $mw_369_child_query from node_2
#
# --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connection node_1a
SET SESSION wsrep_sync_wait = 0;
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_set_sync_point.inc
#
# insert client row, which will make it impossible to replay the
# delete on parent
#
--connection node_2
--eval $mw_369_child_query
#
# Wait until $mw_369_child_query from node_2 reaches the sync point and
# block the 'COMMIT' from node_1 before it certifies.
#
--connection node_1a
--source include/galera_wait_sync_point.inc
--source include/galera_clear_sync_point.inc
--connection node_1
--send COMMIT
--connection node_1a
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_signal_sync_point.inc
--source include/galera_clear_sync_point.inc
# Expect certification failure
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p1;
SELECT * FROM p2;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p1;
DROP TABLE p2;
#
# Same as above, except that UPDATE is on p2.
#
--connection node_1
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
INSERT INTO p1 VALUES (1, 0), (2, 0);
INSERT INTO p2 VALUES (1, 0), (2, 0);
INSERT INTO c VALUES (1, 1, 1);
INSERT INTO c VALUES (2, 2, 2);
--let $mw_369_parent_query = UPDATE p2 SET f1 = f1 + 100
--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1)
--connection node_1
SET AUTOCOMMIT=ON;
START TRANSACTION;
--eval $mw_369_parent_query
#
# Block the $mw_369_child_query from node_2
#
# --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connection node_1a
SET SESSION wsrep_sync_wait = 0;
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_set_sync_point.inc
#
# insert client row, which will make it impossible to replay the
# delete on parent
#
--connection node_2
--eval $mw_369_child_query
#
# Wait until $mw_369_child_query from node_2 reaches the sync point and
# block the 'COMMIT' from node_1 before it certifies.
#
--connection node_1a
--source include/galera_wait_sync_point.inc
--source include/galera_clear_sync_point.inc
--connection node_1
--send COMMIT
--connection node_1a
--let $galera_sync_point = apply_monitor_slave_enter_sync
--source include/galera_signal_sync_point.inc
--source include/galera_clear_sync_point.inc
# Expect certification failure
--connection node_1
--error ER_LOCK_DEADLOCK
--reap
--connection node_2
SELECT * FROM p1;
SELECT * FROM p2;
SELECT * FROM c;
DROP TABLE c;
DROP TABLE p1;
DROP TABLE p2;
|
