Debian Codegraph @

mysql-test/suite/innodb/t/ddl_add_drop_basic.test

raw
1 --source include/have_debug.inc
2 # This test checks the default algorithm used by ALTER TABLE .. ADD/DROP COLUMN.
3 # It also check the behavior when the algorithm clause is specified
4
5 # In a table with 0 rows, the default algorithm is INPLACE.
6 # In a table with 1 or more rows, the default algorithm is INSTANT.
7 # The table's TOTAL_ROW_VERSIONS is used to determine if INSTANT is used or
8 # INPLACE is used
9
10 --echo # Scenario 1: Table with 0 rows
11 --echo # Case 1: Default algorithm
12 CREATE TABLE t1 (c1 INT);
13 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
14 ALTER TABLE t1 ADD COLUMN c2 INT;
15 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
16
17 DROP TABLE t1;
18
19 CREATE TABLE t1 (c1 INT);
20 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
21 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT;
22 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
23
24 DROP TABLE t1;
25
26 --echo # Case 2: ALGORITHM clause has same value as default
27 CREATE TABLE t1 (c1 INT);
28 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
29 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE;
30 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
31
32 DROP TABLE t1;
33
34 --echo # Case 3: ALGORITHM clause has different value from default
35 CREATE TABLE t1 (c1 INT);
36 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
37 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT;
38 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
39
40 DROP TABLE t1;
41
42 --echo # Scenario 2: Table with 1 or more rows
43 --echo # Case 1: Default algorithm
44 CREATE TABLE t1 (c1 INT);
45 INSERT INTO t1 VALUES (1);
46 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
47 ALTER TABLE t1 ADD COLUMN c2 INT;
48 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
49
50 DROP TABLE t1;
51
52 CREATE TABLE t1 (c1 INT);
53 INSERT INTO t1 VALUES (1);
54 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
55 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT;
56 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
57
58 DROP TABLE t1;
59
60 --echo # Case 2: ALGORITHM clause has same value as default
61 CREATE TABLE t1 (c1 INT);
62 INSERT INTO t1 VALUES (1);
63 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
64 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT;
65 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
66
67 DROP TABLE t1;
68
69 --echo # Case 3: ALGORITHM clause has different value from default
70 CREATE TABLE t1 (c1 INT);
71 INSERT INTO t1 VALUES (1);
72 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
73 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE;
74 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
75
76 DROP TABLE t1;
77
78 --echo # Scenario 3: Table with 1 delete-marked record
79 --echo # Case 1: Default algorithm
80 CREATE TABLE t1 (c1 INT);
81 INSERT INTO t1 VALUES (1);
82 SET GLOBAL innodb_purge_stop_now=1;
83 DELETE FROM t1 WHERE c1 = 1;
84
85 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
86 ALTER TABLE t1 ADD COLUMN c2 INT;
87 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
88 SET GLOBAL innodb_purge_run_now=1;
89
90 DROP TABLE t1;
91
92 CREATE TABLE t1 (c1 INT);
93 INSERT INTO t1 VALUES (1);
94 SET GLOBAL innodb_purge_stop_now=1;
95 DELETE FROM t1 WHERE c1 = 1;
96
97 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
98 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=DEFAULT;
99 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
100 SET GLOBAL innodb_purge_run_now=1;
101
102 DROP TABLE t1;
103
104 --echo # Case 2: ALGORITHM clause has same value as default
105 CREATE TABLE t1 (c1 INT);
106 INSERT INTO t1 VALUES (1);
107 SET GLOBAL innodb_purge_stop_now=1;
108 DELETE FROM t1 WHERE c1 = 1;
109
110 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
111 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE;
112 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
113 SET GLOBAL innodb_purge_run_now=1;
114
115 DROP TABLE t1;
116
117 --echo # Case 3: ALGORITHM clause has different value from default
118 CREATE TABLE t1 (c1 INT);
119 INSERT INTO t1 VALUES (1);
120 SET GLOBAL innodb_purge_stop_now=1;
121 DELETE FROM t1 WHERE c1 = 1;
122
123 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
124 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT;
125 SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';
126 SET GLOBAL innodb_purge_run_now=1;
127
128 DROP TABLE t1;
129