Debian Codegraph @

mysql-test/suite/innodb/t/innodb-alter-varchar.test

raw
1
2 --echo #
3 --echo # Bug#15863023 SUPPORT IN-PLACE EXTENSION OF VARCHAR COLUMN
4 --echo #
5 CREATE TABLE t1 (pk VARCHAR(2) PRIMARY KEY) ENGINE=InnoDB;
6 CREATE TABLE t2 (fk VARCHAR(2), FOREIGN KEY (fk) REFERENCES t1 (pk))
7 ENGINE=InnoDB;
8 INSERT INTO t1 VALUES ('aa'), ('bb');
9 INSERT INTO t2 VALUES ('aa'), ('bb');
10 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(5);
11 INSERT INTO t1 VALUES ('co');
12
13 INSERT INTO t2 VALUES ('co');
14 INSERT IGNORE INTO t2 VALUES ('code');
15 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(7);
16 --error ER_NO_REFERENCED_ROW_2
17 INSERT INTO t2 VALUES ('code');
18 --error ER_NO_REFERENCED_ROW_2
19 INSERT INTO t2 VALUES ('coastal');
20 --error ER_NO_REFERENCED_ROW_2
21 INSERT INTO t2 VALUES ('coast');
22 INSERT INTO t1 VALUES ('coast');
23 --error ER_NO_REFERENCED_ROW_2
24 INSERT INTO t2 VALUES ('coastal');
25 INSERT INTO t2 VALUES ('coast');
26
27 DROP TABLE t2, t1;
28
29 CREATE TABLE t1 (pk VARCHAR(2) PRIMARY KEY) ENGINE=InnoDB charset latin1;
30 CREATE TABLE t2 (fk VARCHAR(2),
31 CONSTRAINT fk FOREIGN KEY (fk) REFERENCES t1 (pk))
32 ENGINE=InnoDB charset latin1;
33 INSERT INTO t1 VALUES ('aa'), ('bb');
34 INSERT INTO t2 VALUES ('aa'), ('bb');
35 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6);
36 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(255);
37 --echo # We cannot shrink the size or cross the 256-byte boundary.
38 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
39 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6);
40 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
41 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256);
42 --echo # ALGORITHM=COPY has more limitations with FOREIGN KEY.
43 --error ER_FK_COLUMN_CANNOT_CHANGE
44 ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256);
45 ALTER TABLE t2 ALGORITHM=INPLACE, DROP FOREIGN KEY fk;
46 ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256);
47 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(300);
48 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
49 --error ER_BLOB_KEY_WITHOUT_LENGTH
50 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999);
51 ALTER TABLE t2 ALGORITHM=INPLACE, DROP INDEX fk;
52 --echo # Type change from VARCHAR to BLOB is not supported.
53 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
54 ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999);
55 DROP TABLE t2, t1;
56 SET sql_mode = default;