--source include/have_debug.inc # This test checks the default algorithm used by ALTER TABLE .. ADD/DROP COLUMN. # It also check the behavior when the algorithm clause is specified # In a table with 0 rows, the default algorithm is INPLACE. # In a table with 1 or more rows, the default algorithm is INSTANT. # The table's TOTAL_ROW_VERSIONS is used to determine if INSTANT is used or # INPLACE is used --echo # Scenario 1: Table with 0 rows --echo # Case 1: Default algorithm CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; CREATE TABLE t1 (c1 INT); SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # Scenario 2: Table with 1 or more rows --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; DROP TABLE t1; --echo # Scenario 3: Table with 1 delete-marked record --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1; --echo # 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'; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'; SET GLOBAL innodb_purge_run_now=1; DROP TABLE t1;