Debian Codegraph @

mysql-test/suite/group_replication/t/gr_generated_column.test

raw
1 ###############################################################################
2 # This test checks the behaviour of Generated Columns with GR
3 #
4 # Test
5 # 0. This test requires 2 servers.(M1 and M2)
6 # 1. Check the behaviour of GR with Stored Columns.
7 # 1.1 Create table st1 and execute DMLs on M1.Test if data is replicated
8 # properly by verifying data on both the servers.
9 # st1: stored column genarated on primary key
10 # 1.2 Create table st2 and execute DMLs on M2.Test if data is replicated
11 # properly by verifying data on both the servers.
12 # st2: stored column as primary key
13 # 1.3 Create table st3 and execute DMLs on M1.Test if data is replicated
14 # properly by verifying data on both the servers.
15 # st3: stored column as unique
16 # 1.4 Create table st4 and execute DMLs on M2.Test if data is replicated
17 # properly by verifying data on both the servers.
18 # st4: Stored column as foreign key
19 # 2. Check the behaviour of GR with Virtual Columns.
20 # 2.1 Create table vt1 and execute DMLs on M1.Test if data is replicated
21 # properly by verifying data on both the servers.
22 # vt1: virtual column genarated on primary key
23 # 2.2 Create table vt2 and execute DMLs on M2.Test if data is replicated
24 # properly by verifying data on both the servers.
25 # vt2: virtual column as unique key
26 # 2.3 Create table vt3 and execute DMLs on M1.Test if data is replicated
27 # properly by verifying data on both the servers.
28 # vt3: Index on virtual column and foreign key constraints
29 # 3. Check the behaviour of GR with Generated Columns
30 # 3.1 Create table gt1 and execute DMLs on M2.Test if data is replicated
31 # properly by verifying data on both the servers.
32 # gt1: both stored and virtual columns.
33 # 4. Clean up.
34 ###############################################################################
35
36 --source include/have_group_replication_plugin.inc
37 --source include/group_replication.inc
38
39 # Scenario 1.1 :
40
41 --echo # Table st1: stored column genarated on primary key
42
43 CREATE TABLE st1 (c1 INT PRIMARY KEY);
44 ALTER TABLE st1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1+1) STORED,INDEX(c2));
45
46 # Perform some basic DML on st1
47 INSERT INTO st1(c1) VALUES(1);
48 INSERT INTO st1(c1) VALUES(2);
49 UPDATE st1 SET c1=3 WHERE c2=3;
50 UPDATE st1 SET c1=4 WHERE c1=3;
51 DELETE FROM st1 WHERE c2=5;
52
53 --source include/rpl_sync.inc
54 --echo # check that st1 exists and has same values in both servers
55 --let $diff_tables=server1:st1, server2:st1
56 --source include/diff_tables.inc
57
58 # Scenario 1.2 :
59
60 --echo # Table st2: stored column as primary key
61 --let $rpl_connection_name= server2
62 --source include/rpl_connection.inc
63
64 CREATE TABLE st2 (c1 VARCHAR(10),c2 CHAR(2) GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) STORED PRIMARY KEY);
65
66 # Perform some basic DML on st2
67 INSERT INTO st2(c1) VALUES("abcd");
68 INSERT INTO st2(c1) VALUES("efgh");
69 UPDATE st2 SET c1="abgh" WHERE c1='abcd';
70 UPDATE st2 SET c1="cdgh" WHERE c2='ef';
71 DELETE FROM st2 WHERE c2='cd';
72
73 --source include/rpl_sync.inc
74 --echo # check that st2 exists and has same values in both servers
75 --let $diff_tables=server1:st2, server2:st2
76 --source include/diff_tables.inc
77
78 # Scenario 1.3 :
79
80 --echo Table st3: stored column as unique
81 --let $rpl_connection_name= server1
82 --source include/rpl_connection.inc
83
84 CREATE TABLE st3 (
85 c1 INT AUTO_INCREMENT PRIMARY KEY,
86 c2 VARCHAR(10) NOT NULL,
87 c3 VARCHAR(10) NOT NULL,
88 c4 VARCHAR(21) AS (CONCAT(c2,c3)) STORED UNIQUE
89 );
90
91 # Perform some basic DML on st3
92 INSERT INTO st3(c2,c3) VALUES('first','insert');
93 INSERT INTO st3(c2,c3) VALUES('second','insert');
94
95 # Error for duplicate entry
96 --error ER_DUP_ENTRY
97 INSERT INTO st3 (c2,c3) VALUES('first','insert');
98
99 UPDATE st3 SET c3='update' WHERE c2='first';
100 UPDATE st3 SET c3='update' WHERE c2='second';
101 DELETE FROM st3 WHERE c2='first';
102 DELETE FROM st3 WHERE c3='update';
103
104 --source include/rpl_sync.inc
105 --echo # check that st3 exists and has same values in both servers
106 --let $diff_tables=server1:st3, server2:st3
107 --source include/diff_tables.inc
108
109 # Scenario 1.4 :
110
111 --echo Table st4: Stored column as foreign key
112 --let $rpl_connection_name= server2
113 --source include/rpl_connection.inc
114
115 # Parent table
116 CREATE TABLE t1 (a INT,b INT GENERATED ALWAYS AS (a-10)STORED, PRIMARY KEY(b));
117 CREATE TABLE st4 (c1 INT PRIMARY KEY,c2 INT GENERATED ALWAYS AS (c1%10)STORED,FOREIGN KEY(c2) REFERENCES t1(b));
118
119 # Insert on parent table
120 INSERT INTO t1(a) VALUES(11);
121 INSERT INTO t1(a) VALUES(12);
122
123 # Perform some basic DML on st4
124 INSERT INTO st4(c1) VALUES(31);
125 INSERT INTO st4(c1) VALUES(32);
126 UPDATE st4 SET c1=21 WHERE c2=1;
127 DELETE FROM st4 WHERE c2=2;
128 DELETE FROM st4 WHERE c1=21;
129
130 --source include/rpl_sync.inc
131 --echo # check that st4 exists and has same values in both servers
132 --let $diff_tables=server1:st4, server2:st4
133 --source include/diff_tables.inc
134
135 # Scenario 2.1 :
136
137 --echo Table vt1: virtual column genarated on primary key
138 --let $rpl_connection_name= server1
139 --source include/rpl_connection.inc
140
141 CREATE TABLE vt1 (c1 DATE PRIMARY KEY,c2 INT AS (year(c1)) VIRTUAL);
142
143 # Perform some basic DML on vt1
144 INSERT INTO vt1(c1) VALUES('2008-09-02');
145 INSERT INTO vt1(c1) VALUES('1998-06-17');
146 UPDATE vt1 SET c1='2009-09-02' WHERE c2=2008;
147 UPDATE vt1 SET c1='1999-06-17' WHERE c1='1998-06-17';
148 DELETE FROM vt1 WHERE c1='2009-09-02';
149 DELETE FROM vt1 WHERE c2=1999;
150
151 --source include/rpl_sync.inc
152 --echo # check that vt1 exists and has same values in both servers
153 --let $diff_tables=server1:vt1, server2:vt1
154 --source include/diff_tables.inc
155
156 # Scenario 2.2 :
157
158 --echo Table vt2: virtual column as unique key
159 --let $rpl_connection_name= server2
160 --source include/rpl_connection.inc
161
162 CREATE TABLE vt2 (c1 TEXT,PRIMARY KEY(c1(20)),c2 TEXT GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) VIRTUAL ,UNIQUE(c2(2)));
163
164 # Perform some basic DML on vt2
165 INSERT INTO vt2(c1) VALUES('first insert');
166 INSERT INTO vt2(c1) VALUES('second insert');
167 UPDATE vt2 SET c1='first update' WHERE c1='first insert';
168 UPDATE vt2 SET c1='second update' WHERE c2='se';
169 DELETE FROM vt2 WHERE c1='first update';
170 DELETE FROM vt2 WHERE c2='se';
171
172 --source include/rpl_sync.inc
173 --echo # check that vt2 exists and has same values in both servers
174 --let $diff_tables=server1:vt2, server2:vt2
175 --source include/diff_tables.inc
176
177 # Scenario 2.3 :
178
179 --echo Table vt3: Virtual Column with foreign keys
180 --let $rpl_connection_name= server1
181 --source include/rpl_connection.inc
182
183 #Parent table
184 CREATE TABLE t2(a INT PRIMARY KEY,b INT GENERATED ALWAYS AS (a+1) VIRTUAL,INDEX(b));
185 CREATE TABLE vt3(a INT PRIMARY KEY,b INT,FOREIGN KEY(b) REFERENCES t2(a) ON UPDATE RESTRICT ON DELETE RESTRICT,c INT AS (a*b)VIRTUAL);
186
187 # Insert on Parent table
188 INSERT INTO t2(a) VALUES(1);
189 INSERT INTO t2(a) VALUES(3);
190
191 # Perform some basic DML on vt3
192 INSERT INTO vt3(a,b) VALUES(100,1),(200,3);
193 UPDATE vt3 SET b=NULL WHERE b=3;
194 UPDATE t2 SET a=2 WHERE b=4;
195 UPDATE vt3 SET b=2 WHERE b IS NULL;
196 UPDATE t2,vt3 SET vt3.a=t2.a WHERE t2.b=vt3.b;
197
198 # Error due to foreign key constraint
199 --error ER_ROW_IS_REFERENCED_2
200 DELETE FROM t2 WHERE a=2;
201
202 DELETE FROM vt3 WHERE a=1;
203 DELETE FROM vt3 WHERE c=100;
204
205 --source include/rpl_sync.inc
206 --echo # check that vt3 exists and has same values in both servers
207 --let $diff_tables=server1:vt3, server2:vt3
208 --source include/diff_tables.inc
209
210 # Scenario 3.1 :
211
212 --echo Table gt1: gt1: both stored and virtual columns
213 --let $rpl_connection_name= server2
214 --source include/rpl_connection.inc
215
216 CREATE TABLE gt1 (a INT, b INT AS (a+1)STORED PRIMARY KEY);
217 ALTER TABLE gt1 ADD COLUMN c INT GENERATED ALWAYS AS (b+1)VIRTUAL;
218 ALTER TABLE gt1 ADD COLUMN d INT GENERATED ALWAYS AS (c+10) STORED;
219
220 # Perform some basic DML on gt1
221 --let $i=5
222 while ($i>0){
223 --eval INSERT INTO gt1(a) VALUES($i)
224 --dec $i
225 }
226 UPDATE gt1 SET a=21 WHERE b=2;
227 UPDATE gt1 SET a=22 WHERE c=4;
228 UPDATE gt1 SET a=23 WHERE d=15;
229 UPDATE gt1 SET a=24 WHERE a=4;
230 DELETE FROM gt1 WHERE a=5;
231 DELETE FROM gt1 WHERE b=24;
232 DELETE FROM gt1 WHERE c=24;
233 DELETE FROM gt1 WHERE d=33;
234
235 --source include/rpl_sync.inc
236 --echo # check that gt1 exists and has same values in both servers
237 --let $diff_tables=server1:gt1, server2:gt1
238 --source include/diff_tables.inc
239
240 --echo # Clean Up
241
242 DROP TABLE st1,st2,st3,st4;
243 DROP TABLE vt1,vt2,vt3;
244 DROP TABLE gt1;
245 DROP TABLE t1,t2;
246
247 --source include/group_replication_end.inc