Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Tests the row-based replication of tables with functional indexes and with
4 # extra columns or columns added with `ALTER TABLE`.
5 #
6 # ==== Requirements ====
7 #
8 # R1. Replication should work when hidden generated columns were added to the
9 # table definition in different orders on master and slave.
10 #
11 # R2. Replication should work when either master or slave has extra columns that
12 # were added after the creation of hidden generated columns.
13 #
14 # ==== Implementation ====
15 #
16 # TC1. Add one of the columns after `CREATE` with `ALTER TABLE`
17 # ------------------------------------------------------------------------
18 # 1) Create a table with functional indexes, in one of the nodes.
19 # 2) Create the same table in the other node, but with one less normal column.
20 # 3) Add the missing column with `ALTER TABLE`
21 # 4) Execute `INSERT`, `UPDATE` and `DELETE` statements.
22 # 5) Check the tables have the same content in both nodes.
23 # 6) Repeat the above process for all the combinations of:
24 # a) `binlog_row_image` being `FULL` or `MINIMAL`
25 # b) the node to which the column is added with `ALTER TABLE` being the
26 # master or the slave.
27 # c) the table has a `PRIMARY KEY` or it doesn't
28 #
29 # TC2. Add one extra column with `ALTER TABLE`
30 # ------------------------------------------------------------------------
31 # 1) Create a table with functional indexes, in the master.
32 # 2) Replicate the table.
33 # 3) Add one extra column with `ALTER TABLE`, in one of the nodes.
34 # 4) Execute `INSERT`, `UPDATE` and `DELETE` statements.
35 # 5) Check the tables have the same content in both nodes, for the common
36 # columns.
37 # 6) Repeat the above process for all the combinations of:
38 # a) `binlog_row_image` being `FULL` or `MINIMAL`
39 # b) the node to which the column is added with `ALTER TABLE` being the
40 # master or the slave.
41 # c) the table has a `PRIMARY KEY` or it doesn't
42 #
43 # ==== References ====
44 #
45 # BUG#29390506 HIDDEN GENERATED COLUMNS PREVENT SLAVE FROM HAVING EXTRA COLUMNS IN TABLES
46 #
47
48 --source include/master-slave.inc
49 --source include/have_binlog_format_row.inc
50
51 --let $initial_row_image = `SELECT @@global.binlog_row_image`
52
53 # Procedure that adds, after `CREATE*`, one of the columns with `ALTER TABLE`
54 # and tests the replication of `INSERT`, `UPDATE` and `DELETE`.
55 #
56 --write_file $MYSQLTEST_VARDIR/tmp/add_one_of_the_columns.inc PROCEDURE
57 --let $aditional_column_prop =
58
59 if ($primary_key == true) {
60 --let $aditional_column_prop = PRIMARY KEY
61 }
62
63 if ($alter_on == master) {
64 --let $dont_alter_on = slave
65 }
66 if ($alter_on == slave) {
67 --let $dont_alter_on = master
68 }
69
70 --echo #
71 --echo # Add one of the columns in the $alter_on, with binlog_row_image equal to
72 --echo # $row_image and aditional columns props equal to '$aditional_column_prop'
73 --echo #
74
75 --connection $alter_on
76 --echo [connection $alter_on]
77 SET @@session.sql_log_bin = 0;
78 eval CREATE TABLE t (a INT $aditional_column_prop,
79 INDEX ((a+1)), KEY ((a+10)));
80 ALTER TABLE t ADD COLUMN b INT;
81 SET @@session.sql_log_bin = 1;
82
83 --connection $dont_alter_on
84 --echo [connection $dont_alter_on]
85 SET @@session.sql_log_bin = 0;
86 eval CREATE TABLE t (a INT $aditional_column_prop, b INT,
87 INDEX ((a+1)), KEY ((a+10)));
88 SET @@session.sql_log_bin = 1;
89
90 --source include/rpl_connection_master.inc
91 INSERT INTO t VALUES (1, 2);
92 UPDATE t SET b = 1 WHERE a = 1;
93 INSERT INTO t VALUES (2, 2);
94 DELETE FROM t WHERE b = 2;
95 --source include/sync_slave_sql_with_master.inc
96
97 --let $diff_tables = master:t, slave:t
98 --source include/diff_tables.inc
99
100 --source include/rpl_connection_master.inc
101 DROP TABLE t;
102 --source include/sync_slave_sql_with_master.inc
103 #END OF
104 PROCEDURE
105
106 # Procedure that adds, after `CREATE*`, one extra column with `ALTER TABLE`
107 # and tests the replication of `INSERT`, `UPDATE` and `DELETE`.
108 #
109 --write_file $MYSQLTEST_VARDIR/tmp/add_one_extra_column.inc PROCEDURE
110 --let $aditional_column_prop =
111
112 if ($primary_key == true) {
113 --let $aditional_column_prop = PRIMARY KEY
114 }
115
116 --echo #
117 --echo # Add one extra column in the $alter_on, with binlog_row_image equal to
118 --echo # $row_image and aditional columns props equal to '$aditional_column_prop'
119 --echo #
120
121 --source include/rpl_connection_master.inc
122 eval CREATE TABLE t (a INT $aditional_column_prop, b INT,
123 INDEX ((a+1)), KEY ((a+10)));
124 --source include/sync_slave_sql_with_master.inc
125
126 --connection $alter_on
127 --echo [connection $alter_on]
128 SET @@session.sql_log_bin = 0;
129 ALTER TABLE t ADD COLUMN c INT DEFAULT 1;
130 SET @@session.sql_log_bin = 1;
131
132 --source include/rpl_connection_master.inc
133 INSERT INTO t (a, b) VALUES (1, 2);
134 UPDATE t SET b = 1 WHERE a = 1;
135 INSERT INTO t (a, b) VALUES (2, 2);
136 DELETE FROM t WHERE b = 2;
137
138 --let $rpl_diff_statement = SELECT a, b FROM t
139 --source include/rpl_diff.inc
140
141 --source include/rpl_connection_master.inc
142 DROP TABLE t;
143 --source include/sync_slave_sql_with_master.inc
144 #END OF
145 PROCEDURE
146
147 --echo #
148 --echo # TC1. Add one of the columns after `CREATE` with `ALTER TABLE`
149 --echo #
150
151 --let $k = 0
152 while ($k != 2) {
153 if ($k == 0) {
154 --let $row_image = FULL
155 }
156 if ($k == 1) {
157 --let $row_image = MINIMAL
158 }
159
160 --eval SET @@global.binlog_row_image = $row_image
161
162 --let $n = 0
163 while ($n != 2) {
164 if ($n == 0) {
165 --let $alter_on = master
166 }
167 if ($n == 1) {
168 --let $alter_on = slave
169 }
170
171 --let $l = 0
172 while ($l != 2) {
173 if ($l == 0) {
174 --let $primary_key = true
175 }
176 if ($l == 1) {
177 --let $primary_key =
178 }
179
180 --source $MYSQLTEST_VARDIR/tmp/add_one_of_the_columns.inc
181
182 --inc $l
183 }
184
185 --inc $n
186 }
187
188 --inc $k
189 }
190
191 --echo #
192 --echo # TC2. Add one extra column with `ALTER TABLE`
193 --echo #
194
195 --let $k = 0
196 while ($k != 2) {
197 if ($k == 0) {
198 --let $row_image = FULL
199 }
200 if ($k == 1) {
201 --let $row_image = MINIMAL
202 }
203
204 --eval SET @@global.binlog_row_image = $row_image
205
206 --let $n = 0
207 while ($n != 2) {
208 if ($n == 0) {
209 --let $alter_on = master
210 }
211 if ($n == 1) {
212 --let $alter_on = slave
213 }
214
215 --let $l = 0
216 while ($l != 2) {
217 if ($l == 0) {
218 --let $primary_key = true
219 }
220 if ($l == 1) {
221 --let $primary_key =
222 }
223
224 --source $MYSQLTEST_VARDIR/tmp/add_one_extra_column.inc
225
226 --inc $l
227 }
228
229 --inc $n
230 }
231
232 --inc $k
233 }
234
235 # Clean up
236 --remove_file $MYSQLTEST_VARDIR/tmp/add_one_of_the_columns.inc
237 --remove_file $MYSQLTEST_VARDIR/tmp/add_one_extra_column.inc
238 --replace_result $initial_row_image INITIAL_ROW_IMAGE
239 --eval SET @@global.binlog_row_image = $initial_row_image
240 --source include/rpl_end.inc