1
############################################################
4
# Purpose: See if replication of partition tables work #
5
# Most of this test is copied from the rpl_xxx2yyy tests, #
6
# but here we just test some simple basic replication of #
7
# partition tables with same engine (MyISAM) in both ends. #
8
############################################################
10
--source include/have_binlog_format_row.inc
11
--source include/have_partition.inc
12
--source include/not_ndb_default.inc
13
--source include/master-slave.inc
16
DROP TABLE IF EXISTS t1;
18
let $maybe_ro_var = @@BINLOG_FORMAT;
20
--source include/safe_set_to_maybe_ro_var.inc
22
--echo **** Partition RANGE testing ****
24
# Create table that is partitioned by range on year i.e. year(t) and
25
# replicate basice operations such at insert, update delete between 2
26
# different storage engines Alter table and ensure table is handled
27
# Correctly on the slave
28
# Note that the storage engine should not be explicit: the default
29
# storage engine is used on master and slave.
31
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
32
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
33
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
35
PARTITION BY RANGE (YEAR(t))
36
(PARTITION p0 VALUES LESS THAN (1901),
37
PARTITION p1 VALUES LESS THAN (1946),
38
PARTITION p2 VALUES LESS THAN (1966),
39
PARTITION p3 VALUES LESS THAN (1986),
40
PARTITION p4 VALUES LESS THAN (2005),
41
PARTITION p5 VALUES LESS THAN MAXVALUE);
43
--echo --- On master ---
46
--echo --- On slave --
47
sync_slave_with_master;
50
--source include/rpl_multi_engine3.inc
53
# Check that simple Alter statements are replicated correctly
54
ALTER TABLE t1 MODIFY vc TEXT;
56
--echo --- On master ---
59
--echo --- On slave ---
60
sync_slave_with_master;
63
# Perform basic operation on master and ensure replicated correctly
64
--source include/rpl_multi_engine3.inc
67
DROP TABLE IF EXISTS t1;
69
########################################################
71
--echo **** Partition LIST testing ****
73
# Create table that is partitioned by list on id i.e. (2,4). Pretend
74
# that we missed one and alter to add. Then replicate basice
75
# operations such at insert, update delete between 2 different storage
76
# engines Alter table and ensure table is handled Correctly on the
80
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
81
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
82
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
85
(PARTITION p0 VALUES IN (2, 4),
86
PARTITION p1 VALUES IN (42, 142),
87
PARTITION p2 VALUES IN (412));
89
--echo --- On master ---
92
--echo --- On slave ---
93
sync_slave_with_master;
96
# Perform basic operation on master and ensure replicated correctly
97
--source include/rpl_multi_engine3.inc
100
# Check that simple Alter statements are replicated correctly ---
101
ALTER TABLE t1 MODIFY vc TEXT;
103
--echo --- On master ---
104
SHOW CREATE TABLE t1;
106
--echo --- On slave ---
107
sync_slave_with_master;
108
SHOW CREATE TABLE t1;
110
# Perform basic operation on master and ensure replicated correctly
111
--source include/rpl_multi_engine3.inc
114
DROP TABLE IF EXISTS t1;
116
########################################################
118
--echo **** Partition HASH testing ****
120
# Create table that is partitioned by hash on year i.e. YEAR(t). Then
121
# replicate basice operations such at insert, update delete between 2
122
# different storage engines Alter table and ensure table is handled
123
# Correctly on the slave
125
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
126
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
127
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
129
PARTITION BY HASH( YEAR(t) )
132
--echo --- On master ---
133
SHOW CREATE TABLE t1;
135
--echo --- On slave ---
136
sync_slave_with_master;
137
SHOW CREATE TABLE t1;
139
--source include/rpl_multi_engine3.inc
141
# Check that simple Alter statements are replicated correctly
142
ALTER TABLE t1 MODIFY vc TEXT;
144
--echo --- On master ---
145
SHOW CREATE TABLE t1;
147
--echo --- On slave ---
148
sync_slave_with_master;
149
SHOW CREATE TABLE t1;
151
--source include/rpl_multi_engine3.inc
154
DROP TABLE IF EXISTS t1;
156
########################################################
158
# This part does not work
159
--echo **** Partition by KEY ****
161
# Create table that is partitioned by key on id with 4 parts. Then
162
# replicate basice operations such at insert, update delete between 2
163
# different storage engines Alter table and ensure table is handled
164
# Correctly on the slave
166
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
167
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
168
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
169
y YEAR, t DATE,PRIMARY KEY(id))
173
--echo --- On master ---
174
SHOW CREATE TABLE t1;
176
--echo --- On slave ---
177
sync_slave_with_master;
178
SHOW CREATE TABLE t1;
180
--source include/rpl_multi_engine3.inc
183
# Check that simple Alter statements are replicated correctly
184
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
186
--echo --- On master ---
187
SHOW CREATE TABLE t1;
189
--echo --- On slave ---
190
sync_slave_with_master;
191
SHOW CREATE TABLE t1;
193
--source include/rpl_multi_engine3.inc
196
# Check that simple Alter statements are replicated correctly
197
ALTER TABLE t1 MODIFY vc TEXT;
199
--echo --- On master ---
200
SHOW CREATE TABLE t1;
202
--echo --- On slave ---
203
sync_slave_with_master;
204
SHOW CREATE TABLE t1;
206
--source include/rpl_multi_engine3.inc
208
DROP TABLE IF EXISTS t1;
210
# End of 5.1 test case