Debian Codegraph @

mysql-test/suite/galera/t/galera_var_replicate_aria_on.test

raw
1 #
2 # Simple test for wsrep-mode = REPLICATE_ARIA
3 #
4
5 --source include/galera_cluster.inc
6 --source include/have_innodb.inc
7 --source include/have_aria.inc
8
9 --connection node_1
10 SET GLOBAL wsrep_mode = REPLICATE_ARIA;
11 --connection node_2
12 SET GLOBAL wsrep_mode = REPLICATE_ARIA;
13
14 #
15 # Simple INSERT
16 #
17
18 --connection node_1
19 CREATE TABLE t1 (f1 INT PRIMARY KEY) Engine=Aria;
20 INSERT INTO t1 VALUES (1);
21 INSERT INTO t1 VALUES (2), (3);
22 INSERT INTO t1 SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL;
23
24 --connection node_2
25 SELECT COUNT(*) AS EXPECT_5 FROM t1;
26
27 --connection node_1
28 DROP TABLE t1;
29
30 #
31 # REPLACE
32 #
33
34 --connection node_1
35 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 VARCHAR(100)) ENGINE=Aria;
36 INSERT INTO t1 VALUES (1, 'abc'),(2,'abc'), (3, 'xxx');
37 REPLACE INTO t1 VALUES (1, 'klm'), (2,'xyz');
38 REPLACE INTO t1 SELECT 3, 'yyy' FROM DUAL;
39 SELECT * FROM t1;
40
41 --connection node_2
42 SELECT COUNT(*) AS EXPECT_3 FROM t1;
43 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 1 AND f2 = 'klm';
44 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 2 AND f2 = 'xyz';
45 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 3 AND f2 = 'yyy';
46 SELECT * FROM t1;
47
48 #
49 # UPDATE
50 #
51
52 --connection node_1
53 UPDATE t1 SET f2 = 'zzz' WHERE f2 = 'yyy';
54 SELECT * FROM t1;
55
56 --connection node_2
57 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f2 = 'zzz';
58 SELECT * FROM t1;
59
60 #
61 # DELETE
62 #
63
64 --connection node_1
65 DELETE FROM t1 WHERE f2 = 'zzz';
66 SELECT * FROM t1;
67
68 --connection node_2
69 SELECT COUNT(*) AS EXPECT_0 FROM t1 WHERE f2 = 'zzz';
70 SELECT * FROM t1;
71
72 #
73 # TRUNCATE
74 #
75
76 --connection node_1
77 TRUNCATE TABLE t1;
78 SELECT * FROM t1;
79
80 --connection node_2
81 SELECT COUNT(*) AS EXPECT_0 FROM t1;
82 SELECT * FROM t1;
83
84 --connection node_1
85 DROP TABLE t1;
86
87 #
88 # Preparation for next tests
89 #
90
91 --connection node_1
92 SET GLOBAL wsrep_sync_wait=15;
93 CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=Aria;
94 CREATE TABLE t2 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
95 INSERT INTO t1 VALUES (1);
96 INSERT INTO t2 VALUES (1);
97
98 --connection node_2
99 SET GLOBAL wsrep_sync_wait=15;
100 SELECT COUNT(*) AS EXPECT_1 FROM t1;
101 SELECT COUNT(*) AS EXPECT_1 FROM t2;
102
103 #
104 # Transaction rollback
105 #
106
107 --connection node_1
108 SET AUTOCOMMIT=OFF;
109 START TRANSACTION;
110 INSERT INTO t1 VALUES (2);
111 INSERT INTO t2 VALUES (2);
112 ROLLBACK;
113
114 --connection node_2
115 SELECT COUNT(*) AS EXPECT_2 FROM t1;
116 SELECT COUNT(*) AS EXPECT_1 FROM t2;
117
118 --connection node_1
119 DROP TABLE t1,t2;
120
121 #
122 # Transaction conflict
123 #
124
125 --connection node_1
126 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=Aria;
127 CREATE TABLE t2 (f2 INTEGER PRIMARY KEY) ENGINE=InnoDB;
128
129 START TRANSACTION;
130 INSERT INTO t1 VALUES (1);
131 INSERT INTO t2 VALUES (1);
132
133 --connection node_2
134 # The Aria update is replicated immediately, so a duplicate key error happens even before the COMMIT
135 --error ER_DUP_ENTRY
136 INSERT INTO t1 VALUES (1);
137
138 --connection node_1
139
140 --error ER_ERROR_DURING_COMMIT
141 COMMIT;
142
143 DROP TABLE t1,t2;
144
145 --connection node_1
146 CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
147 INSERT INTO t1 VALUES(1);
148 # This command should not get replicated.
149 SELECT * FROM t1;
150 DROP TABLE t1;
151
152 #
153 # Test prepared staments
154 #
155 --connection node_1
156 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=Aria;
157 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
158
159 PREPARE upd from 'update t1 set b = 100 where id = 5';
160 PREPARE ins from 'insert into t1 values (11,11)';
161 PREPARE del from 'delete from t1 where id = 4';
162 PREPARE rep from 'replace into t1 values (12,12),(6,600)';
163
164 EXECUTE upd;
165 EXECUTE ins;
166 EXECUTE del;
167 EXECUTE rep;
168
169 SELECT * FROM t1 ORDER BY id;
170
171 --connection node_2
172 SELECT * FROM t1 ORDER BY id;
173
174 --connection node_1
175 DROP TABLE t1;
176
177 #
178 # Test procedure
179 #
180 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=Aria;
181 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
182
183 DELIMITER |;
184 CREATE PROCEDURE proc()
185 BEGIN
186 UPDATE t1 set b = 100 WHERE id = 5;
187 INSERT INTO t1 VALUES (11,11);
188 DELETE FROM t1 WHERE id = 4;
189 REPLACE INTO t1 VALUES (12,12),(6,600);
190 COMMIT;
191 END|
192 DELIMITER ;|
193
194 CALL proc();
195 SELECT * FROM t1 ORDER BY id;
196
197 --connection node_2
198 SELECT * FROM t1 ORDER BY id;
199
200 --connection node_1
201 DROP PROCEDURE proc;
202 DROP TABLE t1;
203
204 #
205 # Test trigger
206 #
207 --connection node_1
208 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=Aria;
209 CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT) ENGINE=Aria;
210 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
211 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, NEW.b);
212 CREATE TRIGGER tr2 BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b),(NULL, NEW.b);
213 CREATE TRIGGER tr3 BEFORE DELETE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b);
214
215 INSERT INTO t1 VALUES (11,11);
216 UPDATE t1 SET b = 200 WHERE id = 2;
217 DELETE FROM t1 where id = 5;
218 SELECT * FROM t1 ORDER BY id;
219 SELECT * FROM t2 ORDER BY id;
220
221 --connection node_2
222 SELECT * FROM t1 ORDER BY id;
223 SELECT * FROM t2 ORDER BY id;
224
225 --connection node_1
226 DROP TRIGGER tr1;
227 DROP TRIGGER tr2;
228 DROP TRIGGER tr3;
229 DROP TABLE t1,t2;
230
231 --disable_query_log
232 --connection node_1
233 SET GLOBAL wsrep_mode = DEFAULT;
234 --connection node_2
235 SET GLOBAL wsrep_mode = DEFAULT;
236 --enable_query_log