Debian Codegraph @

mysql-test/suite/innodb/r/ddl_add_drop_basic.result

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