Debian Codegraph @

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

raw
1 ###############################################################################
2 # This test checks the behaviour of Generated Columns with async replication.
3 #
4 # Test
5 # 0. This test requires 2 servers.(Master and slave)
6 # 1. Check the behaviour of replication with Stored Columns.
7 # 1.1 Create table st1 and execute basic DMLs and XA transactions on master.
8 # Test if data is replicated properly by verifying data on both servers.
9 # st1: Table with stored column genarated on primary key
10 # 1.2 Create table st2 and execute basic DMLs and XA transactions on master.
11 # Test if data is replicated properly by verifying data on both servers.
12 # st2: Table with stored column as primary key
13 # 1.3 Create table st3 and execute basic DMLs and XA transactions on master.
14 # Test if data is replicated properly by verifying data on both servers.
15 # st3: Table with stored column as unique key
16 # 1.4 Create table st4 and execute basic DMLs and XA transactions on master.
17 # Test if data is replicated properly by verifying data on both servers.
18 # st4: Table with Stored column as foreign key
19 # 2. Check the behaviour of replication with Virtual Columns.
20 # 2.1 Create table vt1 and execute basic DMLs and XA transactions on master.
21 # Test if data is replicated properly by verifying data on both servers.
22 # vt1: Table with virtual column genarated on primary key
23 # 2.2 Create table vt2 and execute basic DMLs and XA transactions on master.
24 # Test if data is replicated properly by verifying data on both servers.
25 # vt2: Table with virtual column as unique key
26 # 2.3 Create table vt3 and execute basic DMLs and XA transactions on master.
27 # Test if data is replicated properly by verifying data on both servers.
28 # vt3: Table with Index on virtual column and foreign key constraints
29 # 3. Check the behaviour of replication with Generated Columns
30 # 3.1 Create table gt1 and execute basic DMLs and XA transactions on master.
31 # Test if data is replicated properly by verifying data on both servers.
32 # gt1: Table with both stored and virtual columns.
33 # 4. Clean up.
34 ###############################################################################
35
36 --source include/master-slave.inc
37
38 CALL mtr.add_suppression("Statement is unsafe because it is being used inside a XA transaction");
39
40 # Scenario 1.1 :
41
42 --echo # Table st1: stored column genarated on primary key
43
44 CREATE TABLE st1 (c1 INT PRIMARY KEY);
45 ALTER TABLE st1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1+1) STORED, INDEX(c2));
46
47 # Perform some basic DML on st1
48 INSERT INTO st1(c1) VALUES(1);
49 INSERT INTO st1(c1) VALUES(2);
50
51 --disable_warnings
52
53 # Perform XA Transaction on st1
54 XA START 'xstatement';
55 UPDATE st1 SET c1=3 WHERE c2=3;
56 UPDATE st1 SET c1=4 WHERE c1=3;
57 DELETE FROM st1 WHERE c2=5;
58 XA END 'xstatement';
59 XA PREPARE 'xstatement';
60 XA COMMIT 'xstatement';
61
62 --source include/sync_slave_sql_with_master.inc
63
64 --echo # check that st1 exists and has same values in both servers
65 --let $diff_tables=master:st1, slave:st1
66 --source include/diff_tables.inc
67
68 # Scenario 1.2 :
69
70 --echo # Table st2: stored column as primary key
71 --source include/rpl_connection_master.inc
72
73 CREATE TABLE st2 (
74 c1 VARCHAR(10),
75 c2 CHAR(2) GENERATED ALWAYS AS (SUBSTRING(c1,1,2)) STORED PRIMARY KEY
76 );
77
78 # Perform some basic DML on st2
79 INSERT INTO st2(c1) VALUES("abcd");
80 INSERT INTO st2(c1) VALUES("efgh");
81
82 # Perform XA Transaction on st2
83 XA START 'xstatement';
84 UPDATE st2 SET c1="abgh" WHERE c1='abcd';
85 UPDATE st2 SET c1="cdgh" WHERE c2='ef';
86 DELETE FROM st2 WHERE c2='cd';
87 XA END 'xstatement';
88 XA PREPARE 'xstatement';
89 XA COMMIT 'xstatement';
90
91 --source include/sync_slave_sql_with_master.inc
92 --echo # check that st2 exists and has same values in both servers
93 --let $diff_tables=master:st2, slave:st2
94 --source include/diff_tables.inc
95
96 # Scenario 1.3 :
97
98 --echo # Table st3: stored column as unique
99 --source include/rpl_connection_master.inc
100
101 CREATE TABLE st3 (
102 c1 INT AUTO_INCREMENT PRIMARY KEY,
103 c2 VARCHAR(10) NOT NULL,
104 c3 VARCHAR(10) NOT NULL,
105 c4 VARCHAR(21) AS (CONCAT(c2,c3)) STORED UNIQUE
106 );
107
108 # Perform some basic DML on st3
109 INSERT INTO st3(c2,c3) VALUES('first','insert');
110 INSERT INTO st3(c2,c3) VALUES('second','insert');
111 # Error for duplicate entry
112 --error ER_DUP_ENTRY
113 INSERT INTO st3(c2,c3) VALUES('first','insert');
114
115 # Perform XA Transaction on st3
116 XA START 'xstatement';
117 UPDATE st3 SET c3='update' WHERE c2='first';
118 UPDATE st3 SET c3='update' WHERE c2='second';
119 DELETE FROM st3 WHERE c2='first';
120 DELETE FROM st3 WHERE c3='update';
121 XA END 'xstatement';
122 XA PREPARE 'xstatement';
123 XA COMMIT 'xstatement';
124
125 --source include/sync_slave_sql_with_master.inc
126
127 --echo # check that st3 exists and has same values in both servers
128 --let $diff_tables=master:st3, slave:st3
129 --source include/diff_tables.inc
130
131 # Scenario 1.4 :
132
133 --echo # Table st4: Stored column as foreign key
134 --source include/rpl_connection_master.inc
135
136 # Parent table
137 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a-10) STORED, PRIMARY KEY(b));
138
139 CREATE TABLE st4 (
140 c1 INT PRIMARY KEY,
141 c2 INT GENERATED ALWAYS AS (c1%10) STORED,
142 FOREIGN KEY(c2) REFERENCES t1(b)
143 );
144
145 # Insert on parent table
146 INSERT INTO t1(a) VALUES(11);
147 INSERT INTO t1(a) VALUES(12);
148
149 # Perform some basic DML on st4
150 INSERT INTO st4(c1) VALUES(31);
151 INSERT INTO st4(c1) VALUES(32);
152
153 # Perform XA Transaction on st4
154 XA START 'xstatement';
155 UPDATE st4 SET c1=21 WHERE c2=1;
156 DELETE FROM st4 WHERE c2=2;
157 DELETE FROM st4 WHERE c1=21;
158 XA END 'xstatement';
159 XA PREPARE 'xstatement';
160 XA COMMIT 'xstatement';
161
162 --source include/sync_slave_sql_with_master.inc
163
164 --echo # check that st4 exists and has same values in both servers
165 --let $diff_tables=master:st4, slave:st4
166 --source include/diff_tables.inc
167
168 # Scenario 2.1 :
169
170 --echo # Table vt1: virtual column genarated on primary key
171 --source include/rpl_connection_master.inc
172
173 CREATE TABLE vt1 (c1 DATE PRIMARY KEY, c2 INT AS (year(c1)) VIRTUAL);
174
175 # Perform some basic DML on vt1
176 INSERT INTO vt1(c1) VALUES('2008-09-02');
177 INSERT INTO vt1(c1) VALUES('1998-06-17');
178 UPDATE vt1 SET c1='2009-09-02' WHERE c2=2008;
179
180 # Perform XA transaction on vt1
181 XA START 'xstatement';
182 UPDATE vt1 SET c1='1999-06-17' WHERE c1='1998-06-17';
183 DELETE FROM vt1 WHERE c1='2009-09-02';
184 DELETE FROM vt1 WHERE c2=1999;
185 XA END 'xstatement';
186 XA PREPARE 'xstatement';
187 XA COMMIT 'xstatement';
188
189 --source include/sync_slave_sql_with_master.inc
190 --echo # check that vt1 exists and has same values in both servers
191 --let $diff_tables=master:vt1, slave:vt1
192 --source include/diff_tables.inc
193
194 # Scenario 2.2 :
195
196 --echo # Table vt2: virtual column as unique key
197 --source include/rpl_connection_master.inc
198
199 CREATE TABLE vt2 (
200 c1 TEXT, PRIMARY KEY(c1(20)),
201 c2 TEXT GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) VIRTUAL,
202 UNIQUE(c2(2))
203 );
204
205 # Perform some basic DML on vt2
206 INSERT INTO vt2(c1) VALUES('first insert');
207 INSERT INTO vt2(c1) VALUES('second insert');
208 UPDATE vt2 SET c1='first update' WHERE c1='first insert';
209
210 # Perform XA Transaction on vt2
211 XA START 'xstatement';
212 UPDATE vt2 SET c1='second update' WHERE c2='se';
213 DELETE FROM vt2 WHERE c1='first update';
214 DELETE FROM vt2 WHERE c2='se';
215 XA END 'xstatement';
216 XA PREPARE 'xstatement';
217 XA COMMIT 'xstatement';
218
219 --source include/sync_slave_sql_with_master.inc
220 --echo # check that vt2 exists and has same values in both servers
221 --let $diff_tables=master:vt2, slave:vt2
222 --source include/diff_tables.inc
223
224 # Scenario 2.3 :
225
226 --echo # Table vt3: Virtual Column with foreign keys
227 --source include/rpl_connection_master.inc
228
229 #Parent table
230 CREATE TABLE t2(
231 a INT PRIMARY KEY,
232 b INT GENERATED ALWAYS AS (a+1) VIRTUAL,
233 INDEX(b)
234 );
235
236 CREATE TABLE vt3(
237 a INT PRIMARY KEY,
238 b INT,
239 FOREIGN KEY(b) REFERENCES t2(a) ON UPDATE SET NULL ON DELETE RESTRICT,
240 c INT AS (a*b) VIRTUAL
241 );
242
243 # Insert on Parent table
244 INSERT INTO t2(a) VALUES(1);
245 INSERT INTO t2(a) VALUES(3);
246
247 # Perform some basic DML on vt3
248 INSERT INTO vt3(a,b) VALUES(100,1),(200,3);
249 UPDATE t2 SET a=2 WHERE b=4;
250
251 UPDATE vt3 SET b=2 WHERE b IS NULL;
252 UPDATE t2,vt3 SET vt3.a=t2.a WHERE t2.b=vt3.b;
253
254 # Error due to foreign key constraint
255 --error ER_ROW_IS_REFERENCED_2
256 DELETE FROM t2 WHERE a=2;
257
258 # Perform XA Transaction on vt3
259 XA START 'xstatement';
260 DELETE FROM vt3 WHERE a=1;
261 DELETE FROM vt3 WHERE c=100;
262 XA END 'xstatement';
263 XA PREPARE 'xstatement';
264 XA COMMIT 'xstatement';
265
266 --source include/sync_slave_sql_with_master.inc
267 --echo # check that vt3 exists and has same values in both servers
268 --let $diff_tables=master:vt3, slave:vt3
269 --source include/diff_tables.inc
270
271 # Scenario 3.1 :
272
273 --echo # Table gt1: gt1: both stored and virtual columns
274 --source include/rpl_connection_master.inc
275
276 CREATE TABLE gt1 (a INT, b INT AS (a+1) STORED PRIMARY KEY);
277 ALTER TABLE gt1 ADD COLUMN c INT GENERATED ALWAYS AS (b+1) VIRTUAL;
278 ALTER TABLE gt1 ADD COLUMN d INT GENERATED ALWAYS AS (c+10) STORED;
279
280 # Perform some basic DML on gt1
281 --let $i=5
282 while ($i>0){
283 --eval INSERT INTO gt1(a) VALUES($i)
284 --dec $i
285 }
286 UPDATE gt1 SET a=21 WHERE b=2;
287 UPDATE gt1 SET a=22 WHERE c=4;
288 UPDATE gt1 SET a=23 WHERE d=15;
289
290 # Perform XA Transaction on gt1
291 XA START 'xstatement';
292 UPDATE gt1 SET a=24 WHERE a=4;
293 DELETE FROM gt1 WHERE a=5;
294 DELETE FROM gt1 WHERE b=24;
295 DELETE FROM gt1 WHERE c=24;
296 DELETE FROM gt1 WHERE d=33;
297 XA END 'xstatement';
298 XA PREPARE 'xstatement';
299 XA COMMIT 'xstatement';
300
301 --enable_warnings
302
303 --source include/sync_slave_sql_with_master.inc
304 --echo # check that gt1 exists and has same values in both servers
305 --let $diff_tables=master:gt1, slave:gt1
306 --source include/diff_tables.inc
307
308 --echo # Clean Up
309
310 --source include/rpl_connection_master.inc
311 DROP TABLE st1,st2,st3,st4;
312 DROP TABLE vt1,vt2,vt3;
313 DROP TABLE gt1;
314 DROP TABLE t1,t2;
315
316 --source include/rpl_end.inc