1
#######################################
4
# Purpose: To test the replication of #
5
# Cluster Disk Data using partitions #
6
#######################################
8
--source include/have_ndb.inc
9
--source include/have_binlog_format_mixed_or_row.inc
10
--source include/ndb_master-slave.inc
12
--echo --- Doing pre test cleanup ---
16
DROP TABLE IF EXISTS t1;
20
# Start by creating a logfile group
21
##################################
23
CREATE LOGFILE GROUP lg1
24
ADD UNDOFILE 'undofile.dat'
29
ALTER LOGFILE GROUP lg1
30
ADD UNDOFILE 'undofile02.dat'
34
###################################################
35
# Create a tablespace connected to the logfile group
36
###################################################
39
ADD DATAFILE 'datafile.dat'
45
ADD DATAFILE 'datafile02.dat'
49
#################################################################
51
--echo --- Start test 2 partition RANGE testing --
52
--echo --- Do setup --
55
#################################################
56
# Requirment: Create table that is partitioned #
57
# by range on year i.e. year(t) and replicate #
58
# basice operations such at insert, update #
59
# delete between 2 different storage engines #
60
# Alter table and ensure table is handled #
61
# Correctly on the slave #
62
#################################################
64
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
65
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
66
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
68
TABLESPACE ts1 STORAGE DISK
70
PARTITION BY RANGE (YEAR(t))
71
(PARTITION p0 VALUES LESS THAN (1901),
72
PARTITION p1 VALUES LESS THAN (1946),
73
PARTITION p2 VALUES LESS THAN (1966),
74
PARTITION p3 VALUES LESS THAN (1986),
75
PARTITION p4 VALUES LESS THAN (2005),
76
PARTITION p5 VALUES LESS THAN MAXVALUE);
78
--echo --- Show table on master ---
82
--echo --- Show table on slave --
84
sync_slave_with_master;
87
--echo --- Perform basic operation on master ---
88
--echo --- and ensure replicated correctly ---
90
--source include/rpl_multi_engine3.inc
92
--echo --- Check that simple Alter statements are replicated correctly ---
94
ALTER TABLE t1 MODIFY vc VARCHAR(255);
96
--echo --- Show the new improved table on the master ---
100
--echo --- Make sure that our tables on slave are still same engine ---
101
--echo --- and that the alter statements replicated correctly ---
103
sync_slave_with_master;
104
SHOW CREATE TABLE t1;
106
--echo --- Perform basic operation on master ---
107
--echo --- and ensure replicated correctly ---
110
--source include/rpl_multi_engine3.inc
112
--echo --- End test 2 partition RANGE testing ---
113
--echo --- Do Cleanup ---
115
DROP TABLE IF EXISTS t1;
117
########################################################
119
--echo --- Start test 3 partition LIST testing ---
120
--echo --- Do setup ---
121
#################################################
124
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
125
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
126
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
128
TABLESPACE ts1 STORAGE DISK
130
PARTITION BY LIST(id)
131
(PARTITION p0 VALUES IN (2, 4),
132
PARTITION p1 VALUES IN (42, 142));
134
--echo --- Test 3 Alter to add partition ---
136
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
138
--echo --- Show table on master ---
140
SHOW CREATE TABLE t1;
142
--echo --- Show table on slave ---
144
sync_slave_with_master;
145
SHOW CREATE TABLE t1;
147
--echo --- Perform basic operation on master ---
148
--echo --- and ensure replicated correctly ---
150
--source include/rpl_multi_engine3.inc
152
--echo --- Check that simple Alter statements are replicated correctly ---
154
ALTER TABLE t1 MODIFY vc VARCHAR(255);
156
--echo --- Show the new improved table on the master ---
158
SHOW CREATE TABLE t1;
160
--echo --- Make sure that our tables on slave are still same engine ---
161
--echo --- and that the alter statements replicated correctly ---
163
sync_slave_with_master;
164
SHOW CREATE TABLE t1;
166
--echo --- Perform basic operation on master ---
167
--echo --- and ensure replicated correctly ---
169
--source include/rpl_multi_engine3.inc
171
--echo --- End test 3 partition LIST testing ---
172
--echo --- Do Cleanup --
174
DROP TABLE IF EXISTS t1;
176
########################################################
178
--echo --- Start test 4 partition HASH testing ---
179
--echo --- Do setup ---
180
#################################################
183
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
184
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
185
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
187
TABLESPACE ts1 STORAGE DISK
189
PARTITION BY HASH( YEAR(t) )
192
--echo --- show that tables have been created correctly ---
194
SHOW CREATE TABLE t1;
195
sync_slave_with_master;
196
SHOW CREATE TABLE t1;
198
--echo --- Perform basic operation on master ---
199
--echo --- and ensure replicated correctly ---
201
--source include/rpl_multi_engine3.inc
203
--echo --- Check that simple Alter statements are replicated correctly ---
205
ALTER TABLE t1 MODIFY vc VARCHAR(255);
207
--echo --- Show the new improved table on the master ---
209
SHOW CREATE TABLE t1;
211
--echo --- Make sure that our tables on slave are still same engine ---
212
--echo --- and that the alter statements replicated correctly ---
214
sync_slave_with_master;
215
SHOW CREATE TABLE t1;
217
--echo --- Perform basic operation on master ---
218
--echo --- and ensure replicated correctly ---
220
--source include/rpl_multi_engine3.inc
222
--echo --- End test 4 partition HASH testing ---
223
--echo --- Do Cleanup --
225
DROP TABLE IF EXISTS t1;
227
########################################################
229
--echo --- Start test 5 partition by key testing ---
230
--echo --- Create Table Section ---
232
#################################################
234
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
235
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
236
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
237
y YEAR, t DATE,PRIMARY KEY(id))
238
TABLESPACE ts1 STORAGE DISK
243
--echo --- Show that tables on master are ndbcluster tables ---
245
SHOW CREATE TABLE t1;
247
--echo --- Show that tables on slave ---
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
# Okay lets see how it holds up to table changes
258
--echo --- Check that simple Alter statements are replicated correctly ---
260
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
262
--echo --- Show the new improved table on the master ---
264
SHOW CREATE TABLE t1;
266
--echo --- Make sure that our tables on slave are still right type ---
267
--echo --- and that the alter statements replicated correctly ---
269
sync_slave_with_master;
270
SHOW CREATE TABLE t1;
272
--echo --- Perform basic operation on master ---
273
--echo --- and ensure replicated correctly ---
275
--source include/rpl_multi_engine3.inc
277
--echo --- Check that simple Alter statements are replicated correctly ---
279
ALTER TABLE t1 MODIFY vc VARCHAR(255);
281
--echo --- Show the new improved table on the master ---
283
SHOW CREATE TABLE t1;
285
--echo --- Make sure that our tables on slave are still same engine ---
286
--echo --- and that the alter statements replicated correctly ---
288
sync_slave_with_master;
289
SHOW CREATE TABLE t1;
291
--echo --- Perform basic operation on master ---
292
--echo --- and ensure replicated correctly ---
294
--source include/rpl_multi_engine3.inc
296
--echo --- End test 5 key partition testing ---
297
--echo --- Do Cleanup ---
299
DROP TABLE IF EXISTS t1;
301
drop datafile 'datafile.dat'
304
drop datafile 'datafile02.dat'
306
DROP TABLESPACE ts1 ENGINE=NDB;
307
DROP LOGFILE GROUP lg1 ENGINE=NDB;
308
--sync_slave_with_master
310
# End of 5.1 test case