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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
--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_FK_COLUMN_CANNOT_CHANGE
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_FK_COLUMN_CANNOT_CHANGE
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_FK_COLUMN_CANNOT_CHANGE
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_FK_COLUMN_CANNOT_CHANGE_CHILD
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_FK_COLUMN_CANNOT_CHANGE_CHILD
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 #
--echo # MDEV-32060 Server aborts when table doesn't
--echo # have referenced index
--echo #
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t1 (a VARCHAR(16) KEY, FOREIGN KEY(a) REFERENCES t2(b)) ENGINE=InnoDB;
CREATE TABLE t2 (b VARCHAR(8)) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;
ALTER TABLE t2 MODIFY b VARCHAR(16), ADD KEY(b);
DROP TABLE t1, t2;
--echo #
--echo # MDEV-32337 Assertion `pos < table->n_def' failed
--echo # in dict_table_get_nth_col
--echo #
CREATE TABLE t (a INT, va INT AS (a), b INT, vb INT AS (b),
c INT, vc INT AS (c), vf VARCHAR(16) AS (f),
f VARCHAR(4)) ENGINE=InnoDB;
ALTER TABLE t MODIFY f VARCHAR(8);
# Altering the virtual column is not supported
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE t MODIFY vf VARCHAR(18);
DROP TABLE t;
--echo #
--echo # MDEV-32527 Server aborts during alter operation
--echo # when table doesn't have foreign index
--echo #
CREATE TABLE t1 (f1 INT NOT NULL, INDEX(f1)) ENGINE=InnoDB;
CREATE TABLE t2(f1 INT NOT NULL, f2 VARCHAR(100) DEFAULT NULL,
INDEX idx(f1, f2),
FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t2 DROP INDEX idx;
ALTER TABLE t2 MODIFY f2 VARCHAR(1023);
SET SESSION FOREIGN_KEY_CHECKS = ON;
DROP TABLE t2, t1;
--echo #
--echo # MDEV-32638 MariaDB crashes with foreign_key_checks=0
--echo # when changing a column and adding a foreign
--echo # key at the same time
--echo #
CREATE TABLE t1(f1 VARCHAR(2) NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
f2 VARCHAR(10) NOT NULL DEFAULT '')ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t2 CHANGE COLUMN f2 f3 VARCHAR(20) NOT NULL,
ADD CONSTRAINT t2_fk FOREIGN KEY(f3) REFERENCES t1(f1);
DROP TABLE t2, t1;
SET SESSION FOREIGN_KEY_CHECKS = ON;
--echo # End of 10.4 tests
|
