Debian Codegraph @

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

raw
1 #setup tables
2 create table parent(
3 a int unique,
4 b int unique
5 ) engine ndb;
6 insert into parent values (1, 2), (2, 1);
7 create table child(
8 ref int
9 ) engine ndb;
10 #Testing create FK, using copy algorithm
11 #cache parent table in server1
12 select * from parent order by a;
13 a b
14 1 2
15 2 1
16 #now run the DDL query in server2
17 alter table child add
18 constraint fk1 foreign key(ref) references parent(a),
19 algorithm = copy;
20 #check if the parent metadata has been reset in server1
21 #altering column should fail with error
22 alter table parent
23 change column a a varchar(64) not null;
24 ERROR HY000: Referencing column 'ref' and referenced column 'a' in foreign key constraint 'fk1' are incompatible.
25 #Testing drop FK, using inplace algorithm
26 #cache parent table in server1
27 select * from parent order by a;
28 a b
29 1 2
30 2 1
31 #now run the DDL query in server2
32 alter table child drop foreign key fk1, algorithm=inplace;
33 #check if the parent metadata has been reset in server1
34 #altering column should pass
35 alter table parent
36 change column a a varchar(64) not null;
37 #check parent's new definition
38 show create table parent;
39 Table Create Table
40 parent CREATE TABLE `parent` (
41 `a` varchar(64) NOT NULL,
42 `b` int DEFAULT NULL,
43 UNIQUE KEY `a` (`a`),
44 UNIQUE KEY `b` (`b`)
45 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
46 #restore parent to old definition
47 alter table parent change column a a int;
48 #restore fk for further testing
49 alter table child add constraint fk1 foreign key(ref) references parent(a);
50 #Testing drop child table
51 #cache parent table in server1
52 select * from parent order by a;
53 a b
54 1 2
55 2 1
56 #now run the DDL query in server2
57 drop table child;
58 #check if the parent metadata has been reset in server1
59 #altering column should pass
60 alter table parent
61 change column a a varchar(64) not null;
62 #check parent's new definition
63 show create table parent;
64 Table Create Table
65 parent CREATE TABLE `parent` (
66 `a` varchar(64) NOT NULL,
67 `b` int DEFAULT NULL,
68 UNIQUE KEY `a` (`a`),
69 UNIQUE KEY `b` (`b`)
70 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
71 #restore parent to old definition
72 alter table parent change column a a int;
73 #Testing create table
74 #cache parent table in server1
75 select * from parent order by a;
76 a b
77 1 2
78 2 1
79 #now run the DDL query in server2
80 create table child(
81 ref int,
82 constraint fk1 foreign key (ref) references parent(a)
83 ) engine ndb;
84 #check if the parent metadata has been reset in server1
85 #altering column should fail with error
86 alter table parent
87 change column a a varchar(64) not null;
88 ERROR HY000: Referencing column 'ref' and referenced column 'a' in foreign key constraint 'fk1' are incompatible.
89 #Testing multiple alter FK create/drops : I
90 #cache parent table in server1
91 select * from parent order by a;
92 a b
93 1 2
94 2 1
95 #now run the DDL query in server2
96 alter table child
97 drop foreign key fk1,
98 add constraint fk2 foreign key (ref) references parent(b);
99 #check if the parent metadata has been reset in server1
100 #altering column should pass
101 alter table parent
102 change column a a varchar(64) not null;
103 #check parent's new definition
104 show create table parent;
105 Table Create Table
106 parent CREATE TABLE `parent` (
107 `a` varchar(64) NOT NULL,
108 `b` int DEFAULT NULL,
109 UNIQUE KEY `a` (`a`),
110 UNIQUE KEY `b` (`b`)
111 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
112 #restore parent to old definition
113 alter table parent change column a a int;
114 #setup for child referring multiple parents
115 create table parent2(
116 a int unique
117 ) engine ndb;
118 show create table child;
119 Table Create Table
120 child CREATE TABLE `child` (
121 `ref` int DEFAULT NULL,
122 KEY `fk2` (`ref`),
123 CONSTRAINT `fk2` FOREIGN KEY (`ref`) REFERENCES `parent` (`b`)
124 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
125 alter table child
126 drop foreign key fk2,
127 add constraint fk1 foreign key (ref) references parent(a);
128 #Testing multiple alter FK create/drops : II
129 #This also tests the fix for Bug#27646160
130 #Also cache parent2
131 select * from parent2;
132 a
133 #cache parent table in server1
134 select * from parent order by a;
135 a b
136 1 2
137 2 1
138 #now run the DDL query in server2
139 alter table child
140 drop foreign key fk1,
141 add constraint fk2 foreign key (ref) references parent2(a);
142 #check if the parent metadata has been reset in server1
143 #altering column should pass
144 alter table parent
145 change column a a varchar(64) not null;
146 #check parent's new definition
147 show create table parent;
148 Table Create Table
149 parent CREATE TABLE `parent` (
150 `a` varchar(64) NOT NULL,
151 `b` int DEFAULT NULL,
152 UNIQUE KEY `a` (`a`),
153 UNIQUE KEY `b` (`b`)
154 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
155 #restore parent to old definition
156 alter table parent change column a a int;
157 #altering column in parent2 should fail with error
158 flush tables parent2;
159 alter table parent2
160 change column a a varchar(64) not null;
161 ERROR HY000: Referencing column 'ref' and referenced column 'a' in foreign key constraint 'fk2' are incompatible.
162 #setup to test drop database
163 drop table child;
164 create database test1;
165 create table test1.child(
166 ref1 int,
167 ref2 int,
168 foreign key fk1(ref1) references test.parent(a),
169 foreign key fk2(ref2) references test.parent2(a)
170 ) engine ndb;
171 #Testing drop database
172 #cache parent2
173 select * from parent2;
174 a
175 #cache parent table in server1
176 select * from parent order by a;
177 a b
178 1 2
179 2 1
180 #now run the DDL query in server2
181 drop database test1;
182 #check if the parent metadata has been reset in server1
183 #altering column should pass
184 alter table parent
185 change column a a varchar(64) not null;
186 #check parent's new definition
187 show create table parent;
188 Table Create Table
189 parent CREATE TABLE `parent` (
190 `a` varchar(64) NOT NULL,
191 `b` int DEFAULT NULL,
192 UNIQUE KEY `a` (`a`),
193 UNIQUE KEY `b` (`b`)
194 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
195 #restore parent to old definition
196 alter table parent change column a a int;
197 #altering column in parent2 should also pass
198 flush tables parent2;
199 alter table parent2
200 change column a a varchar(64) not null;
201 #cleanup
202 drop table parent, parent2;
203 #setup to test mock table resolution
204 set foreign_key_checks=0;
205 create table child (
206 a int,
207 foreign key fk1(a) references parent(a)
208 ) engine ndb;
209 create table parent (
210 a int unique
211 ) engine ndb;
212 insert into parent values (1), (2);
213 set foreign_key_checks=1;
214 #Now drop child and test
215 #cache parent table in server1
216 select * from parent order by a;
217 a
218 1
219 2
220 #now run the DDL query in server2
221 drop table child;
222 #check if the parent metadata has been reset in server1
223 #altering column should pass
224 alter table parent
225 change column a a varchar(64) not null;
226 #check parent's new definition
227 show create table parent;
228 Table Create Table
229 parent CREATE TABLE `parent` (
230 `a` varchar(64) NOT NULL,
231 UNIQUE KEY `a` (`a`)
232 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
233 #restore parent to old definition
234 alter table parent change column a a int;
235 #cleanup
236 drop table parent;