Debian Codegraph @

mysql-test/suite/innodb/r/innodb-alter-varchar.result

raw
1 #
2 # Bug#15863023 SUPPORT IN-PLACE EXTENSION OF VARCHAR COLUMN
3 #
4 CREATE TABLE t1 (pk VARCHAR(2) PRIMARY KEY) ENGINE=InnoDB;
5 CREATE TABLE t2 (fk VARCHAR(2), FOREIGN KEY (fk) REFERENCES t1 (pk))
6 ENGINE=InnoDB;
7 INSERT INTO t1 VALUES ('aa'), ('bb');
8 INSERT INTO t2 VALUES ('aa'), ('bb');
9 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(5);
10 INSERT INTO t1 VALUES ('co');
11 INSERT INTO t2 VALUES ('co');
12 INSERT IGNORE INTO t2 VALUES ('code');
13 Warnings:
14 Warning 1265 Data truncated for column 'fk' at row 1
15 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(7);
16 INSERT INTO t2 VALUES ('code');
17 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`))
18 INSERT INTO t2 VALUES ('coastal');
19 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`))
20 INSERT INTO t2 VALUES ('coast');
21 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`))
22 INSERT INTO t1 VALUES ('coast');
23 INSERT INTO t2 VALUES ('coastal');
24 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`))
25 INSERT INTO t2 VALUES ('coast');
26 DROP TABLE t2, t1;
27 CREATE TABLE t1 (pk VARCHAR(2) PRIMARY KEY) ENGINE=InnoDB charset latin1;
28 CREATE TABLE t2 (fk VARCHAR(2),
29 CONSTRAINT fk FOREIGN KEY (fk) REFERENCES t1 (pk))
30 ENGINE=InnoDB charset latin1;
31 INSERT INTO t1 VALUES ('aa'), ('bb');
32 INSERT INTO t2 VALUES ('aa'), ('bb');
33 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6);
34 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(255);
35 # We cannot shrink the size or cross the 256-byte boundary.
36 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6);
37 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
38 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256);
39 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
40 # ALGORITHM=COPY has more limitations with FOREIGN KEY.
41 ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256);
42 ERROR HY000: Cannot change column 'fk': used in a foreign key constraint 'fk'
43 ALTER TABLE t2 ALGORITHM=INPLACE, DROP FOREIGN KEY fk;
44 ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256);
45 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(300);
46 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
47 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999);
48 ERROR 42000: BLOB/TEXT column 'fk' used in key specification without a key length
49 ALTER TABLE t2 ALGORITHM=INPLACE, DROP INDEX fk;
50 # Type change from VARCHAR to BLOB is not supported.
51 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999);
52 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
53 DROP TABLE t2, t1;
54 SET sql_mode = default;