Debian Codegraph @

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

raw
1 create table `rows`(a int primary key) engine=ndb;
2 select count(a) from `rows`;
3 count(a)
4 1000
5 create table p1 (name varchar(255),
6 id int(11) auto_increment,
7 parent_id int,
8 parent_c1 int,
9 parent_c2 int,
10 primary key(id),
11 unique(name),
12 key k2(parent_id),
13 unique(parent_c1, parent_c2)
14 )engine = ndb;
15 Warnings:
16 Warning 1681 Integer display width is deprecated and will be removed in a future release.
17 create table c1(c1 int AUTO_INCREMENT,
18 id int(11),
19 primary key(c1),
20 unique key abc (id),
21 key (c1),
22 constraint fk1 foreign key (id) references p1 (id)
23 ) engine=ndb;
24 Warnings:
25 Warning 1681 Integer display width is deprecated and will be removed in a future release.
26 create table c2(c2 varchar(100),
27 id int(11),
28 parent_temp int,
29 parent_rc1 int,
30 parent_rc2 int,
31 parent_rc3 int,
32 primary key(c2),
33 unique key abc(c2),
34 unique key abd(parent_temp),
35 constraint c2_fk1 foreign key (id) references p1 (id),
36 constraint c2_fk2 foreign key (parent_rc1, parent_rc2) references p1(parent_c1, parent_c2),
37 constraint c2_fk3 foreign key (parent_rc3) references c1(id)
38 ) engine=ndb;
39 Warnings:
40 Warning 1681 Integer display width is deprecated and will be removed in a future release.
41 insert into p1(parent_id) select a from `rows`;
42 select count(1) from test.p1;
43 count(1)
44 1000
45 -- Show Fks working
46 insert into test.c1(id) select id from p1;
47 delete from test.c1;
48 insert into test.c1(id) select id from p1;
49 delete from test.c1;
50 delete from test.p1;
51 insert into p1(parent_id) select a from `rows`;
52 insert into test.c1(id) select id from p1;
53 delete from test.c1;
54 Check interaction of inplace alters and restarts
55 create table parent (parent_pk int,
56 parent_uk int,
57 b int,
58 primary key (parent_pk),
59 unique(parent_uk)
60 ) engine=ndb;
61 create table child (child_pk int,
62 child_uk int,
63 child_ord int,
64 data int,
65 primary key(child_pk),
66 unique(child_uk),
67 key(child_ord),
68 constraint pkpk foreign key (child_pk) references parent (parent_pk), # PK->PK
69 constraint pkuk foreign key (child_pk) references parent (parent_uk), # PK->UK
70 constraint ukpk foreign key (child_uk) references parent (parent_pk), # UK->PK
71 constraint ukuk foreign key (child_uk) references parent (parent_uk), # UK->UK
72 constraint ordpk foreign key (child_ord) references parent (parent_pk), # ORD -> PK
73 constraint orduk foreign key (child_ord) references parent (parent_uk) # ORD -> UK
74 ) engine=ndb;
75 insert into parent values
76 (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8), (9,9,9), (10,10,10),
77 (11,100,11),
78 (200,12,200);
79 Succesful inserts
80 insert into child values
81 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6), (7,7,7,7), (8,8,8,8), (9,9,9,9);
82 FK checks working
83 insert into child values (20, 10, 10, 10);
84 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
85 insert into child values (11, 11, 10, 10);
86 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
87 insert into child values (10, 12, 10, 10);
88 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
89 insert into child values (10, 200, 10, 10);
90 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
91 insert into child values (10, 10, 12, 10);
92 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
93 insert into child values (10, 10, 200, 10);
94 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
95 Capture initial schema versions for parent
96 and child tables and unique indices involved
97 in foreign keys
98 Now alter the tables and indices inplace
99 Existing FKs should be unaffected.
100 Alter parent
101 Rename table x 2
102 rename table parent to guardian;
103 rename table guardian to parent;
104 Parent metadata version diff
105 Parent table version diff
106 major_diff minor_diff
107 0 2
108 Parent unique version diff
109 major_diff minor_diff
110 0 0
111 Add a column
112 alter table parent add column extra int;
113 Warnings:
114 Warning 1478 Converted FIXED field 'extra' to DYNAMIC to enable online ADD COLUMN
115 Parent metadata version diff
116 Parent table version diff
117 major_diff minor_diff
118 0 3
119 Parent unique version diff
120 major_diff minor_diff
121 0 0
122 Fiddle with READ_BACKUP
123 alter table parent comment='NDB_TABLE=READ_BACKUP=0';
124 alter table parent comment='NDB_TABLE=READ_BACKUP=1';
125 Parent metadata version diff
126 Parent table version diff
127 major_diff minor_diff
128 0 5
129 Parent unique version diff
130 major_diff minor_diff
131 0 2
132 Alter child
133 Rename table
134 rename table child to junior;
135 rename table junior to child;
136 Child metadata diff
137 Child table version diff
138 major_diff minor_diff
139 0 2
140 Child unique version diff
141 major_diff minor_diff
142 0 0
143 Child ordered version diff
144 major_diff minor_diff
145 0 0
146 Rename a column
147 alter table child rename column data to datum;
148 Child metadata diff
149 Child table version diff
150 major_diff minor_diff
151 0 3
152 Child unique version diff
153 major_diff minor_diff
154 0 0
155 Child ordered version diff
156 major_diff minor_diff
157 0 0
158 Add a column
159 alter table child add column extra int;
160 Warnings:
161 Warning 1478 Converted FIXED field 'extra' to DYNAMIC to enable online ADD COLUMN
162 Child metadata diff
163 Child table version diff
164 major_diff minor_diff
165 0 4
166 Child unique version diff
167 major_diff minor_diff
168 0 0
169 Child ordered version diff
170 major_diff minor_diff
171 0 0
172 Fiddle with readbackup
173 alter table child comment='NDB_TABLE=READ_BACKUP=0';
174 alter table child comment='NDB_TABLE=READ_BACKUP=1';
175 Child metadata diff
176 Child table version diff
177 major_diff minor_diff
178 0 6
179 Child unique version diff
180 major_diff minor_diff
181 0 2
182 Child ordered version diff
183 major_diff minor_diff
184 0 2
185 -- Normal NR
186 --- Wait for node to stop
187 --- Show FKs working
188 insert into test.c1(id) select id from p1;
189 delete from test.c1;
190 insert into test.c1(id) select id from p1;
191 delete from test.c1;
192 delete from test.p1;
193 insert into p1(parent_id) select a from `rows`;
194 insert into test.c1(id) select id from p1;
195 delete from test.c1;
196 FK checks working
197 insert into child values (20, 10, 10, 10, 10);
198 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
199 insert into child values (11, 11, 10, 10, 10);
200 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
201 insert into child values (10, 12, 10, 10, 10);
202 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
203 insert into child values (10, 200, 10, 10, 10);
204 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
205 insert into child values (10, 10, 12, 10, 10);
206 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
207 insert into child values (10, 10, 200, 10, 10);
208 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
209 --- Startup node again
210 --- Wait for node to recover
211 --- Show FKs working
212 insert into test.c1(id) select id from p1;
213 delete from test.c1;
214 insert into test.c1(id) select id from p1;
215 delete from test.c1;
216 delete from test.p1;
217 insert into p1(parent_id) select a from `rows`;
218 insert into test.c1(id) select id from p1;
219 delete from test.c1;
220 FK checks working
221 insert into child values (20, 10, 10, 10, 10);
222 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
223 insert into child values (11, 11, 10, 10, 10);
224 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
225 insert into child values (10, 12, 10, 10, 10);
226 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
227 insert into child values (10, 200, 10, 10, 10);
228 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
229 insert into child values (10, 10, 12, 10, 10);
230 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
231 insert into child values (10, 10, 200, 10, 10);
232 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
233 -- Initial NR
234 --- Wait for node to stop
235 --- Show FKs working
236 insert into test.c1(id) select id from p1;
237 delete from test.c1;
238 insert into test.c1(id) select id from p1;
239 delete from test.c1;
240 delete from test.p1;
241 insert into p1(parent_id) select a from `rows`;
242 insert into test.c1(id) select id from p1;
243 delete from test.c1;
244 FK checks working
245 insert into child values (20, 10, 10, 10, 10);
246 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
247 insert into child values (11, 11, 10, 10, 10);
248 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
249 insert into child values (10, 12, 10, 10, 10);
250 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
251 insert into child values (10, 200, 10, 10, 10);
252 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
253 insert into child values (10, 10, 12, 10, 10);
254 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
255 insert into child values (10, 10, 200, 10, 10);
256 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
257 --- Startup node again
258 --- Wait for node to recover
259 --- Show FKs working
260 insert into test.c1(id) select id from p1;
261 delete from test.c1;
262 insert into test.c1(id) select id from p1;
263 delete from test.c1;
264 delete from test.p1;
265 insert into p1(parent_id) select a from `rows`;
266 insert into test.c1(id) select id from p1;
267 delete from test.c1;
268 FK checks working
269 insert into child values (20, 10, 10, 10, 10);
270 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
271 insert into child values (11, 11, 10, 10, 10);
272 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
273 insert into child values (10, 12, 10, 10, 10);
274 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
275 insert into child values (10, 200, 10, 10, 10);
276 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
277 insert into child values (10, 10, 12, 10, 10);
278 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
279 insert into child values (10, 10, 200, 10, 10);
280 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
281 -- System restart
282 --- Wait for node to stop
283 --- Startup nodes again
284 --- Wait for nodes to recover
285 --- Wait to establish connection
286 --- Show FKs working
287 insert into test.c1(id) select id from p1;
288 delete from test.c1;
289 insert into test.c1(id) select id from p1;
290 delete from test.c1;
291 delete from test.p1;
292 insert into p1(parent_id) select a from `rows`;
293 insert into test.c1(id) select id from p1;
294 delete from test.c1;
295 FK checks working
296 insert into child values (20, 10, 10, 10, 10);
297 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkpk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
298 insert into child values (11, 11, 10, 10, 10);
299 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `pkuk` FOREIGN KEY (`child_pk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
300 insert into child values (10, 12, 10, 10, 10);
301 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukpk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
302 insert into child values (10, 200, 10, 10, 10);
303 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ukuk` FOREIGN KEY (`child_uk`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
304 insert into child values (10, 10, 12, 10, 10);
305 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `ordpk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
306 insert into child values (10, 10, 200, 10, 10);
307 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `orduk` FOREIGN KEY (`child_ord`) REFERENCES `parent` (`parent_uk`) ON DELETE NO ACTION ON UPDATE NO ACTION)
308 delete from test.p1;
309 drop table test.c2;
310 drop table test.c1;
311 drop table test.p1;
312 drop table test.`rows`;
313 drop table test.child;
314 drop table test.parent;