1FLUSH TABLES; 2# 3# MDEV-11369: Instant ADD COLUMN for InnoDB 4# MDEV-15562: Instant DROP COLUMN or changing the order of columns 5# 6connect to_be_killed, localhost, root; 7SET @old_instant= 8(SELECT variable_value FROM information_schema.global_status 9WHERE variable_name = 'innodb_instant_alter_column'); 10CREATE TABLE empty (id INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB; 11CREATE TABLE once LIKE empty; 12CREATE TABLE twice LIKE empty; 13CREATE TABLE thrice LIKE empty; 14INSERT INTO once SET id=1,c2=1; 15INSERT INTO twice SET id=1,c2=1; 16INSERT INTO thrice SET id=1,c2=1; 17ALTER TABLE empty ADD COLUMN (d1 INT DEFAULT 15); 18ALTER TABLE once ADD COLUMN (d1 INT DEFAULT 20); 19ALTER TABLE twice ADD COLUMN (d1 INT DEFAULT 20); 20ALTER TABLE thrice ADD COLUMN (d1 INT DEFAULT 20); 21ALTER TABLE twice ADD COLUMN 22(d2 INT NOT NULL DEFAULT 10, 23d3 VARCHAR(15) NOT NULL DEFAULT 'var och en char'); 24ALTER TABLE thrice ADD COLUMN 25(d2 INT NOT NULL DEFAULT 10, 26d3 TEXT NOT NULL DEFAULT 'con'); 27ALTER TABLE thrice DROP c2, DROP d3, CHANGE d2 d3 INT NOT NULL FIRST; 28SELECT variable_value-@old_instant instants 29FROM information_schema.global_status 30WHERE variable_name = 'innodb_instant_alter_column'; 31instants 327 33BEGIN; 34INSERT INTO empty set id=0,c2=42; 35UPDATE once set c2=c2+1; 36UPDATE twice set c2=c2+1; 37UPDATE thrice set d3=d3+1; 38INSERT INTO twice SET id=2,c2=0,d3=''; 39INSERT INTO thrice SET id=2,d3=0; 40DELETE FROM empty; 41DELETE FROM once; 42DELETE FROM twice; 43DELETE FROM thrice; 44connection default; 45SET GLOBAL innodb_flush_log_at_trx_commit=1; 46CREATE TABLE foo(a INT PRIMARY KEY) ENGINE=InnoDB; 47# Kill the server 48disconnect to_be_killed; 49# restart 50SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; 51SET GLOBAL innodb_purge_rseg_truncate_frequency=1; 52DROP TABLE foo; 53InnoDB 0 transactions not purged 54SET GLOBAL innodb_purge_rseg_truncate_frequency=@saved_frequency; 55SELECT * FROM empty; 56id c2 d1 57SELECT * FROM once; 58id c2 d1 591 1 20 60SELECT * FROM twice; 61id c2 d1 d2 d3 621 1 20 10 var och en char 63SELECT * FROM thrice; 64d3 id d1 6510 1 20 66DROP TABLE empty, once, twice, thrice; 67