1
############################################################
4
# Purpose: See if replication of partition tables work #
5
############################################################
9
DROP TABLE IF EXISTS t1;
12
--echo --- Start test 2 partition RANGE testing --
14
# Create table that is partitioned by range on year i.e. year(t) and
15
# replicate basice operations such at insert, update delete between 2
16
# different storage engines Alter table and ensure table is handled
17
# Correctly on the slave
18
# Note that the storage engine should not be explicit: the default
19
# storage engine is used on master and slave.
21
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
22
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
23
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
25
PARTITION BY RANGE (YEAR(t))
26
(PARTITION p0 VALUES LESS THAN (1901),
27
PARTITION p1 VALUES LESS THAN (1946),
28
PARTITION p2 VALUES LESS THAN (1966),
29
PARTITION p3 VALUES LESS THAN (1986),
30
PARTITION p4 VALUES LESS THAN (2005),
31
PARTITION p5 VALUES LESS THAN MAXVALUE);
33
--echo --- On master ---
36
--echo --- On slave --
37
sync_slave_with_master;
40
--source include/rpl_multi_engine3.inc
42
--echo --- Check that simple Alter statements are replicated correctly ---
43
ALTER TABLE t1 MODIFY vc TEXT;
45
--echo --- On master ---
48
--echo --- On slave ---
49
sync_slave_with_master;
52
--echo --- Perform basic operation on master ---
53
--echo --- and ensure replicated correctly ---
56
--source include/rpl_multi_engine3.inc
58
--echo --- End test 2 partition RANGE testing ---
60
DROP TABLE IF EXISTS t1;
62
########################################################
64
--echo --- Start test 3 partition LIST testing ---
65
--echo --- Do setup ---
66
#################################################
67
# Requirment: Create table that is partitioned #
68
# by list on id i.e. (2,4). Pretend that we #
69
# missed one and alter to add. Then replicate #
70
# basice operations such at insert, update #
71
# delete between 2 different storage engines #
72
# Alter table and ensure table is handled #
73
# Correctly on the slave #
74
#################################################
77
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
78
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
79
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
82
(PARTITION p0 VALUES IN (2, 4),
83
PARTITION p1 VALUES IN (42, 142));
85
--echo --- Test 3 Alter to add partition ---
87
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
89
--echo --- Show table on master ---
93
--echo --- Show table on slave ---
95
sync_slave_with_master;
98
--echo --- Perform basic operation on master ---
99
--echo --- and ensure replicated correctly ---
101
--source include/rpl_multi_engine3.inc
103
--echo --- Check that simple Alter statements are replicated correctly ---
105
ALTER TABLE t1 MODIFY vc TEXT;
107
--echo --- Show the new improved table on the master ---
109
SHOW CREATE TABLE t1;
111
--echo --- Make sure that our tables on slave are still same engine ---
112
--echo --- and that the alter statements replicated correctly ---
114
sync_slave_with_master;
115
SHOW CREATE TABLE t1;
117
--echo --- Perform basic operation on master ---
118
--echo --- and ensure replicated correctly ---
120
--source include/rpl_multi_engine3.inc
122
--echo --- End test 3 partition LIST testing ---
123
--echo --- Do Cleanup --
125
DROP TABLE IF EXISTS t1;
127
########################################################
129
--echo --- Start test 4 partition HASH testing ---
130
--echo --- Do setup ---
131
#################################################
132
# Requirment: Create table that is partitioned #
133
# by hash on year i.e. YEAR(t). Then replicate #
134
# basice operations such at insert, update #
135
# delete between 2 different storage engines #
136
# Alter table and ensure table is handled #
137
# Correctly on the slave #
138
#################################################
141
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
142
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
143
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
145
PARTITION BY HASH( YEAR(t) )
148
--echo --- show that tables have been created correctly ---
150
SHOW CREATE TABLE t1;
151
sync_slave_with_master;
152
SHOW CREATE TABLE t1;
154
--echo --- Perform basic operation on master ---
155
--echo --- and ensure replicated correctly ---
157
--source include/rpl_multi_engine3.inc
159
--echo --- Check that simple Alter statements are replicated correctly ---
161
ALTER TABLE t1 MODIFY vc TEXT;
163
--echo --- Show the new improved table on the master ---
165
SHOW CREATE TABLE t1;
167
--echo --- Make sure that our tables on slave are still same engine ---
168
--echo --- and that the alter statements replicated correctly ---
170
sync_slave_with_master;
171
SHOW CREATE TABLE t1;
173
--echo --- Perform basic operation on master ---
174
--echo --- and ensure replicated correctly ---
176
--source include/rpl_multi_engine3.inc
178
--echo --- End test 4 partition HASH testing ---
179
--echo --- Do Cleanup --
181
DROP TABLE IF EXISTS t1;
183
########################################################
185
--echo --- Start test 5 partition by key testing ---
186
--echo --- Create Table Section ---
188
#################################################
189
# Requirment: Create table that is partitioned #
190
# by key on id with 4 parts. Then replicate #
191
# basice operations such at insert, update #
192
# delete between 2 different storage engines #
193
# Alter table and ensure table is handled #
194
# Correctly on the slave #
195
#################################################
197
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
198
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
199
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
200
y YEAR, t DATE,PRIMARY KEY(id))
204
--echo --- Show that tables on master are ndbcluster tables ---
206
SHOW CREATE TABLE t1;
208
--echo --- Show that tables on slave ---
210
sync_slave_with_master;
211
SHOW CREATE TABLE t1;
213
--echo --- Perform basic operation on master ---
214
--echo --- and ensure replicated correctly ---
216
--source include/rpl_multi_engine3.inc
218
# Okay lets see how it holds up to table changes
219
--echo --- Check that simple Alter statements are replicated correctly ---
221
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
223
--echo --- Show the new improved table on the master ---
225
SHOW CREATE TABLE t1;
227
--echo --- Make sure that our tables on slave are still right type ---
228
--echo --- and that the alter statements replicated correctly ---
230
sync_slave_with_master;
231
SHOW CREATE TABLE t1;
233
--echo --- Perform basic operation on master ---
234
--echo --- and ensure replicated correctly ---
236
--source include/rpl_multi_engine3.inc
238
--echo --- Check that simple Alter statements are replicated correctly ---
240
ALTER TABLE t1 MODIFY vc TEXT;
242
--echo --- Show the new improved table on the master ---
244
SHOW CREATE TABLE t1;
246
--echo --- Make sure that our tables on slave are still same engine ---
247
--echo --- and that the alter statements replicated correctly ---
249
sync_slave_with_master;
250
SHOW CREATE TABLE t1;
252
--echo --- Perform basic operation on master ---
253
--echo --- and ensure replicated correctly ---
255
--source include/rpl_multi_engine3.inc
257
--echo --- End test 5 key partition testing ---
258
--echo --- Do Cleanup ---
260
DROP TABLE IF EXISTS t1;
262
# End of 5.1 test case
added
removed
mysql-test/extra/rpl_tests/rpl_partition.test
