1############################################################
2# Author: MATZ                                             #
3# Date: 2006-03-22                                         #
4# Purpose: See if replication of partition tables work     #
5############################################################
6
7connection master;
8--disable_warnings
9DROP TABLE IF EXISTS t1;
10--enable_query_log
11
12--echo --- Start test 2 partition RANGE testing --
13
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.
20
21CREATE 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,
24                 y YEAR, t DATE)
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);
32
33--echo --- On master ---
34SHOW CREATE TABLE t1;
35
36--echo --- On slave --
37sync_slave_with_master;
38SHOW CREATE TABLE t1;
39
40--source include/rpl_multi_engine3.inc
41
42--echo --- Check that simple Alter statements are replicated correctly ---
43ALTER TABLE t1 MODIFY vc TEXT;
44
45--echo --- On master ---
46SHOW CREATE TABLE t1;
47
48--echo --- On slave ---
49sync_slave_with_master;
50SHOW CREATE TABLE t1;
51
52--echo --- Perform basic operation on master ---
53--echo --- and ensure replicated correctly ---
54--enable_query_log
55
56--source include/rpl_multi_engine3.inc
57
58--echo --- End test 2 partition RANGE testing ---
59
60DROP TABLE IF EXISTS t1;
61
62########################################################
63
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#################################################
75
76
77CREATE 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,
80                 y YEAR, t DATE)
81                 PARTITION BY LIST(id)
82                (PARTITION p0 VALUES IN (2, 4),
83                 PARTITION p1 VALUES IN (42, 142));
84
85--echo --- Test 3 Alter to add partition ---
86
87ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
88
89--echo --- Show table on master ---
90
91SHOW CREATE TABLE t1;
92
93--echo --- Show table on slave ---
94
95sync_slave_with_master;
96SHOW CREATE TABLE t1;
97
98--echo --- Perform basic operation on master ---
99--echo --- and ensure replicated correctly ---
100
101--source include/rpl_multi_engine3.inc
102
103--echo --- Check that simple Alter statements are replicated correctly ---
104
105ALTER TABLE t1 MODIFY vc TEXT;
106
107--echo --- Show the new improved table on the master ---
108
109SHOW CREATE TABLE t1;
110
111--echo --- Make sure that our tables on slave are still same engine ---
112--echo --- and that the alter statements replicated correctly ---
113
114sync_slave_with_master;
115SHOW CREATE TABLE t1;
116
117--echo --- Perform basic operation on master ---
118--echo --- and ensure replicated correctly ---
119
120--source include/rpl_multi_engine3.inc
121
122--echo --- End test 3 partition LIST testing ---
123--echo --- Do Cleanup --
124
125DROP TABLE IF EXISTS t1;
126
127########################################################
128
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#################################################
139
140
141CREATE 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,
144                 y YEAR, t DATE)
145                 PARTITION BY HASH( YEAR(t) )
146                 PARTITIONS 4;
147
148--echo --- show that tables have been created correctly ---
149
150SHOW CREATE TABLE t1;
151sync_slave_with_master;
152SHOW CREATE TABLE t1;
153
154--echo --- Perform basic operation on master ---
155--echo --- and ensure replicated correctly ---
156
157--source include/rpl_multi_engine3.inc
158
159--echo --- Check that simple Alter statements are replicated correctly ---
160
161ALTER TABLE t1 MODIFY vc TEXT;
162
163--echo --- Show the new improved table on the master ---
164
165SHOW CREATE TABLE t1;
166
167--echo --- Make sure that our tables on slave are still same engine ---
168--echo --- and that the alter statements replicated correctly ---
169
170sync_slave_with_master;
171SHOW CREATE TABLE t1;
172
173--echo --- Perform basic operation on master ---
174--echo --- and ensure replicated correctly ---
175
176--source include/rpl_multi_engine3.inc
177
178--echo --- End test 4 partition HASH testing ---
179--echo --- Do Cleanup --
180
181DROP TABLE IF EXISTS t1;
182
183########################################################
184
185--echo --- Start test 5 partition by key testing ---
186--echo --- Create Table Section ---
187
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#################################################
196
197CREATE 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))
201                 PARTITION BY KEY()
202                 PARTITIONS 4;
203
204--echo --- Show that tables on master are ndbcluster tables ---
205
206SHOW CREATE TABLE t1;
207
208--echo --- Show that tables on slave ---
209
210sync_slave_with_master;
211SHOW CREATE TABLE t1;
212
213--echo --- Perform basic operation on master ---
214--echo --- and ensure replicated correctly ---
215
216--source include/rpl_multi_engine3.inc
217
218# Okay lets see how it holds up to table changes
219--echo --- Check that simple Alter statements are replicated correctly ---
220
221ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
222
223--echo --- Show the new improved table on the master ---
224
225SHOW CREATE TABLE t1;
226
227--echo --- Make sure that our tables on slave are still right type ---
228--echo --- and that the alter statements replicated correctly ---
229
230sync_slave_with_master;
231SHOW CREATE TABLE t1;
232
233--echo --- Perform basic operation on master ---
234--echo --- and ensure replicated correctly ---
235
236--source include/rpl_multi_engine3.inc
237
238--echo --- Check that simple Alter statements are replicated correctly ---
239
240ALTER TABLE t1 MODIFY vc TEXT;
241
242--echo --- Show the new improved table on the master ---
243
244SHOW CREATE TABLE t1;
245
246--echo --- Make sure that our tables on slave are still same engine ---
247--echo --- and that the alter statements replicated correctly ---
248
249sync_slave_with_master;
250SHOW CREATE TABLE t1;
251
252--echo --- Perform basic operation on master ---
253--echo --- and ensure replicated correctly ---
254
255--source include/rpl_multi_engine3.inc
256
257--echo --- End test 5 key partition testing ---
258--echo --- Do Cleanup ---
259
260DROP TABLE IF EXISTS t1;
261
262# End of 5.1 test case
263