Debian Codegraph @

mysql-test/suite/galera/t/mysql-wsrep#332.test

raw
1 --source include/galera_cluster.inc
2 --source include/have_innodb.inc
3 --source include/have_debug_sync.inc
4 --source include/galera_have_debug_sync.inc
5
6 # Open connection node_1a here, will use it later
7 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
8
9 #
10 # Test the scenario where a foreign key is added to an existing child table, and
11 # concurrently UPDATE the parent table so that it violates the constraint.
12 #
13 # We expect that ALTER TABLE ADD FOREIGN KEY adds a table level key on both
14 # parent and child table. And therefore we also expect the UPDATE to fail
15 # certification.
16 #
17 --connection node_1
18 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
19 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER) ENGINE=INNODB;
20
21 INSERT INTO p VALUES (1, 0);
22 INSERT INTO p VALUES (2, 0);
23
24 INSERT INTO c VALUES (1, 1);
25 INSERT INTO c VALUES (2, 2);
26
27 --let $mw_369_parent_query = UPDATE p SET f1 = f1 + 100
28 --let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id) REFERENCES p(f1)
29
30 --connection node_1
31 SET AUTOCOMMIT=ON;
32 START TRANSACTION;
33 --eval $mw_369_parent_query
34
35 #
36 # Block the $mw_369_child_query from node_2
37 #
38 # --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
39 --connection node_1a
40 SET SESSION wsrep_sync_wait = 0;
41 --let $galera_sync_point = apply_monitor_slave_enter_sync
42 --source include/galera_set_sync_point.inc
43
44 #
45 # insert client row, which will make it impossible to replay the
46 # delete on parent
47 #
48 --connection node_2
49 --eval $mw_369_child_query
50
51 #
52 # Wait until $mw_369_child_query from node_2 reaches the sync point and
53 # block the 'COMMIT' from node_1 before it certifies.
54 #
55 --connection node_1a
56 --source include/galera_wait_sync_point.inc
57 --source include/galera_clear_sync_point.inc
58
59 --connection node_1
60 --send COMMIT
61
62 --connection node_1a
63 --let $galera_sync_point = apply_monitor_slave_enter_sync
64 --source include/galera_signal_sync_point.inc
65 --source include/galera_clear_sync_point.inc
66
67 # Expect certification failure
68 --connection node_1
69 --error ER_LOCK_DEADLOCK
70 --reap
71
72 --connection node_2
73 SELECT * FROM p;
74 SELECT * FROM c;
75
76 DROP TABLE c;
77 DROP TABLE p;
78
79
80 #
81 # Same as above, except that two foreign keys pointing to different parent
82 # tables are added, p1 and p2. Concurrently UPDATE p1.
83 #
84 # Expect certification error on UPDATE.
85 #
86 --connection node_1
87 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
88 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
89 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
90
91 INSERT INTO p1 VALUES (1, 0), (2, 0);
92 INSERT INTO p2 VALUES (1, 0), (2, 0);
93
94 INSERT INTO c VALUES (1, 1, 1);
95 INSERT INTO c VALUES (2, 2, 2);
96
97 --let $mw_369_parent_query = UPDATE p1 SET f1 = f1 + 100
98 --let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1)
99
100 --connection node_1
101 SET AUTOCOMMIT=ON;
102 START TRANSACTION;
103 --eval $mw_369_parent_query
104
105 #
106 # Block the $mw_369_child_query from node_2
107 #
108 # --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
109 --connection node_1a
110 SET SESSION wsrep_sync_wait = 0;
111 --let $galera_sync_point = apply_monitor_slave_enter_sync
112 --source include/galera_set_sync_point.inc
113
114 #
115 # insert client row, which will make it impossible to replay the
116 # delete on parent
117 #
118 --connection node_2
119 --eval $mw_369_child_query
120
121 #
122 # Wait until $mw_369_child_query from node_2 reaches the sync point and
123 # block the 'COMMIT' from node_1 before it certifies.
124 #
125 --connection node_1a
126 --source include/galera_wait_sync_point.inc
127 --source include/galera_clear_sync_point.inc
128
129 --connection node_1
130 --send COMMIT
131
132 --connection node_1a
133 --let $galera_sync_point = apply_monitor_slave_enter_sync
134 --source include/galera_signal_sync_point.inc
135 --source include/galera_clear_sync_point.inc
136
137 # Expect certification failure
138 --connection node_1
139 --error ER_LOCK_DEADLOCK
140 --reap
141
142 --connection node_2
143 SELECT * FROM p1;
144 SELECT * FROM p2;
145 SELECT * FROM c;
146
147 DROP TABLE c;
148 DROP TABLE p1;
149 DROP TABLE p2;
150
151
152 #
153 # Same as above, except that UPDATE is on p2.
154 #
155 --connection node_1
156 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
157 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
158 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB;
159
160 INSERT INTO p1 VALUES (1, 0), (2, 0);
161 INSERT INTO p2 VALUES (1, 0), (2, 0);
162
163 INSERT INTO c VALUES (1, 1, 1);
164 INSERT INTO c VALUES (2, 2, 2);
165
166 --let $mw_369_parent_query = UPDATE p2 SET f1 = f1 + 100
167 --let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1)
168
169 --connection node_1
170 SET AUTOCOMMIT=ON;
171 START TRANSACTION;
172 --eval $mw_369_parent_query
173
174 #
175 # Block the $mw_369_child_query from node_2
176 #
177 # --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
178 --connection node_1a
179 SET SESSION wsrep_sync_wait = 0;
180 --let $galera_sync_point = apply_monitor_slave_enter_sync
181 --source include/galera_set_sync_point.inc
182
183 #
184 # insert client row, which will make it impossible to replay the
185 # delete on parent
186 #
187 --connection node_2
188 --eval $mw_369_child_query
189
190 #
191 # Wait until $mw_369_child_query from node_2 reaches the sync point and
192 # block the 'COMMIT' from node_1 before it certifies.
193 #
194 --connection node_1a
195 --source include/galera_wait_sync_point.inc
196 --source include/galera_clear_sync_point.inc
197
198 --connection node_1
199 --send COMMIT
200
201 --connection node_1a
202 --let $galera_sync_point = apply_monitor_slave_enter_sync
203 --source include/galera_signal_sync_point.inc
204 --source include/galera_clear_sync_point.inc
205
206 # Expect certification failure
207 --connection node_1
208 --error ER_LOCK_DEADLOCK
209 --reap
210
211 --connection node_2
212 SELECT * FROM p1;
213 SELECT * FROM p2;
214 SELECT * FROM c;
215
216 DROP TABLE c;
217 DROP TABLE p1;
218 DROP TABLE p2;