# Scenario 1: Table with 0 rows # Case 1: Default algorithm CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 DROP TABLE t1; CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 DROP TABLE t1; # Case 2: ALGORITHM clause has same value as default CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 DROP TABLE t1; # Case 3: ALGORITHM clause has different value from default CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 DROP TABLE t1; # Scenario 2: Table with 1 or more rows # Case 1: Default algorithm CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 DROP TABLE t1; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 DROP TABLE t1; # Case 2: ALGORITHM clause has same value as default CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 DROP TABLE t1; # Case 3: ALGORITHM clause has different value from default CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 DROP TABLE t1; # Scenario 3: Table with 1 delete-marked record # Case 1: Default algorithm CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SET GLOBAL innodb_purge_stop_now=1; DELETE FROM t1 WHERE c1 = 1; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SET GLOBAL innodb_purge_stop_now=1; DELETE FROM t1 WHERE c1 = 1; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1; # Case 2: ALGORITHM clause has same value as default CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SET GLOBAL innodb_purge_stop_now=1; DELETE FROM t1 WHERE c1 = 1; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1; # Case 3: ALGORITHM clause has different value from default CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); SET GLOBAL innodb_purge_stop_now=1; DELETE FROM t1 WHERE c1 = 1; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 0 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; NAME TOTAL_ROW_VERSIONS test/t1 1 SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1;