| 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 |