github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/tidb_mysql_test/t/alter_table.test (about) 1 # For add column 2 drop table if exists t; 3 create table t (c1 int); 4 alter table t add column c2 int; 5 alter table t add column c3 int first; 6 alter table t add column c4 int after c1; 7 insert into t values (1, 2, 3, 4); 8 select * from t; 9 10 11 12 create table t1 ( 13 col1 int not null auto_increment primary key, 14 col2 varchar(30) not null, 15 col3 varchar (20) not null, 16 col4 varchar(4) not null, 17 col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, 18 col6 int not null, to_be_deleted int); 19 insert into t1 values (2,4,3,5,"PENDING",1,7); 20 alter table t1 add column col4_5 varchar(20) not null after col4; 21 alter table t1 add column col7 varchar(30) not null after col5; 22 alter table t1 add column col8 datetime not null; 23 alter table t1 drop column to_be_deleted; 24 alter table t1 change column col2 fourth varchar(30) not null after col3; 25 alter table t1 modify column col6 int not null first; 26 select * from t1; 27 #drop table t1; 28 29 30 31 # Check that pack_keys and dynamic length rows are not forced. 32 33 CREATE TABLE t2 ( 34 GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL, 35 LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, 36 NAME varchar(80) DEFAULT '' NOT NULL, 37 PRIMARY KEY (GROUP_ID,LANG_ID), 38 KEY NAME (NAME)); 39 --error 8200 40 ALTER TABLE t2 CHANGE NAME NAME CHAR(80) not null; 41 # TODO: SHOW FULL COLUMNS FROM t1; 42 # TODO: Expected result: > SHOW FULL COLUMNS FROM t1; 43 # TODO: Expected result: > Field Type Collation Null Key Default Extra Privileges Comment 44 # TODO: Expected result: > GROUP_ID int(10) unsigned NULL NO PRI 0 # 45 # TODO: Expected result: > LANG_ID smallint(5) unsigned NULL NO PRI 0 # 46 # TODO: Expected result: > NAME char(80) latin1_swedish_ci NO MUL NULL # 47 #DROP TABLE t2; 48 49 50 # The following is also part of bug #6236 (CREATE TABLE didn't properly count 51 # not null columns for primary keys) 52 53 create table t3 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; 54 # TODO: insert into t1 (a) values(1); 55 # TODO: Expected warnings 56 --replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 57 # TODO: show table status like 't1'; 58 # TODO: Expected result: > show table status like 't1'; 59 # TODO: Expected result: > Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 60 # TODO: Expected result: > t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL 61 alter table t3 modify a int; 62 --replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 63 # TODO: show table status like 't1'; 64 # TODO: Expected result: > show table status like 't1'; 65 # TODO: Expected result: > Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 66 # TODO: Expected result: > t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL 67 #drop table t1; 68 create table t4 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; 69 # TODO: insert into t1 (a) values(1); 70 # TODO: Expected warnings 71 --replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 72 # TODO: show table status like 't1'; 73 # TODO: Expected result: > show table status like 't1'; 74 # TODO: Expected result: > Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 75 # TODO: Expected result: > t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL 76 #drop table t1; 77 78 # 79 # Bug#18038 MySQL server corrupts binary columns data 80 # 81 82 CREATE TABLE t5 (s CHAR(8) BINARY); 83 INSERT INTO t5 VALUES ('test'); 84 SELECT LENGTH(s) FROM t5; 85 ALTER TABLE t5 MODIFY s CHAR(10) BINARY; 86 SELECT LENGTH(s) FROM t5; 87 #DROP TABLE t1; 88 89 CREATE TABLE t6 (s BINARY(8)); 90 INSERT INTO t6 VALUES ('test'); 91 SELECT LENGTH(s) FROM t6; 92 SELECT HEX(s) FROM t6; 93 ALTER TABLE t6 MODIFY s BINARY(10); 94 # TODO: SELECT HEX(s) FROM t1; 95 # TODO: Expected result: > SELECT HEX(s) FROM t1; 96 # TODO: Expected result: > HEX(s) 97 # TODO: Expected result: > 74657374000000000000 98 # TODO: SELECT LENGTH(s) FROM t1; 99 # TODO: Expected result: > SELECT LENGTH(s) FROM t1; 100 # TODO: Expected result: > LENGTH(s) 101 # TODO: Expected result: > 10 102 #DROP TABLE t1; 103 104 105 # 106 # Bug#19386: Multiple alter causes crashed table 107 # The trailing column would get corrupted data, or server could not even read 108 # it. 109 # 110 111 CREATE TABLE t7 (v VARCHAR(3), b INT); 112 INSERT INTO t7 VALUES ('abc', 5); 113 SELECT * FROM t7; 114 ALTER TABLE t7 MODIFY COLUMN v VARCHAR(4); 115 SELECT * FROM t7; 116 #DROP TABLE t1; 117 118 119 # 120 # Bug#28427: Columns were renamed instead of moving by ALTER TABLE. 121 # 122 CREATE TABLE t8 (f1 INT, f2 INT, f3 INT); 123 INSERT INTO t8 VALUES (1, 2, NULL); 124 SELECT * FROM t8; 125 ALTER TABLE t8 MODIFY COLUMN f3 INT AFTER f1; 126 SELECT * FROM t8; 127 ALTER TABLE t8 MODIFY COLUMN f3 INT AFTER f2; 128 SELECT * FROM t8; 129 #DROP TABLE t1; 130 131 132 --echo # 133 --echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION 134 --echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". 135 --echo # 136 --disable_warnings 137 drop table if exists t9; 138 --enable_warnings 139 --echo # Use MyISAM engine as the fact that InnoDB doesn't support 140 --echo # in-place ALTER TABLE in cases when columns are being renamed 141 --echo # hides some bugs. 142 create table t9 (i int, j int) engine=myisam; 143 insert into t9 value (1, 2); 144 --echo # First, test for original problem described in the bug report. 145 select * from t9; 146 --echo # Change of column order by the below ALTER TABLE statement should 147 --echo # affect both column names and column contents. 148 alter table t9 modify column j int first; 149 select * from t9; 150 --echo # Now test for similar problem with the same root. 151 --echo # The below ALTER TABLE should change not only the name but 152 --echo # also the value for the last column of the table. 153 alter table t9 drop column i; 154 alter table t9 add column k int default 0; 155 select * from t9; 156 --echo # Clean-up. 157 #drop table t1; 158 159 160 --echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't 161 --echo # identify correct column name. 162 --echo # 163 164 CREATE TABLE t10 (c1 int unsigned , c2 char(100) not null default ''); 165 ALTER TABLE t10 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2; 166 ALTER TABLE t10 MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; 167 # TODO: SHOW CREATE TABLE t1; 168 # TODO: Expected result: > SHOW CREATE TABLE t1; 169 # TODO: Expected result: > Table Create Table 170 # TODO: Expected result: > t1 CREATE TABLE `t1` ( 171 # TODO: Expected result: > `c1` int(10) unsigned DEFAULT NULL, 172 # TODO: Expected result: > `c2` char(100) NOT NULL DEFAULT '', 173 # TODO: Expected result: > `c3` char(16) NOT NULL DEFAULT '' 174 # TODO: Expected result: > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 175 #DROP TABLE t1; 176 177 178 179 180 # 181 # Some additional tests for new, faster alter table. Note that most of the 182 # whole alter table code is being tested all around the test suite already. 183 # 184 185 create table t11 (v varchar(32)); 186 insert into t11 values ('def'),('abc'),('hij'),('3r4f'); 187 select * from t11; 188 # Fast alter, no copy performed 189 alter table t11 change v v2 varchar(32); 190 select * from t11; 191 # Fast alter, no copy performed 192 alter table t11 change v2 v varchar(64); 193 select * from t11; 194 update t11 set v = 'lmn' where v = 'hij'; 195 select * from t11; 196 #drop table t1;