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
|
--source include/galera_cluster.inc
--source include/have_innodb.inc
--source include/log_bin.inc
--source include/have_sequence.inc
--source include/have_aria.inc
--echo # Case 1 CREATE SEQUENCE with no NOCACHE
--error ER_NOT_SUPPORTED_YET
CREATE SEQUENCE s ENGINE=InnoDB;
CREATE SEQUENCE s NOCACHE ENGINE=InnoDB;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
START TRANSACTION;
REPLACE INTO s VALUES (1,1,9223372036854775806,1,1,1000,0,0);
OPTIMIZE TABLE t1;
SELECT * FROM t1;
SELECT * FROM s;
--connection node_2
SET GLOBAL WSREP_MODE='REPLICATE_ARIA,REPLICATE_MYISAM';
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
--source include/wait_condition.inc
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 's'
--source include/wait_condition.inc
--let $wait_condition = SELECT COUNT(*) = 1 FROM test.s;
--source include/wait_condition.inc
SELECT * FROM t1;
SELECT * FROM s;
--connection node_1
SET GLOBAL WSREP_MODE='REPLICATE_ARIA,REPLICATE_MYISAM';
DROP TABLE t1;
DROP SEQUENCE s;
--echo # Case 2 REPLACE INTO ... SELECT with error
CREATE TABLE t (id INT KEY,a YEAR,INDEX (id,a)) engine=innodb;
--error ER_WRONG_VALUE_COUNT_ON_ROW
REPLACE INTO t (id,a)SELECT /*!99997 */ 1;
REPLACE INTO t (id,a)SELECT /*!99997 */ 1,2;
SELECT * FROM t;
CREATE TABLE t2 (id INT KEY,a YEAR,INDEX (id,a)) engine=myisam;
--error ER_WRONG_VALUE_COUNT_ON_ROW
REPLACE INTO t2 (id,a)SELECT /*!99997 */ 1;
REPLACE INTO t2 (id,a)SELECT /*!99997 */ 1,2;
SELECT * FROM t2;
CREATE TABLE t3 (id INT KEY,a YEAR,INDEX (id,a)) engine=aria;
--error ER_WRONG_VALUE_COUNT_ON_ROW
REPLACE INTO t3 (id,a)SELECT /*!99997 */ 1;
REPLACE INTO t3 (id,a)SELECT /*!99997 */ 1,2;
SELECT * FROM t3;
--connection node_2
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't3'
--source include/wait_condition.inc
--let $wait_condition = SELECT COUNT(*) = 1 FROM test.t3;
--source include/wait_condition.inc
SELECT * FROM t;
SELECT * FROM t2;
SELECT * FROM t3;
--connection node_1
DROP TABLE t,t2,t3;
--echo # Bigger REPLACE ... AS SELECT test
SET GLOBAL wsrep_mode=REPLICATE_MYISAM;
CREATE TABLE t1(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t2(id int not null primary key ,b int) ENGINE=MyISAM;
CREATE TABLE t3(id int not null primary key ,b int) ENGINE=Aria;
CREATE TABLE t4(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t5(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t6(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t7(id int not null primary key ,b int) ENGINE=MyISAM;
CREATE TABLE t8(id int not null primary key ,b int) ENGINE=Aria;
INSERT INTO t1(id) SELECT seq FROM seq_1_to_1000;
INSERT INTO t2(id) SELECT seq FROM seq_1_to_1000;
INSERT INTO t3(id) SELECT seq FROM seq_1_to_1000;
REPLACE INTO t4 SELECT * FROM t1;
REPLACE INTO t5 SELECT * FROM t2;
REPLACE INTO t6 SELECT * FROM t3;
REPLACE INTO t7 SELECT * FROM t2;
REPLACE INTO t8 SELECT * FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t1;
SELECT COUNT(*) AS EXPECT_1000 FROM t2;
SELECT COUNT(*) AS EXPECT_1000 FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t4;
SELECT COUNT(*) AS EXPECT_1000 FROM t5;
SELECT COUNT(*) AS EXPECT_1000 FROM t6;
SELECT COUNT(*) AS EXPECT_1000 FROM t7;
SELECT COUNT(*) AS EXPECT_1000 FROM t8;
--connection node_2
--let $wait_condition = SELECT COUNT(*) = 8 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_'
--source include/wait_condition.inc
--let $wait_condition = SELECT COUNT(*) = 1000 FROM test.t7;
--source include/wait_condition.inc
SELECT COUNT(*) AS EXPECT_1000 FROM t1;
SELECT COUNT(*) AS EXPECT_1000 FROM t2;
SELECT COUNT(*) AS EXPECT_1000 FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t4;
SELECT COUNT(*) AS EXPECT_1000 FROM t5;
SELECT COUNT(*) AS EXPECT_1000 FROM t6;
SELECT COUNT(*) AS EXPECT_1000 FROM t7;
SELECT COUNT(*) AS EXPECT_0 FROM t8;
--connection node_1
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8;
--echo # Bigger INSERT INTO ... SELECT test
CREATE TABLE t1(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t2(id int not null primary key ,b int) ENGINE=MyISAM;
CREATE TABLE t3(id int not null primary key ,b int) ENGINE=Aria;
CREATE TABLE t4(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t5(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t6(id int not null primary key ,b int) ENGINE=InnoDB;
CREATE TABLE t7(id int not null primary key ,b int) ENGINE=MyISAM;
CREATE TABLE t8(id int not null primary key ,b int) ENGINE=Aria;
INSERT INTO t1(id) SELECT seq FROM seq_1_to_1000;
INSERT INTO t2(id) SELECT seq FROM seq_1_to_1000;
INSERT INTO t3(id) SELECT seq FROM seq_1_to_1000;
INSERT INTO t4 SELECT * FROM t1;
INSERT INTO t5 SELECT * FROM t2;
INSERT INTO t6 SELECT * FROM t3;
INSERT INTO t7 SELECT * FROM t2;
INSERT INTO t8 SELECT * FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t1;
SELECT COUNT(*) AS EXPECT_1000 FROM t2;
SELECT COUNT(*) AS EXPECT_1000 FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t4;
SELECT COUNT(*) AS EXPECT_1000 FROM t5;
SELECT COUNT(*) AS EXPECT_1000 FROM t6;
SELECT COUNT(*) AS EXPECT_1000 FROM t7;
SELECT COUNT(*) AS EXPECT_1000 FROM t8;
--connection node_2
--let $wait_condition = SELECT COUNT(*) = 8 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_'
--source include/wait_condition.inc
--let $wait_condition = SELECT COUNT(*) = 1000 FROM test.t7;
--source include/wait_condition.inc
SELECT COUNT(*) AS EXPECT_1000 FROM t1;
SELECT COUNT(*) AS EXPECT_1000 FROM t2;
SELECT COUNT(*) AS EXPECT_1000 FROM t3;
SELECT COUNT(*) AS EXPECT_1000 FROM t4;
SELECT COUNT(*) AS EXPECT_1000 FROM t5;
SELECT COUNT(*) AS EXPECT_1000 FROM t6;
SELECT COUNT(*) AS EXPECT_1000 FROM t7;
SELECT COUNT(*) AS EXPECT_0 FROM t8;
--connection node_1
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8;
#
# View
#
CREATE TABLE t1(pk int not null primary key) engine=innodb;
INSERT INTO t1 values (1),(2),(3),(4);
CREATE VIEW view_t1 AS SELECT * FROM t1;
INSERT INTO view_t1 VALUES (5);
SELECT * FROM t1;
DROP TABLE t1;
DROP VIEW view_t1;
CREATE TABLE t1(pk int not null primary key) engine=myisam;
INSERT INTO t1 values (1),(2),(3),(4);
CREATE VIEW view_t1 AS SELECT * FROM t1;
INSERT INTO view_t1 VALUES (5);
SELECT * FROM t1;
DROP TABLE t1;
DROP VIEW view_t1;
CREATE TABLE t1(pk int not null primary key) engine=aria;
INSERT INTO t1 values (1),(2),(3),(4);
CREATE VIEW view_t1 AS SELECT * FROM t1;
INSERT INTO view_t1 VALUES (5);
SELECT * FROM t1;
DROP TABLE t1;
DROP VIEW view_t1;
SET GLOBAL wsrep_mode=DEFAULT;
--connection node_2
SET GLOBAL wsrep_mode=DEFAULT;
|
