[tomato.git] / release / src / router / mysql / mysql-test / suite / rpl / r / rpl_row_basic_8partition.result
| blob | c7241c52353a671a53bdc7d908a0ae8a7ff79574 |
1 include/master-slave.inc
2 [connection master]
3 DROP TABLE IF EXISTS t1;
4 SET @@BINLOG_FORMAT = ROW;
5 SELECT @@BINLOG_FORMAT;
6 @@BINLOG_FORMAT
7 ROW
8 **** Partition RANGE testing ****
9 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
10 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
11 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
12 y YEAR, t DATE)
13 PARTITION BY RANGE (YEAR(t))
14 (PARTITION p0 VALUES LESS THAN (1901),
15 PARTITION p1 VALUES LESS THAN (1946),
16 PARTITION p2 VALUES LESS THAN (1966),
17 PARTITION p3 VALUES LESS THAN (1986),
18 PARTITION p4 VALUES LESS THAN (2005),
19 PARTITION p5 VALUES LESS THAN MAXVALUE);
20 --- On master ---
21 SHOW CREATE TABLE t1;
22 Table Create Table
23 t1 CREATE TABLE `t1` (
24 `id` mediumint(9) NOT NULL,
25 `b1` bit(8) DEFAULT NULL,
26 `vc` varchar(255) DEFAULT NULL,
27 `bc` char(255) DEFAULT NULL,
28 `d` decimal(10,4) DEFAULT '0.0000',
29 `f` float DEFAULT '0',
30 `total` bigint(20) unsigned DEFAULT NULL,
31 `y` year(4) DEFAULT NULL,
32 `t` date DEFAULT NULL
33 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
34 /*!50100 PARTITION BY RANGE (YEAR(t))
35 (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
36 PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
37 PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
38 PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
39 PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
40 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
41 --- On slave --
42 SHOW CREATE TABLE t1;
43 Table Create Table
44 t1 CREATE TABLE `t1` (
45 `id` mediumint(9) NOT NULL,
46 `b1` bit(8) DEFAULT NULL,
47 `vc` varchar(255) DEFAULT NULL,
48 `bc` char(255) DEFAULT NULL,
49 `d` decimal(10,4) DEFAULT '0.0000',
50 `f` float DEFAULT '0',
51 `total` bigint(20) unsigned DEFAULT NULL,
52 `y` year(4) DEFAULT NULL,
53 `t` date DEFAULT NULL
54 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
55 /*!50100 PARTITION BY RANGE (YEAR(t))
56 (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
57 PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
58 PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
59 PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
60 PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
61 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
62 "--- Insert into t1 --" as "";
63 --- Select from t1 on master ---
64 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
65 id hex(b1) vc bc d f total y t
66 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
67 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
68 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
69 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
70 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
71 --- Select from t1 on slave ---
72 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
73 id hex(b1) vc bc d f total y t
74 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
75 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
76 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
77 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
78 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
79 --- Update t1 on master --
80 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
81 --- Check the update on master ---
82 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
83 id hex(b1) vc bc d f total y t
84 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
85 --- Check Update on slave ---
86 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
87 id hex(b1) vc bc d f total y t
88 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
89 --- Remove a record from t1 on master ---
90 DELETE FROM t1 WHERE id = 42;
91 --- Show current count on master for t1 ---
92 SELECT COUNT(*) FROM t1;
93 COUNT(*)
94 4
95 --- Show current count on slave for t1 ---
96 SELECT COUNT(*) FROM t1;
97 COUNT(*)
98 4
99 DELETE FROM t1;
100 ALTER TABLE t1 MODIFY vc TEXT;
101 --- On master ---
102 SHOW CREATE TABLE t1;
103 Table Create Table
104 t1 CREATE TABLE `t1` (
105 `id` mediumint(9) NOT NULL,
106 `b1` bit(8) DEFAULT NULL,
107 `vc` text,
108 `bc` char(255) DEFAULT NULL,
109 `d` decimal(10,4) DEFAULT '0.0000',
110 `f` float DEFAULT '0',
111 `total` bigint(20) unsigned DEFAULT NULL,
112 `y` year(4) DEFAULT NULL,
113 `t` date DEFAULT NULL
114 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
115 /*!50100 PARTITION BY RANGE (YEAR(t))
116 (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
117 PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
118 PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
119 PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
120 PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
121 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
122 --- On slave ---
123 SHOW CREATE TABLE t1;
124 Table Create Table
125 t1 CREATE TABLE `t1` (
126 `id` mediumint(9) NOT NULL,
127 `b1` bit(8) DEFAULT NULL,
128 `vc` text,
129 `bc` char(255) DEFAULT NULL,
130 `d` decimal(10,4) DEFAULT '0.0000',
131 `f` float DEFAULT '0',
132 `total` bigint(20) unsigned DEFAULT NULL,
133 `y` year(4) DEFAULT NULL,
134 `t` date DEFAULT NULL
135 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
136 /*!50100 PARTITION BY RANGE (YEAR(t))
137 (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
138 PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
139 PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
140 PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
141 PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
142 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
143 "--- Insert into t1 --" as "";
144 --- Select from t1 on master ---
145 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
146 id hex(b1) vc bc d f total y t
147 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
148 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
149 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
150 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
151 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
152 --- Select from t1 on slave ---
153 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
154 id hex(b1) vc bc d f total y t
155 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
156 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
157 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
158 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
159 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
160 --- Update t1 on master --
161 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
162 --- Check the update on master ---
163 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
164 id hex(b1) vc bc d f total y t
165 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
166 --- Check Update on slave ---
167 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
168 id hex(b1) vc bc d f total y t
169 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
170 --- Remove a record from t1 on master ---
171 DELETE FROM t1 WHERE id = 42;
172 --- Show current count on master for t1 ---
173 SELECT COUNT(*) FROM t1;
174 COUNT(*)
175 4
176 --- Show current count on slave for t1 ---
177 SELECT COUNT(*) FROM t1;
178 COUNT(*)
179 4
180 DELETE FROM t1;
181 DROP TABLE IF EXISTS t1;
182 **** Partition LIST testing ****
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)
187 PARTITION BY LIST(id)
188 (PARTITION p0 VALUES IN (2, 4),
189 PARTITION p1 VALUES IN (42, 142),
190 PARTITION p2 VALUES IN (412));
191 --- On master ---
192 SHOW CREATE TABLE t1;
193 Table Create Table
194 t1 CREATE TABLE `t1` (
195 `id` mediumint(9) NOT NULL,
196 `b1` bit(8) DEFAULT NULL,
197 `vc` varchar(255) DEFAULT NULL,
198 `bc` char(255) DEFAULT NULL,
199 `d` decimal(10,4) DEFAULT '0.0000',
200 `f` float DEFAULT '0',
201 `total` bigint(20) unsigned DEFAULT NULL,
202 `y` year(4) DEFAULT NULL,
203 `t` date DEFAULT NULL
204 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
205 /*!50100 PARTITION BY LIST (id)
206 (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
207 PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
208 PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
209 --- On slave ---
210 SHOW CREATE TABLE t1;
211 Table Create Table
212 t1 CREATE TABLE `t1` (
213 `id` mediumint(9) NOT NULL,
214 `b1` bit(8) DEFAULT NULL,
215 `vc` varchar(255) DEFAULT NULL,
216 `bc` char(255) DEFAULT NULL,
217 `d` decimal(10,4) DEFAULT '0.0000',
218 `f` float DEFAULT '0',
219 `total` bigint(20) unsigned DEFAULT NULL,
220 `y` year(4) DEFAULT NULL,
221 `t` date DEFAULT NULL
222 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
223 /*!50100 PARTITION BY LIST (id)
224 (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
225 PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
226 PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
227 "--- Insert into t1 --" as "";
228 --- Select from t1 on master ---
229 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
230 id hex(b1) vc bc d f total y t
231 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
232 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
233 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
234 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
235 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
236 --- Select from t1 on slave ---
237 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
238 id hex(b1) vc bc d f total y t
239 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
240 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
241 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
242 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
243 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
244 --- Update t1 on master --
245 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
246 --- Check the update on master ---
247 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
248 id hex(b1) vc bc d f total y t
249 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
250 --- Check Update on slave ---
251 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
252 id hex(b1) vc bc d f total y t
253 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
254 --- Remove a record from t1 on master ---
255 DELETE FROM t1 WHERE id = 42;
256 --- Show current count on master for t1 ---
257 SELECT COUNT(*) FROM t1;
258 COUNT(*)
259 4
260 --- Show current count on slave for t1 ---
261 SELECT COUNT(*) FROM t1;
262 COUNT(*)
263 4
264 DELETE FROM t1;
265 ALTER TABLE t1 MODIFY vc TEXT;
266 --- On master ---
267 SHOW CREATE TABLE t1;
268 Table Create Table
269 t1 CREATE TABLE `t1` (
270 `id` mediumint(9) NOT NULL,
271 `b1` bit(8) DEFAULT NULL,
272 `vc` text,
273 `bc` char(255) DEFAULT NULL,
274 `d` decimal(10,4) DEFAULT '0.0000',
275 `f` float DEFAULT '0',
276 `total` bigint(20) unsigned DEFAULT NULL,
277 `y` year(4) DEFAULT NULL,
278 `t` date DEFAULT NULL
279 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
280 /*!50100 PARTITION BY LIST (id)
281 (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
282 PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
283 PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
284 --- On slave ---
285 SHOW CREATE TABLE t1;
286 Table Create Table
287 t1 CREATE TABLE `t1` (
288 `id` mediumint(9) NOT NULL,
289 `b1` bit(8) DEFAULT NULL,
290 `vc` text,
291 `bc` char(255) DEFAULT NULL,
292 `d` decimal(10,4) DEFAULT '0.0000',
293 `f` float DEFAULT '0',
294 `total` bigint(20) unsigned DEFAULT NULL,
295 `y` year(4) DEFAULT NULL,
296 `t` date DEFAULT NULL
297 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
298 /*!50100 PARTITION BY LIST (id)
299 (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
300 PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
301 PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
302 "--- Insert into t1 --" as "";
303 --- Select from t1 on master ---
304 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
305 id hex(b1) vc bc d f total y t
306 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
307 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
308 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
309 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
310 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
311 --- Select from t1 on slave ---
312 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
313 id hex(b1) vc bc d f total y t
314 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
315 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
316 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
317 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
318 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
319 --- Update t1 on master --
320 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
321 --- Check the update on master ---
322 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
323 id hex(b1) vc bc d f total y t
324 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
325 --- Check Update on slave ---
326 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
327 id hex(b1) vc bc d f total y t
328 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
329 --- Remove a record from t1 on master ---
330 DELETE FROM t1 WHERE id = 42;
331 --- Show current count on master for t1 ---
332 SELECT COUNT(*) FROM t1;
333 COUNT(*)
334 4
335 --- Show current count on slave for t1 ---
336 SELECT COUNT(*) FROM t1;
337 COUNT(*)
338 4
339 DELETE FROM t1;
340 DROP TABLE IF EXISTS t1;
341 **** Partition HASH testing ****
342 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
343 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
344 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
345 y YEAR, t DATE)
346 PARTITION BY HASH( YEAR(t) )
347 PARTITIONS 4;
348 --- On master ---
349 SHOW CREATE TABLE t1;
350 Table Create Table
351 t1 CREATE TABLE `t1` (
352 `id` mediumint(9) NOT NULL,
353 `b1` bit(8) DEFAULT NULL,
354 `vc` varchar(255) DEFAULT NULL,
355 `bc` char(255) DEFAULT NULL,
356 `d` decimal(10,4) DEFAULT '0.0000',
357 `f` float DEFAULT '0',
358 `total` bigint(20) unsigned DEFAULT NULL,
359 `y` year(4) DEFAULT NULL,
360 `t` date DEFAULT NULL
361 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
362 /*!50100 PARTITION BY HASH ( YEAR(t))
363 PARTITIONS 4 */
364 --- On slave ---
365 SHOW CREATE TABLE t1;
366 Table Create Table
367 t1 CREATE TABLE `t1` (
368 `id` mediumint(9) NOT NULL,
369 `b1` bit(8) DEFAULT NULL,
370 `vc` varchar(255) DEFAULT NULL,
371 `bc` char(255) DEFAULT NULL,
372 `d` decimal(10,4) DEFAULT '0.0000',
373 `f` float DEFAULT '0',
374 `total` bigint(20) unsigned DEFAULT NULL,
375 `y` year(4) DEFAULT NULL,
376 `t` date DEFAULT NULL
377 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
378 /*!50100 PARTITION BY HASH ( YEAR(t))
379 PARTITIONS 4 */
380 "--- Insert into t1 --" as "";
381 --- Select from t1 on master ---
382 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
383 id hex(b1) vc bc d f total y t
384 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
385 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
386 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
387 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
388 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
389 --- Select from t1 on slave ---
390 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
391 id hex(b1) vc bc d f total y t
392 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
393 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
394 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
395 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
396 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
397 --- Update t1 on master --
398 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
399 --- Check the update on master ---
400 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
401 id hex(b1) vc bc d f total y t
402 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
403 --- Check Update on slave ---
404 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
405 id hex(b1) vc bc d f total y t
406 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
407 --- Remove a record from t1 on master ---
408 DELETE FROM t1 WHERE id = 42;
409 --- Show current count on master for t1 ---
410 SELECT COUNT(*) FROM t1;
411 COUNT(*)
412 4
413 --- Show current count on slave for t1 ---
414 SELECT COUNT(*) FROM t1;
415 COUNT(*)
416 4
417 DELETE FROM t1;
418 ALTER TABLE t1 MODIFY vc TEXT;
419 --- On master ---
420 SHOW CREATE TABLE t1;
421 Table Create Table
422 t1 CREATE TABLE `t1` (
423 `id` mediumint(9) NOT NULL,
424 `b1` bit(8) DEFAULT NULL,
425 `vc` text,
426 `bc` char(255) DEFAULT NULL,
427 `d` decimal(10,4) DEFAULT '0.0000',
428 `f` float DEFAULT '0',
429 `total` bigint(20) unsigned DEFAULT NULL,
430 `y` year(4) DEFAULT NULL,
431 `t` date DEFAULT NULL
432 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
433 /*!50100 PARTITION BY HASH ( YEAR(t))
434 PARTITIONS 4 */
435 --- On slave ---
436 SHOW CREATE TABLE t1;
437 Table Create Table
438 t1 CREATE TABLE `t1` (
439 `id` mediumint(9) NOT NULL,
440 `b1` bit(8) DEFAULT NULL,
441 `vc` text,
442 `bc` char(255) DEFAULT NULL,
443 `d` decimal(10,4) DEFAULT '0.0000',
444 `f` float DEFAULT '0',
445 `total` bigint(20) unsigned DEFAULT NULL,
446 `y` year(4) DEFAULT NULL,
447 `t` date DEFAULT NULL
448 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
449 /*!50100 PARTITION BY HASH ( YEAR(t))
450 PARTITIONS 4 */
451 "--- Insert into t1 --" as "";
452 --- Select from t1 on master ---
453 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
454 id hex(b1) vc bc d f total y t
455 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
456 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
457 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
458 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
459 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
460 --- Select from t1 on slave ---
461 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
462 id hex(b1) vc bc d f total y t
463 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
464 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
465 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
466 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
467 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
468 --- Update t1 on master --
469 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
470 --- Check the update on master ---
471 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
472 id hex(b1) vc bc d f total y t
473 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
474 --- Check Update on slave ---
475 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
476 id hex(b1) vc bc d f total y t
477 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
478 --- Remove a record from t1 on master ---
479 DELETE FROM t1 WHERE id = 42;
480 --- Show current count on master for t1 ---
481 SELECT COUNT(*) FROM t1;
482 COUNT(*)
483 4
484 --- Show current count on slave for t1 ---
485 SELECT COUNT(*) FROM t1;
486 COUNT(*)
487 4
488 DELETE FROM t1;
489 DROP TABLE IF EXISTS t1;
490 **** Partition by KEY ****
491 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
492 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
493 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
494 y YEAR, t DATE,PRIMARY KEY(id))
495 PARTITION BY KEY()
496 PARTITIONS 4;
497 --- On master ---
498 SHOW CREATE TABLE t1;
499 Table Create Table
500 t1 CREATE TABLE `t1` (
501 `id` mediumint(9) NOT NULL,
502 `b1` bit(8) DEFAULT NULL,
503 `vc` varchar(255) DEFAULT NULL,
504 `bc` char(255) DEFAULT NULL,
505 `d` decimal(10,4) DEFAULT '0.0000',
506 `f` float DEFAULT '0',
507 `total` bigint(20) unsigned DEFAULT NULL,
508 `y` year(4) DEFAULT NULL,
509 `t` date DEFAULT NULL,
510 PRIMARY KEY (`id`)
511 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
512 /*!50100 PARTITION BY KEY ()
513 PARTITIONS 4 */
514 --- On slave ---
515 SHOW CREATE TABLE t1;
516 Table Create Table
517 t1 CREATE TABLE `t1` (
518 `id` mediumint(9) NOT NULL,
519 `b1` bit(8) DEFAULT NULL,
520 `vc` varchar(255) DEFAULT NULL,
521 `bc` char(255) DEFAULT NULL,
522 `d` decimal(10,4) DEFAULT '0.0000',
523 `f` float DEFAULT '0',
524 `total` bigint(20) unsigned DEFAULT NULL,
525 `y` year(4) DEFAULT NULL,
526 `t` date DEFAULT NULL,
527 PRIMARY KEY (`id`)
528 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
529 /*!50100 PARTITION BY KEY ()
530 PARTITIONS 4 */
531 "--- Insert into t1 --" as "";
532 --- Select from t1 on master ---
533 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
534 id hex(b1) vc bc d f total y t
535 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
536 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
537 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
538 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
539 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
540 --- Select from t1 on slave ---
541 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
542 id hex(b1) vc bc d f total y t
543 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
544 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
545 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
546 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
547 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
548 --- Update t1 on master --
549 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
550 --- Check the update on master ---
551 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
552 id hex(b1) vc bc d f total y t
553 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
554 --- Check Update on slave ---
555 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
556 id hex(b1) vc bc d f total y t
557 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
558 --- Remove a record from t1 on master ---
559 DELETE FROM t1 WHERE id = 42;
560 --- Show current count on master for t1 ---
561 SELECT COUNT(*) FROM t1;
562 COUNT(*)
563 4
564 --- Show current count on slave for t1 ---
565 SELECT COUNT(*) FROM t1;
566 COUNT(*)
567 4
568 DELETE FROM t1;
569 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
570 --- On master ---
571 SHOW CREATE TABLE t1;
572 Table Create Table
573 t1 CREATE TABLE `t1` (
574 `id` mediumint(9) NOT NULL,
575 `b1` bit(8) DEFAULT NULL,
576 `vc` varchar(255) DEFAULT NULL,
577 `bc` char(255) DEFAULT NULL,
578 `d` decimal(10,4) DEFAULT '0.0000',
579 `f` float DEFAULT '0',
580 `total` bigint(20) unsigned NOT NULL DEFAULT '0',
581 `y` year(4) DEFAULT NULL,
582 `t` date DEFAULT NULL,
583 PRIMARY KEY (`id`,`total`)
584 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
585 /*!50100 PARTITION BY KEY ()
586 PARTITIONS 4 */
587 --- On slave ---
588 SHOW CREATE TABLE t1;
589 Table Create Table
590 t1 CREATE TABLE `t1` (
591 `id` mediumint(9) NOT NULL,
592 `b1` bit(8) DEFAULT NULL,
593 `vc` varchar(255) DEFAULT NULL,
594 `bc` char(255) DEFAULT NULL,
595 `d` decimal(10,4) DEFAULT '0.0000',
596 `f` float DEFAULT '0',
597 `total` bigint(20) unsigned NOT NULL DEFAULT '0',
598 `y` year(4) DEFAULT NULL,
599 `t` date DEFAULT NULL,
600 PRIMARY KEY (`id`,`total`)
601 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
602 /*!50100 PARTITION BY KEY ()
603 PARTITIONS 4 */
604 "--- Insert into t1 --" as "";
605 --- Select from t1 on master ---
606 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
607 id hex(b1) vc bc d f total y t
608 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
609 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
610 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
611 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
612 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
613 --- Select from t1 on slave ---
614 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
615 id hex(b1) vc bc d f total y t
616 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
617 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
618 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
619 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
620 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
621 --- Update t1 on master --
622 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
623 --- Check the update on master ---
624 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
625 id hex(b1) vc bc d f total y t
626 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
627 --- Check Update on slave ---
628 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
629 id hex(b1) vc bc d f total y t
630 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
631 --- Remove a record from t1 on master ---
632 DELETE FROM t1 WHERE id = 42;
633 --- Show current count on master for t1 ---
634 SELECT COUNT(*) FROM t1;
635 COUNT(*)
636 4
637 --- Show current count on slave for t1 ---
638 SELECT COUNT(*) FROM t1;
639 COUNT(*)
640 4
641 DELETE FROM t1;
642 ALTER TABLE t1 MODIFY vc TEXT;
643 --- On master ---
644 SHOW CREATE TABLE t1;
645 Table Create Table
646 t1 CREATE TABLE `t1` (
647 `id` mediumint(9) NOT NULL,
648 `b1` bit(8) DEFAULT NULL,
649 `vc` text,
650 `bc` char(255) DEFAULT NULL,
651 `d` decimal(10,4) DEFAULT '0.0000',
652 `f` float DEFAULT '0',
653 `total` bigint(20) unsigned NOT NULL DEFAULT '0',
654 `y` year(4) DEFAULT NULL,
655 `t` date DEFAULT NULL,
656 PRIMARY KEY (`id`,`total`)
657 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
658 /*!50100 PARTITION BY KEY ()
659 PARTITIONS 4 */
660 --- On slave ---
661 SHOW CREATE TABLE t1;
662 Table Create Table
663 t1 CREATE TABLE `t1` (
664 `id` mediumint(9) NOT NULL,
665 `b1` bit(8) DEFAULT NULL,
666 `vc` text,
667 `bc` char(255) DEFAULT NULL,
668 `d` decimal(10,4) DEFAULT '0.0000',
669 `f` float DEFAULT '0',
670 `total` bigint(20) unsigned NOT NULL DEFAULT '0',
671 `y` year(4) DEFAULT NULL,
672 `t` date DEFAULT NULL,
673 PRIMARY KEY (`id`,`total`)
674 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
675 /*!50100 PARTITION BY KEY ()
676 PARTITIONS 4 */
677 "--- Insert into t1 --" as "";
678 --- Select from t1 on master ---
679 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
680 id hex(b1) vc bc d f total y t
681 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
682 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
683 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
684 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
685 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
686 --- Select from t1 on slave ---
687 select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
688 id hex(b1) vc bc d f total y t
689 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
690 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
691 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
692 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
693 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
694 --- Update t1 on master --
695 UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
696 --- Check the update on master ---
697 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
698 id hex(b1) vc bc d f total y t
699 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
700 --- Check Update on slave ---
701 SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
702 id hex(b1) vc bc d f total y t
703 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
704 --- Remove a record from t1 on master ---
705 DELETE FROM t1 WHERE id = 42;
706 --- Show current count on master for t1 ---
707 SELECT COUNT(*) FROM t1;
708 COUNT(*)
709 4
710 --- Show current count on slave for t1 ---
711 SELECT COUNT(*) FROM t1;
712 COUNT(*)
713 4
714 DELETE FROM t1;
715 DROP TABLE IF EXISTS t1;
716 include/rpl_end.inc