Debian Codegraph @

mysql-test/suite/ndb/r/ndb_metadata_upgrade_fk.result

raw
1 Backup from 7.5 restored
2 Restart server 1 to trigger schema distribution synchronization
3 # restart
4 Restart server 2 to trigger schema distribution synchronization
5 # restart
6 # Check schema and then perform DML and DDL on the tables
7 # Verify that parent has been installed in DD
8 SHOW CREATE TABLE parent;
9 Table Create Table
10 parent CREATE TABLE `parent` (
11 `c1` int NOT NULL,
12 `c2` int DEFAULT NULL,
13 PRIMARY KEY (`c1`),
14 UNIQUE KEY `p_ukc2` (`c2`)
15 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
16 SELECT * FROM parent;
17 c1 c2
18 1 10
19 2 20
20 # Check foreign key constraints
21 INSERT INTO parent VALUES (3, 30);
22 DELETE FROM parent WHERE c1 = 1;
23 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
24 SELECT * FROM parent;
25 c1 c2
26 1 10
27 2 20
28 3 30
29 # Verify that child has been installed in DD
30 SELECT * FROM information_schema.referential_constraints
31 WHERE constraint_schema='test'
32 AND table_name='child'
33 ORDER BY constraint_name ASC;
34 CONSTRAINT_CATALOG def
35 CONSTRAINT_SCHEMA test
36 CONSTRAINT_NAME c_fk1
37 UNIQUE_CONSTRAINT_CATALOG def
38 UNIQUE_CONSTRAINT_SCHEMA test
39 UNIQUE_CONSTRAINT_NAME PRIMARY
40 MATCH_OPTION NONE
41 UPDATE_RULE NO ACTION
42 DELETE_RULE NO ACTION
43 TABLE_NAME child
44 REFERENCED_TABLE_NAME parent
45 CONSTRAINT_CATALOG def
46 CONSTRAINT_SCHEMA test
47 CONSTRAINT_NAME c_fk2
48 UNIQUE_CONSTRAINT_CATALOG def
49 UNIQUE_CONSTRAINT_SCHEMA test
50 UNIQUE_CONSTRAINT_NAME p_ukc2
51 MATCH_OPTION NONE
52 UPDATE_RULE NO ACTION
53 DELETE_RULE NO ACTION
54 TABLE_NAME child
55 REFERENCED_TABLE_NAME parent
56 SHOW CREATE TABLE child;
57 Table Create Table
58 child CREATE TABLE `child` (
59 `c1` int DEFAULT NULL,
60 `c2` int DEFAULT NULL,
61 KEY `c_xc1` (`c1`),
62 KEY `c_xc2` (`c2`),
63 CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`),
64 CONSTRAINT `c_fk2` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
65 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
66 SELECT * FROM child;
67 c1 c2
68 1 10
69 1 20
70 2 10
71 2 20
72 # Check foreign key constraints
73 INSERT INTO child VALUES (3, 30);
74 INSERT INTO child VALUES (4, 40);
75 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
76 SELECT * FROM child;
77 c1 c2
78 1 10
79 1 20
80 2 10
81 2 20
82 3 30
83 # Verify that self_ref has been installed in DD
84 SELECT * FROM information_schema.referential_constraints
85 WHERE constraint_schema='test'
86 AND table_name='self_ref'
87 ORDER BY constraint_name ASC;
88 CONSTRAINT_CATALOG def
89 CONSTRAINT_SCHEMA test
90 CONSTRAINT_NAME sr_fk1
91 UNIQUE_CONSTRAINT_CATALOG def
92 UNIQUE_CONSTRAINT_SCHEMA test
93 UNIQUE_CONSTRAINT_NAME PRIMARY
94 MATCH_OPTION NONE
95 UPDATE_RULE SET DEFAULT
96 DELETE_RULE CASCADE
97 TABLE_NAME self_ref
98 REFERENCED_TABLE_NAME self_ref
99 SHOW CREATE TABLE self_ref;
100 Table Create Table
101 self_ref CREATE TABLE `self_ref` (
102 `c1` int NOT NULL,
103 `c2` int DEFAULT NULL,
104 UNIQUE KEY `sr_ukc1` (`c1`),
105 KEY `sr_fk1` (`c2`),
106 CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT
107 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
108 SELECT * FROM self_ref;
109 c1 c2
110 1 1
111 2 1
112 # Check foreign key constraints
113 INSERT INTO self_ref VALUES (3, 2);
114 INSERT INTO self_ref VALUES (4, 5);
115 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT)
116 DELETE FROM self_ref WHERE c1 = 1;
117 SELECT * FROM self_ref;
118 c1 c2
119 # Cleanup
120 DROP TABLE child;
121 DROP TABLE parent;
122 DROP TABLE self_ref;
123 Backup from 7.5 restored
124 # Test upgrade of the metadata during discovery from NDB
125 # Discover and verify table parent
126 SHOW CREATE TABLE parent;
127 Table Create Table
128 parent CREATE TABLE `parent` (
129 `c1` int NOT NULL,
130 `c2` int DEFAULT NULL,
131 PRIMARY KEY (`c1`),
132 UNIQUE KEY `p_ukc2` (`c2`)
133 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
134 SELECT * FROM parent;
135 c1 c2
136 1 10
137 2 20
138 # Check foreign key constraints
139 INSERT INTO parent VALUES (3, 30);
140 DELETE FROM parent WHERE c1 = 1;
141 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
142 SELECT * FROM parent;
143 c1 c2
144 1 10
145 2 20
146 3 30
147 # Discover and verify table child
148 SHOW CREATE TABLE child;
149 Table Create Table
150 child CREATE TABLE `child` (
151 `c1` int DEFAULT NULL,
152 `c2` int DEFAULT NULL,
153 KEY `c_xc1` (`c1`),
154 KEY `c_xc2` (`c2`),
155 CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`),
156 CONSTRAINT `c_fk2` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
157 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
158 SELECT * FROM information_schema.referential_constraints
159 WHERE constraint_schema='test'
160 AND table_name='child'
161 ORDER BY constraint_name ASC;
162 CONSTRAINT_CATALOG def
163 CONSTRAINT_SCHEMA test
164 CONSTRAINT_NAME c_fk1
165 UNIQUE_CONSTRAINT_CATALOG def
166 UNIQUE_CONSTRAINT_SCHEMA test
167 UNIQUE_CONSTRAINT_NAME PRIMARY
168 MATCH_OPTION NONE
169 UPDATE_RULE NO ACTION
170 DELETE_RULE NO ACTION
171 TABLE_NAME child
172 REFERENCED_TABLE_NAME parent
173 CONSTRAINT_CATALOG def
174 CONSTRAINT_SCHEMA test
175 CONSTRAINT_NAME c_fk2
176 UNIQUE_CONSTRAINT_CATALOG def
177 UNIQUE_CONSTRAINT_SCHEMA test
178 UNIQUE_CONSTRAINT_NAME p_ukc2
179 MATCH_OPTION NONE
180 UPDATE_RULE NO ACTION
181 DELETE_RULE NO ACTION
182 TABLE_NAME child
183 REFERENCED_TABLE_NAME parent
184 SELECT * FROM child;
185 c1 c2
186 1 10
187 1 20
188 2 10
189 2 20
190 # Check foreign key constraints
191 INSERT INTO child VALUES (3, 30);
192 INSERT INTO child VALUES (4, 40);
193 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
194 SELECT * FROM child;
195 c1 c2
196 1 10
197 1 20
198 2 10
199 2 20
200 3 30
201 # Discover and verify table self_ref
202 SHOW CREATE TABLE self_ref;
203 Table Create Table
204 self_ref CREATE TABLE `self_ref` (
205 `c1` int NOT NULL,
206 `c2` int DEFAULT NULL,
207 UNIQUE KEY `sr_ukc1` (`c1`),
208 KEY `sr_fk1` (`c2`),
209 CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT
210 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
211 SELECT * FROM information_schema.referential_constraints
212 WHERE constraint_schema='test'
213 AND table_name='self_ref'
214 ORDER BY constraint_name ASC;
215 CONSTRAINT_CATALOG def
216 CONSTRAINT_SCHEMA test
217 CONSTRAINT_NAME sr_fk1
218 UNIQUE_CONSTRAINT_CATALOG def
219 UNIQUE_CONSTRAINT_SCHEMA test
220 UNIQUE_CONSTRAINT_NAME PRIMARY
221 MATCH_OPTION NONE
222 UPDATE_RULE SET DEFAULT
223 DELETE_RULE CASCADE
224 TABLE_NAME self_ref
225 REFERENCED_TABLE_NAME self_ref
226 SELECT * FROM self_ref;
227 c1 c2
228 1 1
229 2 1
230 # Check foreign key constraints
231 INSERT INTO self_ref VALUES (3, 2);
232 INSERT INTO self_ref VALUES (4, 5);
233 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT)
234 DELETE FROM self_ref WHERE c1 = 1;
235 SELECT * FROM self_ref;
236 c1 c2
237 DROP TABLE child;
238 DROP TABLE parent;
239 DROP TABLE self_ref;
240 Backup from 7.5 restored
241 # During upgrades, the tables might get upgraded in
242 # any order. Test this by discovering parent and
243 # child in the reverse order and verifying the FKs.
244 SHOW CREATE TABLE child;
245 Table Create Table
246 child CREATE TABLE `child` (
247 `c1` int DEFAULT NULL,
248 `c2` int DEFAULT NULL,
249 KEY `c_xc1` (`c1`),
250 KEY `c_xc2` (`c2`),
251 CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`),
252 CONSTRAINT `c_fk2` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
253 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
254 SHOW CREATE TABLE parent;
255 Table Create Table
256 parent CREATE TABLE `parent` (
257 `c1` int NOT NULL,
258 `c2` int DEFAULT NULL,
259 PRIMARY KEY (`c1`),
260 UNIQUE KEY `p_ukc2` (`c2`)
261 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
262 SELECT * FROM parent;
263 c1 c2
264 1 10
265 2 20
266 SELECT * FROM child;
267 c1 c2
268 1 10
269 1 20
270 2 10
271 2 20
272 # Check foreign key constraints
273 SELECT * FROM information_schema.referential_constraints
274 WHERE constraint_schema='test'
275 AND table_name='child'
276 ORDER BY constraint_name ASC;
277 CONSTRAINT_CATALOG def
278 CONSTRAINT_SCHEMA test
279 CONSTRAINT_NAME c_fk1
280 UNIQUE_CONSTRAINT_CATALOG def
281 UNIQUE_CONSTRAINT_SCHEMA test
282 UNIQUE_CONSTRAINT_NAME PRIMARY
283 MATCH_OPTION NONE
284 UPDATE_RULE NO ACTION
285 DELETE_RULE NO ACTION
286 TABLE_NAME child
287 REFERENCED_TABLE_NAME parent
288 CONSTRAINT_CATALOG def
289 CONSTRAINT_SCHEMA test
290 CONSTRAINT_NAME c_fk2
291 UNIQUE_CONSTRAINT_CATALOG def
292 UNIQUE_CONSTRAINT_SCHEMA test
293 UNIQUE_CONSTRAINT_NAME p_ukc2
294 MATCH_OPTION NONE
295 UPDATE_RULE NO ACTION
296 DELETE_RULE NO ACTION
297 TABLE_NAME child
298 REFERENCED_TABLE_NAME parent
299 INSERT INTO parent VALUES (3, 30);
300 DELETE FROM parent WHERE c1 = 1;
301 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
302 INSERT INTO child VALUES (3, 30);
303 INSERT INTO child VALUES (4, 40);
304 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
305 SELECT * FROM parent;
306 c1 c2
307 1 10
308 2 20
309 3 30
310 SELECT * FROM child;
311 c1 c2
312 1 10
313 1 20
314 2 10
315 2 20
316 3 30
317 # Cleanup
318 DROP TABLE child;
319 DROP TABLE parent;
320 DROP TABLE self_ref;