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