Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_basic_8partition.test

raw
1 ############################################################
2 # Author: MATZ #
3 # Date: 2006-03-22 #
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 ############################################################
9
10 --source include/not_group_replication_plugin.inc
11 --source include/have_binlog_format_row.inc
12 --source include/not_ndb_default.inc
13 --source include/master-slave.inc
14 connection master;
15
16 let $maybe_ro_var = @@BINLOG_FORMAT;
17 let $val4var = ROW;
18 --source include/safe_set_to_maybe_ro_var.inc
19
20 --echo **** Partition RANGE testing ****
21
22 # Create table that is partitioned by range on year i.e. year(t) and
23 # replicate basice operations such at insert, update delete between 2
24 # different storage engines Alter table and ensure table is handled
25 # Correctly on the slave
26 # Note that the storage engine should not be explicit: the default
27 # storage engine is used on master and slave.
28
29 #Get default engine value WL#6921
30 --let $DEFAULT_ENGINE = `select @@global.default_storage_engine`
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,
34 y YEAR, t DATE)
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);
42
43 --echo --- On master ---
44 #Replace default engine value with static engine string WL#6921
45 --replace_result $DEFAULT_ENGINE ENGINE
46 SHOW CREATE TABLE t1;
47
48 --echo --- On slave --
49 --source include/sync_slave_sql_with_master.inc
50 --replace_result $DEFAULT_ENGINE ENGINE
51 SHOW CREATE TABLE t1;
52
53 --source include/rpl_multi_engine3.inc
54
55 connection master;
56 # Check that simple Alter statements are replicated correctly
57 ALTER TABLE t1 MODIFY vc TEXT;
58
59 --echo --- On master ---
60 --replace_result $DEFAULT_ENGINE ENGINE
61 SHOW CREATE TABLE t1;
62
63 --echo --- On slave ---
64 --source include/sync_slave_sql_with_master.inc
65 --replace_result $DEFAULT_ENGINE ENGINE
66 SHOW CREATE TABLE t1;
67
68 # Perform basic operation on master and ensure replicated correctly
69 --source include/rpl_multi_engine3.inc
70
71 connection master;
72 DROP TABLE IF EXISTS t1;
73
74 ########################################################
75
76 --echo **** Partition LIST testing ****
77
78 # Create table that is partitioned by list on id i.e. (2,4). Pretend
79 # that we missed one and alter to add. Then replicate basice
80 # operations such at insert, update delete between 2 different storage
81 # engines Alter table and ensure table is handled Correctly on the
82 # slave.
83
84
85 #Get default engine value WL#6921
86 --let $DEFAULT_ENGINE = `select @@global.default_storage_engine`
87 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
88 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
89 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
90 y YEAR, t DATE)
91 PARTITION BY LIST(id)
92 (PARTITION p0 VALUES IN (2, 4),
93 PARTITION p1 VALUES IN (42, 142),
94 PARTITION p2 VALUES IN (412));
95
96 --echo --- On master ---
97 --replace_result $DEFAULT_ENGINE ENGINE
98 SHOW CREATE TABLE t1;
99
100 --echo --- On slave ---
101 --source include/sync_slave_sql_with_master.inc
102 --replace_result $DEFAULT_ENGINE ENGINE
103 SHOW CREATE TABLE t1;
104
105 # Perform basic operation on master and ensure replicated correctly
106 --source include/rpl_multi_engine3.inc
107
108 connection master;
109 # Check that simple Alter statements are replicated correctly ---
110 ALTER TABLE t1 MODIFY vc TEXT;
111
112 --echo --- On master ---
113 --replace_result $DEFAULT_ENGINE ENGINE
114 SHOW CREATE TABLE t1;
115
116 --echo --- On slave ---
117 --source include/sync_slave_sql_with_master.inc
118 --replace_result $DEFAULT_ENGINE ENGINE
119 SHOW CREATE TABLE t1;
120
121 # Perform basic operation on master and ensure replicated correctly
122 --source include/rpl_multi_engine3.inc
123
124 connection master;
125 DROP TABLE IF EXISTS t1;
126
127 ########################################################
128
129 --echo **** Partition HASH testing ****
130
131 # Create table that is partitioned by hash on year i.e. YEAR(t). Then
132 # replicate basice operations such at insert, update delete between 2
133 # different storage engines Alter table and ensure table is handled
134 # Correctly on the slave
135
136 #Get default engine value WL#6921
137 --let $DEFAULT_ENGINE = `select @@global.default_storage_engine`
138 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
139 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
140 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
141 y YEAR, t DATE)
142 PARTITION BY HASH( YEAR(t) )
143 PARTITIONS 4;
144
145 --echo --- On master ---
146 --replace_result $DEFAULT_ENGINE ENGINE
147 SHOW CREATE TABLE t1;
148
149 --echo --- On slave ---
150 --source include/sync_slave_sql_with_master.inc
151 --replace_result $DEFAULT_ENGINE ENGINE
152 SHOW CREATE TABLE t1;
153
154 --source include/rpl_multi_engine3.inc
155
156 # Check that simple Alter statements are replicated correctly
157 ALTER TABLE t1 MODIFY vc TEXT;
158
159 --echo --- On master ---
160 --replace_result $DEFAULT_ENGINE ENGINE
161 SHOW CREATE TABLE t1;
162
163 --echo --- On slave ---
164 --source include/sync_slave_sql_with_master.inc
165 --replace_result $DEFAULT_ENGINE ENGINE
166 SHOW CREATE TABLE t1;
167
168 --source include/rpl_multi_engine3.inc
169
170 connection master;
171 DROP TABLE IF EXISTS t1;
172
173 ########################################################
174
175 # This part does not work
176 --echo **** Partition by KEY ****
177
178 # Create table that is partitioned by key on id with 4 parts. Then
179 # replicate basice operations such at insert, update delete between 2
180 # different storage engines Alter table and ensure table is handled
181 # Correctly on the slave
182
183 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
184 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
185 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
186 y YEAR, t DATE,PRIMARY KEY(id))
187 PARTITION BY KEY()
188 PARTITIONS 4;
189
190 --echo --- On master ---
191 --replace_result $DEFAULT_ENGINE ENGINE
192 SHOW CREATE TABLE t1;
193
194 --echo --- On slave ---
195 --source include/sync_slave_sql_with_master.inc
196 --replace_result $DEFAULT_ENGINE ENGINE
197 SHOW CREATE TABLE t1;
198
199 --source include/rpl_multi_engine3.inc
200
201 connection master;
202 # Check that simple Alter statements are replicated correctly
203 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
204
205 --echo --- On master ---
206 --replace_result $DEFAULT_ENGINE ENGINE
207 SHOW CREATE TABLE t1;
208
209 --echo --- On slave ---
210 --source include/sync_slave_sql_with_master.inc
211 --replace_result $DEFAULT_ENGINE ENGINE
212 SHOW CREATE TABLE t1;
213
214 --source include/rpl_multi_engine3.inc
215
216 connection master;
217 # Check that simple Alter statements are replicated correctly
218 ALTER TABLE t1 MODIFY vc TEXT;
219
220 --echo --- On master ---
221 --replace_result $DEFAULT_ENGINE ENGINE
222 SHOW CREATE TABLE t1;
223
224 --echo --- On slave ---
225 --source include/sync_slave_sql_with_master.inc
226 --replace_result $DEFAULT_ENGINE ENGINE
227 SHOW CREATE TABLE t1;
228
229 --source include/rpl_multi_engine3.inc
230
231 DROP TABLE IF EXISTS t1;
232
233 # End of 5.1 test case
234 --source include/rpl_end.inc