1create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=myisam; 2insert into t1 (a) values (1),(2),(3); 3alter online table t1 modify b int default 5, alter c set default 'X'; 4alter online table t1 change b new_name int; 5alter online table t1 modify e enum('a','b','c'); 6alter online table t1 comment "new comment"; 7alter table t1 add constraint q check (a > 0); 8alter online table t1 drop constraint q; 9alter online table t1 algorithm=INPLACE, lock=NONE; 10alter online table t1; 11alter table t1 algorithm=INPLACE; 12alter table t1 lock=NONE; 13show create table t1; 14Table Create Table 15t1 CREATE TABLE `t1` ( 16 `a` int(11) NOT NULL, 17 `new_name` int(11) DEFAULT NULL, 18 `c` varchar(80) DEFAULT 'X', 19 `e` enum('a','b','c') DEFAULT NULL, 20 PRIMARY KEY (`a`) 21) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='new comment' 22drop table t1; 23create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); 24insert into t1 (a) values (1),(2),(3); 25alter online table t1 modify b int default 5, alter c set default 'X'; 26alter online table t1 change b new_name int; 27alter online table t1 modify e enum('a','b','c'); 28alter online table t1 comment "new comment"; 29alter online table t1 rename to t2; 30show create table t2; 31Table Create Table 32t2 CREATE TEMPORARY TABLE `t2` ( 33 `a` int(11) NOT NULL, 34 `new_name` int(11) DEFAULT NULL, 35 `c` varchar(80) DEFAULT 'X', 36 `e` enum('a','b','c') DEFAULT NULL, 37 PRIMARY KEY (`a`) 38) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='new comment' 39drop table t2; 40create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=aria; 41insert into t1 (a) values (1),(2),(3); 42alter online table t1 modify b int default 5; 43alter online table t1 change b new_name int; 44alter online table t1 modify e enum('a','b','c'); 45alter online table t1 comment "new comment"; 46show create table t1; 47Table Create Table 48t1 CREATE TABLE `t1` ( 49 `a` int(11) NOT NULL, 50 `new_name` int(11) DEFAULT NULL, 51 `c` varchar(80) DEFAULT NULL, 52 `e` enum('a','b','c') DEFAULT NULL, 53 PRIMARY KEY (`a`) 54) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 COMMENT='new comment' 55alter online table t1 page_checksum=1; 56alter online table t1 page_checksum=0; 57ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 58drop table t1; 59create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); 60insert into t1 (a) values (1),(2),(3); 61alter online table t1 drop column b, add b int; 62ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 63alter online table t1 modify b bigint; 64ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 65alter online table t1 modify e enum('c','a','b'); 66ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 67alter online table t1 modify c varchar(50); 68ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 69alter online table t1 modify c varchar(100); 70ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 71alter online table t1 add f int; 72ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 73alter online table t1 engine=memory; 74ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED 75alter online table t1 rename to t2; 76ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE 77alter online table t1 checksum=1; 78ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 79alter online table t1 add constraint check (b > 0); 80ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED 81alter table t1 engine=innodb; 82alter table t1 add index (b); 83alter online table t1 add index c (c); 84alter online table t1 drop index b; 85alter online table t1 comment "new comment"; 86show create table t1; 87Table Create Table 88t1 CREATE TABLE `t1` ( 89 `a` int(11) NOT NULL, 90 `b` int(11) DEFAULT NULL, 91 `c` varchar(80) DEFAULT NULL, 92 `e` enum('a','b') DEFAULT NULL, 93 PRIMARY KEY (`a`), 94 KEY `c` (`c`) 95) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='new comment' 96drop table t1; 97create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); 98insert into t1 (a) values (1),(2),(3); 99alter online table t1 drop column b, add b int; 100alter online table t1 modify b bigint; 101alter online table t1 modify e enum('c','a','b'); 102alter online table t1 modify c varchar(50); 103alter online table t1 modify c varchar(100); 104alter online table t1 add f int; 105alter online table t1 engine=memory; 106alter table t1 engine=innodb; 107alter table t1 add index (b); 108alter online table t1 add index c (c); 109alter online table t1 drop index b; 110drop table t1; 111create table t1 (a int not null primary key, b int, c varchar(80)); 112create table t2 (a int not null primary key, b int, c varchar(80)); 113create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge UNION=(t1); 114alter online table t3 union=(t1,t2); 115ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=EXCLUSIVE 116drop table t1,t2,t3; 117create table t1 (i int) partition by hash(i) partitions 2; 118alter online table t1 comment 'test'; 119drop table t1; 120create table t1 (a int); 121alter online table t1 modify a int comment 'test'; 122drop table t1; 123create table t1 (a int) engine=innodb; 124alter online table t1 modify a int comment 'test'; 125drop table t1; 126create table t1 (a int) partition by hash(a) partitions 2; 127alter online table t1 modify a int comment 'test'; 128drop table t1; 129# 130# MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB 131# 132CREATE TABLE t1 (a BINARY(10)); 133ALTER TABLE t1 MODIFY a BINARY(10), ALGORITHM=INPLACE; 134DROP TABLE t1; 135CREATE TABLE t1 (a VARBINARY(10)); 136ALTER TABLE t1 MODIFY a VARBINARY(10), ALGORITHM=INPLACE; 137DROP TABLE t1; 138CREATE TABLE t1 (a TINYBLOB); 139ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; 140DROP TABLE t1; 141CREATE TABLE t1 (a MEDIUMBLOB); 142ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; 143DROP TABLE t1; 144CREATE TABLE t1 (a BLOB); 145ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; 146DROP TABLE t1; 147CREATE TABLE t1 (a LONGBLOB); 148ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; 149DROP TABLE t1; 150CREATE TABLE t1 (a CHAR(10)); 151ALTER TABLE t1 MODIFY a CHAR(10), ALGORITHM=INPLACE; 152DROP TABLE t1; 153CREATE TABLE t1 (a VARCHAR(10)); 154ALTER TABLE t1 MODIFY a VARCHAR(10), ALGORITHM=INPLACE; 155DROP TABLE t1; 156CREATE TABLE t1 (a TINYTEXT); 157ALTER TABLE t1 MODIFY a TINYTEXT, ALGORITHM=INPLACE; 158DROP TABLE t1; 159CREATE TABLE t1 (a MEDIUMTEXT); 160ALTER TABLE t1 MODIFY a MEDIUMTEXT, ALGORITHM=INPLACE; 161DROP TABLE t1; 162CREATE TABLE t1 (a TEXT); 163ALTER TABLE t1 MODIFY a TEXT, ALGORITHM=INPLACE; 164DROP TABLE t1; 165CREATE TABLE t1 (a LONGTEXT); 166ALTER TABLE t1 MODIFY a LONGTEXT, ALGORITHM=INPLACE; 167DROP TABLE t1; 168CREATE TABLE t1 (a CHAR(10)); 169ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; 170ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 171DROP TABLE t1; 172CREATE TABLE t1 (a VARCHAR(10)); 173ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; 174ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 175DROP TABLE t1; 176CREATE TABLE t1 (a TINYTEXT); 177ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; 178ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 179DROP TABLE t1; 180CREATE TABLE t1 (a MEDIUMTEXT); 181ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; 182ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 183DROP TABLE t1; 184CREATE TABLE t1 (a TEXT); 185ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_bin, ALGORITHM=INPLACE; 186ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 187DROP TABLE t1; 188CREATE TABLE t1 (a LONGTEXT); 189ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; 190ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 191DROP TABLE t1; 192CREATE TABLE t1 (a CHAR(10) COLLATE latin1_bin); 193ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 194ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 195DROP TABLE t1; 196CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); 197ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 198ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 199DROP TABLE t1; 200CREATE TABLE t1 (a TINYTEXT COLLATE latin1_bin); 201ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 202ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 203DROP TABLE t1; 204CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_bin); 205ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 206ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 207DROP TABLE t1; 208CREATE TABLE t1 (a TEXT COLLATE latin1_bin); 209ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 210ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 211DROP TABLE t1; 212CREATE TABLE t1 (a LONGTEXT COLLATE latin1_bin); 213ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 214ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 215DROP TABLE t1; 216CREATE TABLE t1 (a CHAR(10) COLLATE latin1_general_ci); 217ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 218ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 219DROP TABLE t1; 220CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_general_ci); 221ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 222ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 223DROP TABLE t1; 224CREATE TABLE t1 (a TINYTEXT COLLATE latin1_general_ci); 225ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 226ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 227DROP TABLE t1; 228CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_general_ci); 229ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 230ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 231DROP TABLE t1; 232CREATE TABLE t1 (a TEXT COLLATE latin1_general_ci); 233ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 234ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 235DROP TABLE t1; 236CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); 237ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; 238ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY 239DROP TABLE t1; 240select @@global.delay_key_write; 241@@global.delay_key_write 242ON 243create table t1 (a int, b int, key(b)); 244flush tables; 245flush status; 246show status like 'Feature_delay_key_write'; 247Variable_name Value 248Feature_delay_key_write 0 249insert t1 values (1,2),(2,3),(3,4); 250show status like 'Feature_delay_key_write'; 251Variable_name Value 252Feature_delay_key_write 0 253alter online table t1 delay_key_write=1; 254show status like 'Feature_delay_key_write'; 255Variable_name Value 256Feature_delay_key_write 1 257flush tables; 258insert t1 values (1,2),(2,3),(3,4); 259show status like 'Feature_delay_key_write'; 260Variable_name Value 261Feature_delay_key_write 2 262alter online table t1 delay_key_write=0; 263show status like 'Feature_delay_key_write'; 264Variable_name Value 265Feature_delay_key_write 2 266flush tables; 267insert t1 values (1,2),(2,3),(3,4); 268show status like 'Feature_delay_key_write'; 269Variable_name Value 270Feature_delay_key_write 2 271drop table t1; 272