diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/instant_alter.result')
-rw-r--r-- | mysql-test/suite/innodb/r/instant_alter.result | 635 |
1 files changed, 634 insertions, 1 deletions
diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 5f89b31c142..f864221def5 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -236,6 +236,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'; @@ -246,6 +247,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'; @@ -257,6 +259,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'; @@ -267,6 +270,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'; @@ -386,6 +390,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'; @@ -409,6 +414,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'; @@ -422,6 +428,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'; @@ -523,6 +530,210 @@ 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) 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 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -705,6 +916,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'; @@ -715,6 +927,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'; @@ -726,6 +939,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'; @@ -736,6 +950,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'; @@ -855,6 +1070,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'; @@ -878,6 +1094,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'; @@ -891,6 +1108,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'; @@ -992,6 +1210,210 @@ 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) 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 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -1174,6 +1596,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'; @@ -1184,6 +1607,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'; @@ -1195,6 +1619,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'; @@ -1205,6 +1630,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'; @@ -1324,6 +1750,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'; @@ -1347,6 +1774,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'; @@ -1360,6 +1788,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'; @@ -1461,10 +1890,214 @@ 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) 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; disconnect analyze; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -78 +170 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; |