Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_alter.test

raw
1 -- source include/have_ndb.inc
2
3 ###
4 ### PK vs PK
5 ###
6 create table parent (
7 a int primary key auto_increment,
8 b int not null,
9 c int not null,
10 unique(b) using hash,
11 index(c)) engine = ndb;
12
13 create table child (
14 a int primary key auto_increment,
15 b int not null,
16 c int not null,
17 unique(b) using hash,
18 index(c)) engine = ndb;
19
20 # Parent pk
21 # child pk, uk, oi
22 alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a);
23 alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(a);
24 alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(a);
25
26 --disable_warnings
27 show create table child;
28 --enable_warnings
29
30 alter table child algorithm=inplace, drop foreign key fk1;
31
32 --disable_warnings
33 show create table child;
34 --enable_warnings
35
36 alter table child algorithm=inplace, drop foreign key fk2;
37
38 --disable_warnings
39 show create table child;
40 --enable_warnings
41
42 alter table child algorithm=inplace, drop foreign key fk3;
43
44 --disable_warnings
45 show create table child;
46 --enable_warnings
47
48 drop table parent, child;
49
50 #
51 #
52 #
53 CREATE TABLE bug46599a (
54 b VARCHAR(2), PRIMARY KEY(b)) ENGINE=ndb;
55
56 CREATE TABLE bug46599b (b VARCHAR(2),
57 CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES bug46599a (b)) ENGINE=Ndb;
58
59 INSERT INTO bug46599a VALUES ('b');
60 INSERT INTO bug46599b VALUES ('b'), (NULL);
61 --error ER_NO_REFERENCED_ROW_2
62 INSERT INTO bug46599b VALUES ('');
63
64 --sorted_result
65 SELECT * FROM bug46599a;
66 --sorted_result
67 SELECT * FROM bug46599b;
68
69 --error ER_FK_COLUMN_CANNOT_CHANGE
70 ALTER TABLE bug46599b MODIFY COLUMN b VARCHAR(2) NOT NULL DEFAULT '';
71
72 --sorted_result
73 SELECT * FROM bug46599b;
74
75 DROP TABLE bug46599b,bug46599a;
76
77 #
78 # bug#16912989 - part I
79 #
80 # copy_fk_for_offline_alter must use column name (not column number)
81 # in determining columns for newly created FK's
82 # This as column number change with DROP COLUMN
83 #
84 create table t1(
85 col1 int not null primary key,
86 col2 int not null,
87 col3 varchar(35) not null,
88 unique name0 (col2),
89 unique name1 (col3)
90 ) engine=ndb;
91
92 create table t2(
93 col1 int not null unique,
94 col2 int not null,
95 col3 varchar(35),
96 constraint fk1 foreign key (col3) references t1(col3)
97 ) engine=ndb;
98
99 ALTER TABLE t1 DROP COLUMN col2;
100
101 drop table t2, t1;
102
103 #
104 # bug#16912989 - part II
105 #
106 # When offline altering a parent table, the on the child recreated FK's
107 # can't be verified since the copying of data to the new parent
108 # is done later
109 #
110 let $i=2;
111 while ($i)
112 {
113 eval set @i=$i;
114 let $engine=`select if(@i=1,'ndb','innodb')`;
115
116 eval create table t1(
117 col1 int not null primary key,
118 col2 int not null unique,
119 col3 varchar(35)
120 ) engine=$engine;
121
122 eval create table t2(
123 col1 int not null unique,
124 col2 int not null,
125 col3 varchar(35),
126 constraint fk1 foreign key (col2) references t1(col1)
127 ) engine=$engine;
128
129 insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl');
130 insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl');
131
132 --echo # Attempt to drop col1, should give error
133 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
134 ALTER TABLE t1 DROP COLUMN col1;
135
136 --echo # Attempt to modify col1, should give error
137 --error ER_FK_INCOMPATIBLE_COLUMNS
138 ALTER TABLE t1 MODIFY COLUMN col1 bigint;
139
140 --echo # Drop the column t1.col3
141 ALTER TABLE t1 DROP COLUMN col3;
142
143 --echo # Drop t2.col2
144 --error 1553, 1828
145 ALTER TABLE t2 DROP COLUMN col2;
146
147 drop table t2, t1;
148 dec $i;
149 }
150
151 --echo #
152 --echo # Test non-auto partitioning
153 --echo #
154 CREATE TABLE t1
155 (col1 int NOT NULL PRIMARY KEY,
156 col2 int NOT NULL,
157 col3 varchar(35) NOT NULL,
158 UNIQUE KEY name0 (col2),
159 UNIQUE KEY name1 (col3)
160 )
161 ENGINE = NDB;
162
163 CREATE TABLE t2
164 (col1 int NOT NULL UNIQUE,
165 col2 int NOT NULL,
166 col3 varchar(35),
167 CONSTRAINT fk1 FOREIGN KEY (col3) REFERENCES t1 (col3)
168 )
169 ENGINE = NDB;
170
171 --disable_warnings
172 SHOW CREATE TABLE t1;
173 SHOW CREATE TABLE t2;
174 --enable_warnings
175 ALTER TABLE t1 PARTITION BY KEY ();
176 --disable_warnings
177 SHOW CREATE TABLE t1;
178 --enable_warnings
179 ALTER TABLE t1 PARTITION BY KEY (col1);
180 --disable_warnings
181 SHOW CREATE TABLE t1;
182 --enable_warnings
183 ALTER TABLE t1 REMOVE PARTITIONING;
184 --disable_warnings
185 SHOW CREATE TABLE t1;
186 --enable_warnings
187
188 ALTER TABLE t2 PARTITION BY KEY ();
189 --disable_warnings
190 SHOW CREATE TABLE t2;
191 --enable_warnings
192 ALTER TABLE t2 PARTITION BY KEY (col1);
193 --disable_warnings
194 SHOW CREATE TABLE t2;
195 --enable_warnings
196 ALTER TABLE t2 REMOVE PARTITIONING;
197 --disable_warnings
198 SHOW CREATE TABLE t2;
199 --enable_warnings
200 # Parent fk dependence
201 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
202 ALTER TABLE t1 RENAME COLUMN col3 TO col3_new;
203 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
204 ALTER TABLE t1 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
205 # Child fk dependence
206 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
207 ALTER TABLE t2 RENAME COLUMN col3 TO col3_new;
208 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
209 ALTER TABLE t2 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
210 --disable_warnings
211 SHOW CREATE TABLE t1;
212 SHOW CREATE TABLE t2;
213 --enable_warnings
214
215 DROP TABLE t2, t1;
216
217 --echo #
218 --echo # Bug#30233405 SELF REFERENTIAL FKS ARE LOST WHEN THE TABLE IS ALTERED USING COPY ALGORITHM
219 --echo #
220
221 --echo # Setup
222 CREATE TABLE self_ref (
223 pk INT PRIMARY KEY,
224 parent_col INT UNIQUE,
225 child_col INT,
226 FOREIGN KEY fk1(child_col) REFERENCES self_ref(parent_col)
227 ) ENGINE NDB;
228
229 SHOW CREATE TABLE self_ref;
230 INSERT INTO self_ref VALUES(1, 1, NULL);
231 INSERT INTO self_ref VALUES(2, 2, 1);
232 --error ER_NO_REFERENCED_ROW_2
233 INSERT INTO self_ref VALUES(3, 3, 6);
234 --error ER_ROW_IS_REFERENCED_2
235 DELETE FROM self_ref WHERE parent_col = 1;
236
237 --echo # Perform a COPY ALTER
238 # Turn off foreign key checks during ALTER as the rows might not be
239 # copied in order and hence might not be FK consistent.
240 SET foreign_key_checks= 0;
241 ALTER TABLE self_ref
242 ADD COLUMN dummy INT,
243 ALGORITHM=COPY;
244 SET foreign_key_checks= 1;
245
246 --echo # Verify the foreign keys
247 SHOW CREATE TABLE self_ref;
248 INSERT INTO self_ref VALUES(3, 3, 1, 0);
249 --error ER_NO_REFERENCED_ROW_2
250 INSERT INTO self_ref VALUES(4, 4, 8, 0);
251 --error ER_ROW_IS_REFERENCED_2
252 DELETE FROM self_ref WHERE parent_col = 1;
253
254 --echo # Cleanup
255 DROP TABLE self_ref;
256
257 --echo #
258 --echo # Bug#30512043 IGNORE CASE WHEN COMPARING FOREIGN KEY NAMES FROM DD WITH NDB
259 --echo #
260
261 --echo # Setup
262 create table parent(col1 int primary key) engine=ndbcluster;
263 create table child(col int) engine=ndbcluster;
264
265 --echo # Create the foreign key with name in lowercase.
266 alter table child add constraint fk1 foreign key (col) references parent(col1) on delete set null;
267
268 --echo # Check if the foreign key is added.
269 Show create table child;
270
271 --echo # Drop the foreign key using an uppercase identifier and check if the drop is successful.
272 alter table child drop foreign key FK1;
273 alter table child drop index FK1;
274
275 --echo # Check if the foreign key is dropped successfully.
276 Show create table child;
277
278 --echo # Cleanup
279 drop table child, parent;