Debian Codegraph @

mysql-test/suite/innodb/r/fk_col_alter.result

raw
1 #
2 # MDEV-31086 MODIFY COLUMN can break FK constraints, and
3 # lead to unrestorable dumps
4 #
5 CREATE TABLE t1(
6 id SERIAL,
7 msg VARCHAR(100) CHARACTER SET utf8mb3,
8 KEY(msg))ENGINE=InnoDB;
9 CREATE TABLE t2(
10 id SERIAL,
11 msg varchar(100) CHARACTER SET utf8mb4,
12 CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB;
13 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
14 CREATE TABLE t2(
15 id SERIAL,
16 msg varchar(100) CHARACTER SET utf8mb3,
17 msg_1 varchar(100) CHARACTER SET utf8mb3,
18 INDEX (msg_1),
19 INDEX (msg),
20 CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
21 ON DELETE CASCADE)ENGINE=InnoDB;
22 SET FOREIGN_KEY_CHECKS=1;
23 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
24 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
25 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=INPLACE;
26 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1'
27 SET FOREIGN_KEY_CHECKS=0;
28 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
29 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
30 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(400) character set utf8mb3, ALGORITHM=INPLACE;
31 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1'
32 SET FOREIGN_KEY_CHECKS=1;
33 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
34 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
35 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
36 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1'
37 SET FOREIGN_KEY_CHECKS=0;
38 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=COPY;
39 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
40 ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=INPLACE;
41 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1'
42 SET FOREIGN_KEY_CHECKS=1;
43 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
44 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
45 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
46 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
47 SET FOREIGN_KEY_CHECKS=0;
48 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
49 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
50 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
51 ERROR HY000: Cannot change column 'msg_1': used in a foreign key constraint 'test/t2_ibfk_0'
52 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(200) CHARACTER SET utf8mb3, ALGORITHM=INPLACE;
53 ERROR HY000: Cannot change column 'msg_1': used in a foreign key constraint 'test/t2_ibfk_0'
54 SET FOREIGN_KEY_CHECKS=1;
55 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
56 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
57 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=INPLACE;
58 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1' of table 'test/t2'
59 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
60 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
61 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
62 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1' of table 'test/t2'
63 SET FOREIGN_KEY_CHECKS=0;
64 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
65 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
66 ALTER TABLE t1 MODIFY msg VARCHAR(400) CHARSET utf8mb3, ALGORITHM=INPLACE;
67 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1' of table 'test/t2'
68 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
69 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
70 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
71 ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'test/fk_t1' of table 'test/t2'
72 SET FOREIGN_KEY_CHECKS=0;
73 ALTER TABLE t2 DROP FOREIGN KEY fk_t1, MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
74 ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
75 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (msg) REFERENCES t1(msg), aLGORITHM=INPLACE;
76 SET FOREIGN_KEY_CHECKS=1;
77 DROP TABLE t2, t1;
78 #
79 # MDEV-31869 Server aborts when table does drop column
80 #
81 CREATE TABLE t (a VARCHAR(40), b INT, C INT) ENGINE=InnoDB;
82 ALTER TABLE t MODIFY a VARCHAR(50), DROP b;
83 DROP TABLE t;
84 #
85 # MDEV-32060 Server aborts when table doesn't
86 # have referenced index
87 #
88 SET SESSION FOREIGN_KEY_CHECKS = OFF;
89 CREATE TABLE t1 (a VARCHAR(16) KEY, FOREIGN KEY(a) REFERENCES t2(b)) ENGINE=InnoDB;
90 CREATE TABLE t2 (b VARCHAR(8)) ENGINE=InnoDB;
91 SET SESSION FOREIGN_KEY_CHECKS = ON;
92 ALTER TABLE t2 MODIFY b VARCHAR(16), ADD KEY(b);
93 DROP TABLE t1, t2;
94 #
95 # MDEV-32337 Assertion `pos < table->n_def' failed
96 # in dict_table_get_nth_col
97 #
98 CREATE TABLE t (a INT, va INT AS (a), b INT, vb INT AS (b),
99 c INT, vc INT AS (c), vf VARCHAR(16) AS (f),
100 f VARCHAR(4)) ENGINE=InnoDB;
101 ALTER TABLE t MODIFY f VARCHAR(8);
102 ALTER TABLE t MODIFY vf VARCHAR(18);
103 ERROR HY000: This is not yet supported for generated columns
104 DROP TABLE t;
105 #
106 # MDEV-32527 Server aborts during alter operation
107 # when table doesn't have foreign index
108 #
109 CREATE TABLE t1 (f1 INT NOT NULL, INDEX(f1)) ENGINE=InnoDB;
110 CREATE TABLE t2(f1 INT NOT NULL, f2 VARCHAR(100) DEFAULT NULL,
111 INDEX idx(f1, f2),
112 FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
113 SET SESSION FOREIGN_KEY_CHECKS = OFF;
114 ALTER TABLE t2 DROP INDEX idx;
115 ALTER TABLE t2 MODIFY f2 VARCHAR(1023);
116 SET SESSION FOREIGN_KEY_CHECKS = ON;
117 DROP TABLE t2, t1;
118 #
119 # MDEV-32638 MariaDB crashes with foreign_key_checks=0
120 # when changing a column and adding a foreign
121 # key at the same time
122 #
123 CREATE TABLE t1(f1 VARCHAR(2) NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
124 CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
125 f2 VARCHAR(10) NOT NULL DEFAULT '')ENGINE=InnoDB;
126 SET SESSION FOREIGN_KEY_CHECKS = OFF;
127 ALTER TABLE t2 CHANGE COLUMN f2 f3 VARCHAR(20) NOT NULL,
128 ADD CONSTRAINT t2_fk FOREIGN KEY(f3) REFERENCES t1(f1);
129 DROP TABLE t2, t1;
130 SET SESSION FOREIGN_KEY_CHECKS = ON;
131 # End of 10.4 tests