diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/instant_alter.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/instant_alter.result | 1358 |
1 files changed, 1347 insertions, 11 deletions
diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 1a818531756..e0661557159 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -2,7 +2,7 @@ # MDEV-11369: Instant ADD COLUMN for InnoDB # SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed; -SET GLOBAL innodb_instant_alter_column_allowed = add_last; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is"); CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t ADD e INT, ROW_FORMAT=COMPRESSED; @@ -185,8 +185,8 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 INSERT INTO t1 SET id=9; ALTER TABLE t1 DROP c3; -affected rows: 9 -info: Records: 9 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -239,6 +239,7 @@ UPDATE t2 SET c1 = repeat(id, 4000); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -249,6 +250,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -260,6 +262,7 @@ UPDATE t2 SET d1 = repeat(id, 200); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -270,6 +273,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -391,6 +395,7 @@ COMMIT; connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -414,6 +419,7 @@ test.big 385477733 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -427,6 +433,7 @@ test.big 1705165209 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -535,8 +542,439 @@ ALTER TABLE t1 MODIFY a INT DEFAULT 0; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; -DROP TABLE t1; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; +DROP TABLE t1; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; +CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 (a) VALUES (NULL), (NULL); +ALTER TABLE t1 DROP a, ADD COLUMN a INT; +DELETE FROM t1; +BEGIN; +INSERT INTO t1 SET a=NULL; +ROLLBACK; +DELETE FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES(347,''); +ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT; +SELECT * FROM t1; +a +347 +DROP TABLE t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b; +SELECT * FROM t1; +b c a +NULL NULL NULL +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SET @t=REPEAT('x',@@innodb_page_size / 2); +INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t); +ALTER TABLE t1 ADD COLUMN a INT FIRST; +UPDATE t1 SET a = 0; +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET t = @x; +ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1; +ALTER TABLE t1 ADD COLUMN t TEXT; +SELECT * FROM t1; +i t +1 NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET a=NULL; +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42; +INSERT INTO t1 SET a=NULL; +UPDATE t1 SET a=a+2; +SELECT * FROM t1; +a c +3 42 +4 42 +DROP TABLE t1; +CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET i=1; +ALTER TABLE t1 ADD COLUMN b BIT FIRST; +ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL; +SELECT * FROM t1; +b i v +NULL 1 1 +DROP TABLE t1; +CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME; +ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST; +ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (4,4,4); +ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT; +DELETE FROM t1; +ALTER TABLE t1 DROP COLUMN f4; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT; +ALTER TABLE t1 DROP f4; +ALTER TABLE t1 DROP f1; +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 () VALUES (),(); +SELECT * FROM t1; +f +NULL +NULL +ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id); +SELECT * FROM t1; +id f +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET f=NULL; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 SET f=NULL; +SELECT * FROM t1; +f +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k)) +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 (pk) VALUES (1); +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, +f2 CHAR(255), +f3 BIGINT, +f4 INT, +f5 CHAR(255), +f6 CHAR(255), +f7 CHAR(255) NOT NULL, +f8 INT, +f9 CHAR(10) +) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'), +(2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'), +(3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'), +(4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'), +(5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'), +(6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'), +(7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'), +(8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'), +(9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'), +(10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'), +(11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'), +(12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'), +(13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'), +(14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'), +(15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o'); +DELETE FROM t1 WHERE pk=1; +InnoDB 0 transactions not purged +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'); +ALTER TABLE t1 DROP COLUMN f1; +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, f2 CHAR(32) NOT NULL, +f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL, +f7 CHAR(32), f8 CHAR(32) +) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES +(1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''), +(3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''), +(5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''), +(7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''), +(9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''), +(11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''), +(13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''), +(15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''), +(17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''), +(19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''), +(21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''), +(23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''), +(25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''), +(51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''), +(53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''), +(55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''), +(57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''), +(59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''), +(61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''), +(63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''), +(101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''), +(103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''), +(105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''), +(107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''), +(109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''), +(151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''), +(153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''), +(155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''), +(157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''), +(159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''), +(201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''), +(203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''), +(205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''), +(207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''), +(252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''), +(254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''), +(256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''), +(258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''), +(301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''), +(303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''), +(305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''), +(307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''), +(351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''), +(353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''), +(355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''), +(357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''), +(359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''), +(361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''), +(363,75,'',66,76,52,'','',''); +ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar '; +UPDATE t1 SET f1 = 0; +ALTER TABLE t1 DROP COLUMN x; +DROP TABLE t1; +CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST; +DROP TABLE t1; +CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 CHANGE COLUMN a a INT; +DELETE FROM t1 WHERE a = NULL OR a IS NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT, +e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (0,0); +ALTER TABLE t1 MODIFY a INT AFTER b; +ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL; +ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 ADD COLUMN b INT; +ALTER TABLE t1 MODIFY COLUMN a INT NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000)); +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 VALUES (2,20); +ALTER TABLE t1 ADD COLUMN vpk INT AS (pk); +ALTER TABLE t1 DROP COLUMN i; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD f DATE AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD COLUMN f INT AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 MODIFY COLUMN t TEXT; +DROP TABLE t1; +CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES(127,6502),(-128,33101); +ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345, +MODIFY g BIGINT UNSIGNED DEFAULT 1234567; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f g +127 6502 +-128 33101 +DROP TABLE t1; +CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (b'10000000'),(b'00000001'); +ALTER TABLE t1 MODIFY f BIT(16); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000'); +SELECT HEX(f) FROM t1; +HEX(f) +80 +1 +80AF +80 +ALTER TABLE t1 MODIFY f SMALLINT; +ERROR 22003: Out of range value for column 'f' at row 3 +ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED; +affected rows: 4 +info: Records: 4 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f +128 +1 +32943 +128 +ALTER TABLE t1 MODIFY f BIT; +ERROR 22001: Data too long for column 'f' at row 1 +ALTER TABLE t1 MODIFY f BIT(15); +ERROR 22001: Data too long for column 'f' at row 3 +DELETE FROM t1 LIMIT 3; +ALTER TABLE t1 MODIFY f BIT(15); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT HEX(f) FROM t1; +HEX(f) +80 +DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES(1,'a'); +ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT; +INSERT INTO t1 VALUES(2,'bah',3); +SELECT * FROM t1; +a b c +1 a NULL +2 bah 3 +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES('barf'); +ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +b a vb +NULL barf NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 ADD COLUMN c TEXT FIRST; +ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST; +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b)) +DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET a='fubar',b=42; +ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +a b vb +fubar 42 42 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 MODIFY b INT FIRST; +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -665,8 +1103,8 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 INSERT INTO t1 SET id=9; ALTER TABLE t1 DROP c3; -affected rows: 9 -info: Records: 9 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -719,6 +1157,7 @@ UPDATE t2 SET c1 = repeat(id, 4000); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -729,6 +1168,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -740,6 +1180,7 @@ UPDATE t2 SET d1 = repeat(id, 200); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -750,6 +1191,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -871,6 +1313,7 @@ COMMIT; connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -894,6 +1337,7 @@ test.big 385477733 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -907,6 +1351,7 @@ test.big 1705165209 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -1015,8 +1460,439 @@ ALTER TABLE t1 MODIFY a INT DEFAULT 0; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; -DROP TABLE t1; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; +DROP TABLE t1; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; +CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 (a) VALUES (NULL), (NULL); +ALTER TABLE t1 DROP a, ADD COLUMN a INT; +DELETE FROM t1; +BEGIN; +INSERT INTO t1 SET a=NULL; +ROLLBACK; +DELETE FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES(347,''); +ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT; +SELECT * FROM t1; +a +347 +DROP TABLE t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b; +SELECT * FROM t1; +b c a +NULL NULL NULL +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SET @t=REPEAT('x',@@innodb_page_size / 2); +INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t); +ALTER TABLE t1 ADD COLUMN a INT FIRST; +UPDATE t1 SET a = 0; +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET t = @x; +ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1; +ALTER TABLE t1 ADD COLUMN t TEXT; +SELECT * FROM t1; +i t +1 NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET a=NULL; +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42; +INSERT INTO t1 SET a=NULL; +UPDATE t1 SET a=a+2; +SELECT * FROM t1; +a c +3 42 +4 42 +DROP TABLE t1; +CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET i=1; +ALTER TABLE t1 ADD COLUMN b BIT FIRST; +ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL; +SELECT * FROM t1; +b i v +NULL 1 1 +DROP TABLE t1; +CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME; +ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST; +ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (4,4,4); +ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT; +DELETE FROM t1; +ALTER TABLE t1 DROP COLUMN f4; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT; +ALTER TABLE t1 DROP f4; +ALTER TABLE t1 DROP f1; +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 () VALUES (),(); +SELECT * FROM t1; +f +NULL +NULL +ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id); +SELECT * FROM t1; +id f +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET f=NULL; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 SET f=NULL; +SELECT * FROM t1; +f +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k)) +ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 (pk) VALUES (1); +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, +f2 CHAR(255), +f3 BIGINT, +f4 INT, +f5 CHAR(255), +f6 CHAR(255), +f7 CHAR(255) NOT NULL, +f8 INT, +f9 CHAR(10) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'), +(2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'), +(3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'), +(4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'), +(5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'), +(6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'), +(7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'), +(8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'), +(9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'), +(10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'), +(11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'), +(12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'), +(13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'), +(14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'), +(15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o'); +DELETE FROM t1 WHERE pk=1; +InnoDB 0 transactions not purged +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'); +ALTER TABLE t1 DROP COLUMN f1; +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, f2 CHAR(32) NOT NULL, +f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL, +f7 CHAR(32), f8 CHAR(32) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES +(1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''), +(3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''), +(5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''), +(7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''), +(9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''), +(11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''), +(13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''), +(15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''), +(17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''), +(19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''), +(21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''), +(23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''), +(25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''), +(51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''), +(53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''), +(55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''), +(57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''), +(59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''), +(61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''), +(63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''), +(101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''), +(103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''), +(105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''), +(107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''), +(109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''), +(151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''), +(153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''), +(155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''), +(157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''), +(159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''), +(201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''), +(203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''), +(205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''), +(207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''), +(252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''), +(254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''), +(256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''), +(258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''), +(301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''), +(303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''), +(305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''), +(307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''), +(351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''), +(353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''), +(355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''), +(357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''), +(359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''), +(361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''), +(363,75,'',66,76,52,'','',''); +ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar '; +UPDATE t1 SET f1 = 0; +ALTER TABLE t1 DROP COLUMN x; +DROP TABLE t1; +CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST; +DROP TABLE t1; +CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 CHANGE COLUMN a a INT; +DELETE FROM t1 WHERE a = NULL OR a IS NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT, +e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (0,0); +ALTER TABLE t1 MODIFY a INT AFTER b; +ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL; +ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 ADD COLUMN b INT; +ALTER TABLE t1 MODIFY COLUMN a INT NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000)); +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 VALUES (2,20); +ALTER TABLE t1 ADD COLUMN vpk INT AS (pk); +ALTER TABLE t1 DROP COLUMN i; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD f DATE AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD COLUMN f INT AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 MODIFY COLUMN t TEXT; +DROP TABLE t1; +CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES(127,6502),(-128,33101); +ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345, +MODIFY g BIGINT UNSIGNED DEFAULT 1234567; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f g +127 6502 +-128 33101 +DROP TABLE t1; +CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (b'10000000'),(b'00000001'); +ALTER TABLE t1 MODIFY f BIT(16); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000'); +SELECT HEX(f) FROM t1; +HEX(f) +80 +1 +80AF +80 +ALTER TABLE t1 MODIFY f SMALLINT; +ERROR 22003: Out of range value for column 'f' at row 3 +ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED; +affected rows: 4 +info: Records: 4 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f +128 +1 +32943 +128 +ALTER TABLE t1 MODIFY f BIT; +ERROR 22001: Data too long for column 'f' at row 1 +ALTER TABLE t1 MODIFY f BIT(15); +ERROR 22001: Data too long for column 'f' at row 3 +DELETE FROM t1 LIMIT 3; +ALTER TABLE t1 MODIFY f BIT(15); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT HEX(f) FROM t1; +HEX(f) +80 +DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES(1,'a'); +ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT; +INSERT INTO t1 VALUES(2,'bah',3); +SELECT * FROM t1; +a b c +1 a NULL +2 bah 3 +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES('barf'); +ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +b a vb +NULL barf NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 ADD COLUMN c TEXT FIRST; +ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST; +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b)) +DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET a='fubar',b=42; +ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +a b vb +fubar 42 42 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 MODIFY b INT FIRST; +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -1145,8 +2021,8 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 INSERT INTO t1 SET id=9; ALTER TABLE t1 DROP c3; -affected rows: 9 -info: Records: 9 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1199,6 +2075,7 @@ UPDATE t2 SET c1 = repeat(id, 4000); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -1209,6 +2086,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -1220,6 +2098,7 @@ UPDATE t2 SET d1 = repeat(id, 200); connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -1230,6 +2109,7 @@ ROLLBACK; connection analyze; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; @@ -1351,6 +2231,7 @@ COMMIT; connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -1374,6 +2255,7 @@ test.big 385477733 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -1387,6 +2269,7 @@ test.big 1705165209 connection analyze; ANALYZE TABLE big; Table Op Msg_type Msg_text +test.big analyze status Engine-independent statistics collected test.big analyze status OK SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/big'; @@ -1495,13 +2378,466 @@ ALTER TABLE t1 MODIFY a INT DEFAULT 0; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; -DROP TABLE t1; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; SET GLOBAL innodb_instant_alter_column_allowed = add_last; +ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_atler_column_allowed=add_last. Try ALGORITHM=INPLACE +ALTER TABLE t1 MODIFY a INT DEFAULT 0; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; +DROP TABLE t1; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; +CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 (a) VALUES (NULL), (NULL); +ALTER TABLE t1 DROP a, ADD COLUMN a INT; +DELETE FROM t1; +BEGIN; +INSERT INTO t1 SET a=NULL; +ROLLBACK; +DELETE FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES(347,''); +ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT; +SELECT * FROM t1; +a +347 +DROP TABLE t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b; +SELECT * FROM t1; +b c a +NULL NULL NULL +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SET @t=REPEAT('x',@@innodb_page_size / 2); +INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t); +ALTER TABLE t1 ADD COLUMN a INT FIRST; +UPDATE t1 SET a = 0; +DROP TABLE t1; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET t = @x; +ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1; +ALTER TABLE t1 ADD COLUMN t TEXT; +SELECT * FROM t1; +i t +1 NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET a=NULL; +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42; +INSERT INTO t1 SET a=NULL; +UPDATE t1 SET a=a+2; +SELECT * FROM t1; +a c +3 42 +4 42 +DROP TABLE t1; +CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET i=1; +ALTER TABLE t1 ADD COLUMN b BIT FIRST; +ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL; +SELECT * FROM t1; +b i v +NULL 1 1 +DROP TABLE t1; +CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME; +ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST; +ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (4,4,4); +ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT; +DELETE FROM t1; +ALTER TABLE t1 DROP COLUMN f4; +DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT; +ALTER TABLE t1 DROP f4; +ALTER TABLE t1 DROP f1; +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 () VALUES (),(); +SELECT * FROM t1; +f +NULL +NULL +ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id); +SELECT * FROM t1; +id f +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET f=NULL; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 SET f=NULL; +SELECT * FROM t1; +f +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k)) +ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 (pk) VALUES (1); +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, +f2 CHAR(255), +f3 BIGINT, +f4 INT, +f5 CHAR(255), +f6 CHAR(255), +f7 CHAR(255) NOT NULL, +f8 INT, +f9 CHAR(10) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'), +(2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'), +(3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'), +(4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'), +(5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'), +(6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'), +(7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'), +(8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'), +(9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'), +(10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'), +(11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'), +(12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'), +(13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'), +(14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'), +(15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o'); +DELETE FROM t1 WHERE pk=1; +InnoDB 0 transactions not purged +INSERT INTO t1 VALUES +(1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'); +ALTER TABLE t1 DROP COLUMN f1; +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, +f1 INT, f2 CHAR(32) NOT NULL, +f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL, +f7 CHAR(32), f8 CHAR(32) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''), +(3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''), +(5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''), +(7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''), +(9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''), +(11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''), +(13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''), +(15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''), +(17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''), +(19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''), +(21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''), +(23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''), +(25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''), +(51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''), +(53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''), +(55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''), +(57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''), +(59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''), +(61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''), +(63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''), +(101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''), +(103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''), +(105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''), +(107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''), +(109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''), +(151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''), +(153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''), +(155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''), +(157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''), +(159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''), +(201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''), +(203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''), +(205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''), +(207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''), +(252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''), +(254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''), +(256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''), +(258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''), +(301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''), +(303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''), +(305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''), +(307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''), +(351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''), +(353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''), +(355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''), +(357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''), +(359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''), +(361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''), +(363,75,'',66,76,52,'','',''); +ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar '; +UPDATE t1 SET f1 = 0; +ALTER TABLE t1 DROP COLUMN x; +DROP TABLE t1; +CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST; +DROP TABLE t1; +CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 CHANGE COLUMN a a INT; +DELETE FROM t1 WHERE a = NULL OR a IS NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT, +e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (0,0); +ALTER TABLE t1 MODIFY a INT AFTER b; +ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL; +ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 ADD COLUMN b INT; +ALTER TABLE t1 MODIFY COLUMN a INT NULL; +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000)); +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 VALUES (2,20); +ALTER TABLE t1 ADD COLUMN vpk INT AS (pk); +ALTER TABLE t1 DROP COLUMN i; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD f DATE AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD COLUMN f INT AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; +CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 MODIFY COLUMN t TEXT; +DROP TABLE t1; +CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES(127,6502),(-128,33101); +ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345, +MODIFY g BIGINT UNSIGNED DEFAULT 1234567; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f g +127 6502 +-128 33101 +DROP TABLE t1; +CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (b'10000000'),(b'00000001'); +ALTER TABLE t1 MODIFY f BIT(16); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000'); +SELECT HEX(f) FROM t1; +HEX(f) +80 +1 +80AF +80 +ALTER TABLE t1 MODIFY f SMALLINT; +ERROR 22003: Out of range value for column 'f' at row 3 +ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED; +affected rows: 4 +info: Records: 4 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f +128 +1 +32943 +128 +ALTER TABLE t1 MODIFY f BIT; +ERROR 22001: Data too long for column 'f' at row 1 +ALTER TABLE t1 MODIFY f BIT(15); +ERROR 22001: Data too long for column 'f' at row 3 +DELETE FROM t1 LIMIT 3; +ALTER TABLE t1 MODIFY f BIT(15); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f BIT(8); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT HEX(f) FROM t1; +HEX(f) +80 +DROP TABLE t1; +CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT HEX(c) FROM t1; +HEX(c) +1 +DROP TABLE t1; +CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +c f d +scary -42 NULL +gory 64802 NULL +fury -8388608 1970-01-01 03:00:42 +DROP TABLE t1; +CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +ALTER TABLE t1 CHANGE t s SMALLINT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY +ALTER TABLE t1 CHANGE m i INT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +affected rows: 1 +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +@table_id1 = @table_id2 @table_id2 = @table_id3 +0 0 +INSERT IGNORE INTO t1 VALUES (0, -123456); +Warnings: +Warning 1062 Duplicate entry '-123456' for key 'm' +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +Warnings: +Warning 1264 Out of range value for column 's' at row 1 +Warning 1264 Out of range value for column 'i' at row 1 +SELECT * FROM t1; +s i +-42 123456 +32767 2147483647 +DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 (c) VALUES(1),(2),(3); +ALTER TABLE t1 MODIFY c BIGINT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +UPDATE t1 SET c=c+1; +ERROR 23000: Duplicate entry '2' for key 'c' +SELECT * FROM t1; +a b c +1 NULL 1 +2 1 2 +3 2 4 +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES(1,'a'); +ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT; +INSERT INTO t1 VALUES(2,'bah',3); +SELECT * FROM t1; +a b c +1 a NULL +2 bah 3 +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES('barf'); +ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +b a vb +NULL barf NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 ADD COLUMN c TEXT FIRST; +ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST; +DROP TABLE t1; +CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b)) +DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET a='fubar',b=42; +ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT; +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +a b vb +fubar 42 42 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 MODIFY b INT FIRST; +DROP TABLE t1; disconnect analyze; +# +# MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT +# +CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: DROP INDEX. Try ALGORITHM=NOCOPY +ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY; +DROP TABLE t1; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -60 +206 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance; +# +# MDEV-18266: Changing an index comment unnecessarily rebuilds index +# +CREATE TABLE t1(a INT, b INT) ENGINE=INNODB; +CREATE INDEX i1 ON t1(a) COMMENT 'comment1'; +ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2', ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + KEY `i1` (`a`) COMMENT 'comment2' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; |