/
fk_col_alter.test
114 lines (94 loc) · 4.54 KB
/
fk_col_alter.test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
--source include/have_innodb.inc
--echo #
--echo # MDEV-31086 MODIFY COLUMN can break FK constraints, and
--echo # lead to unrestorable dumps
--echo #
CREATE TABLE t1(
id SERIAL,
msg VARCHAR(100) CHARACTER SET utf8mb3,
KEY(msg))ENGINE=InnoDB;
--error ER_CANT_CREATE_TABLE
CREATE TABLE t2(
id SERIAL,
msg varchar(100) CHARACTER SET utf8mb4,
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB;
CREATE TABLE t2(
id SERIAL,
msg varchar(100) CHARACTER SET utf8mb3,
msg_1 varchar(100) CHARACTER SET utf8mb3,
INDEX (msg_1),
INDEX (msg),
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
ON DELETE CASCADE)ENGINE=InnoDB;
# Changing column used in FK constraint
SET FOREIGN_KEY_CHECKS=1;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(400) character set utf8mb3, ALGORITHM=INPLACE;
# Changing column charset used in FK constraint
SET FOREIGN_KEY_CHECKS=1;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=INPLACE;
# Modify the column in the newly added foreign constraint
SET FOREIGN_KEY_CHECKS=1;
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=0;
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
--error ER_FK_COLUMN_CANNOT_CHANGE
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(200) CHARACTER SET utf8mb3, ALGORITHM=INPLACE;
# Change referenced table column
SET FOREIGN_KEY_CHECKS=1;
# Change referenced column length
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=INPLACE;
# Change referenced column character set
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=0;
# Change referenced column length
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(400) CHARSET utf8mb3, ALGORITHM=INPLACE;
# Change referenced column character set
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
# Correct way to change character set in foreign key constraint
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t2 DROP FOREIGN KEY fk_t1, MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (msg) REFERENCES t1(msg), aLGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLE t2, t1;
--echo #
--echo # MDEV-31869 Server aborts when table does drop column
--echo #
CREATE TABLE t (a VARCHAR(40), b INT, C INT) ENGINE=InnoDB;
ALTER TABLE t MODIFY a VARCHAR(50), DROP b;
DROP TABLE t;
--echo # End of 10.4 tests