Debian Codegraph @

mysql-test/suite/rpl/t/rpl_instant_add_column.test

raw
1 # WL#11250 Support Instant Add Column
2
3 --source include/master-slave.inc
4
5 --echo # Scenario 1:
6 --echo # Create a small table, add some columns instantly
7 --echo #
8
9 --source include/rpl_connection_master.inc
10 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
11 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
12 --source include/sync_slave_sql_with_master.inc
13
14 --let $old_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
15
16 --source include/rpl_connection_master.inc
17 # ADD COLUMN INT
18 let $new_cols = 1;
19 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
20 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
21 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
22
23 INSERT INTO t1(a, c1) VALUES(6, 1);
24 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
25 SELECT c1 FROM t1 WHERE c1 = 1;
26 SHOW CREATE TABLE t1;
27 --source include/sync_slave_sql_with_master.inc
28
29 --let $new_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
30 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
31 SHOW CREATE TABLE t1;
32
33 --source include/rpl_connection_master.inc
34 --let $diff_tables=master:test.t1,slave:test.t1
35 --source include/diff_tables.inc
36
37 if($old_table_id_on_slave == $new_table_id_on_slave)
38 {
39 --echo # Table id not changed on slave
40 }
41
42 if($old_table_id_on_slave != $new_table_id_on_slave)
43 {
44 --echo # Table id changed on slave
45 }
46
47 --source include/rpl_connection_master.inc
48 DROP TABLE t1;
49 --source include/sync_slave_sql_with_master.inc
50
51
52 --echo # Scenario 2:
53 --echo # Create a small table, add some virtual columns instantly
54 --echo #
55
56 --source include/rpl_connection_master.inc
57 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
58 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
59 --source include/sync_slave_sql_with_master.inc
60
61 --let $old_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
62
63 --source include/rpl_connection_master.inc
64 # ADD VIRTUAL COLUMN
65 let $new_cols = 1;
66 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 INT, ADD COLUMN c2 FLOAT GENERATED ALWAYS AS ((1.4 * 2.8)) VIRTUAL, ALGORITHM=INSTANT;
67 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
68
69 INSERT INTO t1(a, c1) VALUES(6, 1);
70 SELECT * FROM t1;
71 SHOW CREATE TABLE t1;
72 --source include/sync_slave_sql_with_master.inc
73
74 --let $new_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
75 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
76 SHOW CREATE TABLE t1;
77
78 --source include/rpl_connection_master.inc
79 --let $diff_tables=master:test.t1,slave:test.t1
80 --source include/diff_tables.inc
81
82 if($old_table_id_on_slave == $new_table_id_on_slave)
83 {
84 --echo # Table id not changed on slave
85 }
86
87 if($old_table_id_on_slave != $new_table_id_on_slave)
88 {
89 --echo # Table id changed on slave
90 }
91
92 --source include/rpl_connection_master.inc
93 DROP TABLE t1;
94 --source include/sync_slave_sql_with_master.inc
95
96
97 --echo # Scenario 3:
98 --echo # Create a small table with RANGE and HASH partition,
99 --echo # and add INSTANT column
100 --echo #
101
102 --source include/rpl_connection_master.inc
103 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT)
104 PARTITION BY RANGE (a)
105 SUBPARTITION BY HASH (a)
106 SUBPARTITIONS 3 (
107 PARTITION p1 values less than (10),
108 PARTITION p2 values less than (20),
109 PARTITION p3 values less than maxvalue);
110 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
111 --let $old_table_id_on_master=`SELECT GROUP_CONCAT(table_id) FROM information_schema.innodb_tables WHERE name='test/t1';`
112 --source include/sync_slave_sql_with_master.inc
113
114 --let $old_table_id_on_slave=`SELECT GROUP_CONCAT(table_id) FROM information_schema.innodb_tables WHERE name='test/t1';`
115
116 --source include/rpl_connection_master.inc
117 # ADD VIRTUAL COLUMN
118 let $new_cols = 1;
119 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
120 INSERT INTO t1(a, b) VALUES(5, 5), (6, 6), (28, 28);
121 SELECT * FROM t1;
122 SHOW CREATE TABLE t1;
123 --let $new_table_id_on_master=`SELECT GROUP_CONCAT(table_id) FROM information_schema.innodb_tables WHERE name='test/t1';`
124 --source include/sync_slave_sql_with_master.inc
125
126 --let $new_table_id_on_slave=`SELECT GROUP_CONCAT(table_id) FROM information_schema.innodb_tables WHERE name='test/t1';`
127 SELECT * FROM t1;
128 SHOW CREATE TABLE t1;
129
130 --source include/rpl_connection_master.inc
131 --let $diff_tables=master:test.t1,slave:test.t1
132 --source include/diff_tables.inc
133
134 if($old_table_id_on_slave == $new_table_id_on_slave)
135 {
136 --echo # Table id not changed on slave
137 }
138
139 if($old_table_id_on_slave != $new_table_id_on_slave)
140 {
141 --echo # Table id changed on slave
142 }
143
144
145 if($old_table_id_on_master == $new_table_id_on_master)
146 {
147 --echo # Table id not changed on master
148 }
149
150 if($old_table_id_on_master != $new_table_id_on_master)
151 {
152 --echo # Table id changed on master
153 }
154
155 --source include/rpl_connection_master.inc
156 DROP TABLE t1;
157 --source include/sync_slave_sql_with_master.inc
158
159
160 --echo # Scenario 4:
161 --echo # Create a small table and add TIMESTAMP columns instantly
162 --echo #
163
164 --source include/rpl_connection_master.inc
165 SET @start_session_value = @@session.explicit_defaults_for_timestamp;
166
167 # SET explicit_defaults_for_timestamp to OFF
168 SET @@session.explicit_defaults_for_timestamp=OFF;
169 SELECT @@session.explicit_defaults_for_timestamp;
170 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
171 INSERT INTO t1 VALUES(1);
172 --source include/sync_slave_sql_with_master.inc
173
174 --let $old_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
175
176 --source include/rpl_connection_master.inc
177 # ADD VIRTUAL COLUMN
178 let $new_cols = 3;
179 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP, ADD COLUMN c2 TIMESTAMP NULL, ADD COLUMN c3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ALGORITHM=INSTANT;
180 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
181
182 INSERT INTO t1 VALUES(2,NULL,NULL,NULL);
183 SHOW CREATE TABLE t1;
184 --replace_column 2 # 4 #
185 SELECT * FROM t1;
186 --source include/sync_slave_sql_with_master.inc
187
188 --let $new_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
189 --replace_column 2 # 4 #
190 SELECT * FROM t1;
191 SHOW CREATE TABLE t1;
192
193 --source include/rpl_connection_master.inc
194 --let $diff_tables=master:test.t1,slave:test.t1
195 --source include/diff_tables.inc
196
197 if($old_table_id_on_slave == $new_table_id_on_slave)
198 {
199 --echo # Table id not changed on slave
200 }
201
202 if($old_table_id_on_slave != $new_table_id_on_slave)
203 {
204 --echo # Table id changed on slave
205 }
206
207 DROP TABLE t1;
208 --source include/sync_slave_sql_with_master.inc
209
210 # SET explicit_defaults_for_timestamp to ON
211 --source include/rpl_connection_master.inc
212 SET @@session.explicit_defaults_for_timestamp=ON;
213 SELECT @@session.explicit_defaults_for_timestamp;
214 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
215 INSERT INTO t1 VALUES(1);
216 --source include/sync_slave_sql_with_master.inc
217
218 --let $old_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
219
220 --source include/rpl_connection_master.inc
221 # ADD VIRTUAL COLUMN
222 let $new_cols = 3;
223 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP, ADD COLUMN c2 TIMESTAMP NOT NULL, ADD COLUMN c3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ALGORITHM=INSTANT;
224 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
225
226 INSERT INTO t1 VALUES(2,NULL,'2001-01-01 00:00:00',NULL);
227 SHOW CREATE TABLE t1;
228 --replace_column 2 # 4 #
229 SELECT * FROM t1;
230 --source include/sync_slave_sql_with_master.inc
231
232 --let $new_table_id_on_slave=`SELECT table_id FROM information_schema.innodb_tables WHERE name='test/t1';`
233 --replace_column 2 # 4 #
234 SELECT * FROM t1;
235 SHOW CREATE TABLE t1;
236
237 --source include/rpl_connection_master.inc
238 --let $diff_tables=master:test.t1,slave:test.t1
239 --source include/diff_tables.inc
240
241 if($old_table_id_on_slave == $new_table_id_on_slave)
242 {
243 --echo # Table id not changed on slave
244 }
245
246 if($old_table_id_on_slave != $new_table_id_on_slave)
247 {
248 --echo # Table id changed on slave
249 }
250
251 DROP TABLE t1;
252 --source include/sync_slave_sql_with_master.inc
253
254 --source include/rpl_connection_master.inc
255 SET @@session.explicit_defaults_for_timestamp = @start_session_value;
256 SELECT @@session.explicit_defaults_for_timestamp;
257
258 --source include/rpl_end.inc