| 1 |
--source include/have_innodb.inc |
| 2 |
--echo # |
| 3 |
--echo # MDEV-31086 MODIFY COLUMN can break FK constraints, and |
| 4 |
--echo # lead to unrestorable dumps |
| 5 |
--echo # |
| 6 |
CREATE TABLE t1( |
| 7 |
id SERIAL, |
| 8 |
msg VARCHAR(100) CHARACTER SET utf8mb3, |
| 9 |
KEY(msg))ENGINE=InnoDB; |
| 10 |
|
| 11 |
--error ER_CANT_CREATE_TABLE |
| 12 |
CREATE TABLE t2( |
| 13 |
id SERIAL, |
| 14 |
msg varchar(100) CHARACTER SET utf8mb4, |
| 15 |
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB; |
| 16 |
|
| 17 |
CREATE TABLE t2( |
| 18 |
id SERIAL, |
| 19 |
msg varchar(100) CHARACTER SET utf8mb3, |
| 20 |
msg_1 varchar(100) CHARACTER SET utf8mb3, |
| 21 |
INDEX (msg_1), |
| 22 |
INDEX (msg), |
| 23 |
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg) |
| 24 |
ON DELETE CASCADE)ENGINE=InnoDB; |
| 25 |
|
| 26 |
# Changing column used in FK constraint |
| 27 |
SET FOREIGN_KEY_CHECKS=1; |
| 28 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 29 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY; |
| 30 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 31 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=INPLACE; |
| 32 |
|
| 33 |
SET FOREIGN_KEY_CHECKS=0; |
| 34 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 35 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY; |
| 36 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 37 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(400) character set utf8mb3, ALGORITHM=INPLACE; |
| 38 |
|
| 39 |
# Changing column charset used in FK constraint |
| 40 |
SET FOREIGN_KEY_CHECKS=1; |
| 41 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 42 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY; |
| 43 |
|
| 44 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 45 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE; |
| 46 |
|
| 47 |
SET FOREIGN_KEY_CHECKS=0; |
| 48 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 49 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=COPY; |
| 50 |
|
| 51 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 52 |
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=INPLACE; |
| 53 |
|
| 54 |
# Modify the column in the newly added foreign constraint |
| 55 |
SET FOREIGN_KEY_CHECKS=1; |
| 56 |
--error ER_CANT_CREATE_TABLE |
| 57 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY; |
| 58 |
|
| 59 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON |
| 60 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE; |
| 61 |
|
| 62 |
|
| 63 |
SET FOREIGN_KEY_CHECKS=0; |
| 64 |
--error ER_CANT_CREATE_TABLE |
| 65 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY; |
| 66 |
|
| 67 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 68 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE; |
| 69 |
|
| 70 |
--error ER_FK_COLUMN_CANNOT_CHANGE |
| 71 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(200) CHARACTER SET utf8mb3, ALGORITHM=INPLACE; |
| 72 |
|
| 73 |
# Change referenced table column |
| 74 |
SET FOREIGN_KEY_CHECKS=1; |
| 75 |
# Change referenced column length |
| 76 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 77 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY; |
| 78 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 79 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=INPLACE; |
| 80 |
# Change referenced column character set |
| 81 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 82 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY; |
| 83 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 84 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE; |
| 85 |
|
| 86 |
SET FOREIGN_KEY_CHECKS=0; |
| 87 |
# Change referenced column length |
| 88 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 89 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY; |
| 90 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 91 |
ALTER TABLE t1 MODIFY msg VARCHAR(400) CHARSET utf8mb3, ALGORITHM=INPLACE; |
| 92 |
|
| 93 |
# Change referenced column character set |
| 94 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 95 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY; |
| 96 |
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD |
| 97 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE; |
| 98 |
|
| 99 |
# Correct way to change character set in foreign key constraint |
| 100 |
SET FOREIGN_KEY_CHECKS=0; |
| 101 |
ALTER TABLE t2 DROP FOREIGN KEY fk_t1, MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY; |
| 102 |
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY; |
| 103 |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (msg) REFERENCES t1(msg), aLGORITHM=INPLACE; |
| 104 |
SET FOREIGN_KEY_CHECKS=1; |
| 105 |
|
| 106 |
DROP TABLE t2, t1; |
| 107 |
|
| 108 |
--echo # |
| 109 |
--echo # MDEV-31869 Server aborts when table does drop column |
| 110 |
--echo # |
| 111 |
CREATE TABLE t (a VARCHAR(40), b INT, C INT) ENGINE=InnoDB; |
| 112 |
ALTER TABLE t MODIFY a VARCHAR(50), DROP b; |
| 113 |
DROP TABLE t; |
| 114 |
|
| 115 |
--echo # |
| 116 |
--echo # MDEV-32060 Server aborts when table doesn't |
| 117 |
--echo # have referenced index |
| 118 |
--echo # |
| 119 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 120 |
CREATE TABLE t1 (a VARCHAR(16) KEY, FOREIGN KEY(a) REFERENCES t2(b)) ENGINE=InnoDB; |
| 121 |
CREATE TABLE t2 (b VARCHAR(8)) ENGINE=InnoDB; |
| 122 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 123 |
ALTER TABLE t2 MODIFY b VARCHAR(16), ADD KEY(b); |
| 124 |
DROP TABLE t1, t2; |
| 125 |
|
| 126 |
--echo # |
| 127 |
--echo # MDEV-32337 Assertion `pos < table->n_def' failed |
| 128 |
--echo # in dict_table_get_nth_col |
| 129 |
--echo # |
| 130 |
CREATE TABLE t (a INT, va INT AS (a), b INT, vb INT AS (b), |
| 131 |
c INT, vc INT AS (c), vf VARCHAR(16) AS (f), |
| 132 |
f VARCHAR(4)) ENGINE=InnoDB; |
| 133 |
ALTER TABLE t MODIFY f VARCHAR(8); |
| 134 |
# Altering the virtual column is not supported |
| 135 |
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN |
| 136 |
ALTER TABLE t MODIFY vf VARCHAR(18); |
| 137 |
DROP TABLE t; |
| 138 |
|
| 139 |
--echo # |
| 140 |
--echo # MDEV-32527 Server aborts during alter operation |
| 141 |
--echo # when table doesn't have foreign index |
| 142 |
--echo # |
| 143 |
CREATE TABLE t1 (f1 INT NOT NULL, INDEX(f1)) ENGINE=InnoDB; |
| 144 |
CREATE TABLE t2(f1 INT NOT NULL, f2 VARCHAR(100) DEFAULT NULL, |
| 145 |
INDEX idx(f1, f2), |
| 146 |
FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB; |
| 147 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 148 |
ALTER TABLE t2 DROP INDEX idx; |
| 149 |
ALTER TABLE t2 MODIFY f2 VARCHAR(1023); |
| 150 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 151 |
DROP TABLE t2, t1; |
| 152 |
|
| 153 |
--echo # |
| 154 |
--echo # MDEV-32638 MariaDB crashes with foreign_key_checks=0 |
| 155 |
--echo # when changing a column and adding a foreign |
| 156 |
--echo # key at the same time |
| 157 |
--echo # |
| 158 |
CREATE TABLE t1(f1 VARCHAR(2) NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB; |
| 159 |
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY, |
| 160 |
f2 VARCHAR(10) NOT NULL DEFAULT '')ENGINE=InnoDB; |
| 161 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 162 |
ALTER TABLE t2 CHANGE COLUMN f2 f3 VARCHAR(20) NOT NULL, |
| 163 |
ADD CONSTRAINT t2_fk FOREIGN KEY(f3) REFERENCES t1(f1); |
| 164 |
DROP TABLE t2, t1; |
| 165 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 166 |
--echo # End of 10.4 tests |