Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_basic_8partition.result

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