2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
DROP TABLE IF EXISTS t1;
8
SET @@BINLOG_FORMAT = ROW;
9
SELECT @@BINLOG_FORMAT;
12
**** Partition RANGE testing ****
13
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
14
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
15
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
17
PARTITION BY RANGE (YEAR(t))
18
(PARTITION p0 VALUES LESS THAN (1901),
19
PARTITION p1 VALUES LESS THAN (1946),
20
PARTITION p2 VALUES LESS THAN (1966),
21
PARTITION p3 VALUES LESS THAN (1986),
22
PARTITION p4 VALUES LESS THAN (2005),
23
PARTITION p5 VALUES LESS THAN MAXVALUE);
27
t1 CREATE TABLE `t1` (
28
`id` mediumint(9) NOT NULL,
29
`b1` bit(8) DEFAULT NULL,
30
`vc` varchar(255) DEFAULT NULL,
31
`bc` char(255) DEFAULT NULL,
32
`d` decimal(10,4) DEFAULT '0.0000',
33
`f` float DEFAULT '0',
34
`total` bigint(20) unsigned DEFAULT NULL,
35
`y` year(4) DEFAULT NULL,
37
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
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,
51
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
52
"--- Insert into t1 --" as "";
53
--- Select from t1 on master ---
54
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
55
id hex(b1) vc bc d f total y t
56
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
57
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
58
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
59
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
60
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
61
--- Select from t1 on slave ---
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
--- Update t1 on master --
70
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
71
--- Check the update on master ---
72
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
73
id hex(b1) vc bc d f total y t
74
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
75
--- Check Update on slave ---
76
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
77
id hex(b1) vc bc d f total y t
78
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
79
--- Remove a record from t1 on master ---
80
DELETE FROM t1 WHERE id = 42;
81
--- Show current count on master for t1 ---
82
SELECT COUNT(*) FROM t1;
85
--- Show current count on slave for t1 ---
86
SELECT COUNT(*) FROM t1;
90
ALTER TABLE t1 MODIFY vc TEXT;
94
t1 CREATE TABLE `t1` (
95
`id` mediumint(9) NOT NULL,
96
`b1` bit(8) DEFAULT NULL,
98
`bc` char(255) DEFAULT NULL,
99
`d` decimal(10,4) DEFAULT '0.0000',
100
`f` float DEFAULT '0',
101
`total` bigint(20) unsigned DEFAULT NULL,
102
`y` year(4) DEFAULT NULL,
103
`t` date DEFAULT NULL
104
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
106
SHOW CREATE TABLE t1;
108
t1 CREATE TABLE `t1` (
109
`id` mediumint(9) NOT NULL,
110
`b1` bit(8) 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=latin1 /*!50100 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
119
"--- Insert into t1 --" as "";
120
--- Select from t1 on master ---
121
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
122
id hex(b1) vc bc d f total y t
123
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
124
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
125
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
126
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
127
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
128
--- Select from t1 on slave ---
129
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
130
id hex(b1) vc bc d f total y t
131
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
132
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
133
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
134
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
135
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
136
--- Update t1 on master --
137
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
138
--- Check the update on master ---
139
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
140
id hex(b1) vc bc d f total y t
141
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
142
--- Check Update on slave ---
143
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
144
id hex(b1) vc bc d f total y t
145
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
146
--- Remove a record from t1 on master ---
147
DELETE FROM t1 WHERE id = 42;
148
--- Show current count on master for t1 ---
149
SELECT COUNT(*) FROM t1;
152
--- Show current count on slave for t1 ---
153
SELECT COUNT(*) FROM t1;
157
DROP TABLE IF EXISTS t1;
158
**** Partition LIST testing ****
159
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
160
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
161
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
163
PARTITION BY LIST(id)
164
(PARTITION p0 VALUES IN (2, 4),
165
PARTITION p1 VALUES IN (42, 142),
166
PARTITION p2 VALUES IN (412));
168
SHOW CREATE TABLE t1;
170
t1 CREATE TABLE `t1` (
171
`id` mediumint(9) NOT NULL,
172
`b1` bit(8) DEFAULT NULL,
173
`vc` varchar(255) DEFAULT NULL,
174
`bc` char(255) DEFAULT NULL,
175
`d` decimal(10,4) DEFAULT '0.0000',
176
`f` float DEFAULT '0',
177
`total` bigint(20) unsigned DEFAULT NULL,
178
`y` year(4) DEFAULT NULL,
179
`t` date DEFAULT NULL
180
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM, PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
182
SHOW CREATE TABLE t1;
184
t1 CREATE TABLE `t1` (
185
`id` mediumint(9) NOT NULL,
186
`b1` bit(8) DEFAULT NULL,
187
`vc` varchar(255) DEFAULT NULL,
188
`bc` char(255) DEFAULT NULL,
189
`d` decimal(10,4) DEFAULT '0.0000',
190
`f` float DEFAULT '0',
191
`total` bigint(20) unsigned DEFAULT NULL,
192
`y` year(4) DEFAULT NULL,
193
`t` date DEFAULT NULL
194
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM, PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
195
"--- Insert into t1 --" as "";
196
--- Select from t1 on master ---
197
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
198
id hex(b1) vc bc d f total y t
199
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
200
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
201
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
202
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
203
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
204
--- Select from t1 on slave ---
205
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
206
id hex(b1) vc bc d f total y t
207
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
208
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
209
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
210
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
211
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
212
--- Update t1 on master --
213
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
214
--- Check the update on master ---
215
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
216
id hex(b1) vc bc d f total y t
217
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
218
--- Check Update on slave ---
219
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
220
id hex(b1) vc bc d f total y t
221
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
222
--- Remove a record from t1 on master ---
223
DELETE FROM t1 WHERE id = 42;
224
--- Show current count on master for t1 ---
225
SELECT COUNT(*) FROM t1;
228
--- Show current count on slave for t1 ---
229
SELECT COUNT(*) FROM t1;
233
ALTER TABLE t1 MODIFY vc TEXT;
235
SHOW CREATE TABLE t1;
237
t1 CREATE TABLE `t1` (
238
`id` mediumint(9) NOT NULL,
239
`b1` bit(8) DEFAULT NULL,
241
`bc` char(255) DEFAULT NULL,
242
`d` decimal(10,4) DEFAULT '0.0000',
243
`f` float DEFAULT '0',
244
`total` bigint(20) unsigned DEFAULT NULL,
245
`y` year(4) DEFAULT NULL,
246
`t` date DEFAULT NULL
247
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM, PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
249
SHOW CREATE TABLE t1;
251
t1 CREATE TABLE `t1` (
252
`id` mediumint(9) NOT NULL,
253
`b1` bit(8) DEFAULT NULL,
255
`bc` char(255) DEFAULT NULL,
256
`d` decimal(10,4) DEFAULT '0.0000',
257
`f` float DEFAULT '0',
258
`total` bigint(20) unsigned DEFAULT NULL,
259
`y` year(4) DEFAULT NULL,
260
`t` date DEFAULT NULL
261
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM, PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
262
"--- Insert into t1 --" as "";
263
--- Select from t1 on master ---
264
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
265
id hex(b1) vc bc d f total y t
266
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
267
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
268
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
269
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
270
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
271
--- Select from t1 on slave ---
272
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
273
id hex(b1) vc bc d f total y t
274
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
275
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
276
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
277
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
278
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
279
--- Update t1 on master --
280
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
281
--- Check the update on master ---
282
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
283
id hex(b1) vc bc d f total y t
284
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
285
--- Check Update on slave ---
286
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
287
id hex(b1) vc bc d f total y t
288
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
289
--- Remove a record from t1 on master ---
290
DELETE FROM t1 WHERE id = 42;
291
--- Show current count on master for t1 ---
292
SELECT COUNT(*) FROM t1;
295
--- Show current count on slave for t1 ---
296
SELECT COUNT(*) FROM t1;
300
DROP TABLE IF EXISTS t1;
301
**** Partition HASH testing ****
302
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
303
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
304
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
306
PARTITION BY HASH( YEAR(t) )
309
SHOW CREATE TABLE t1;
311
t1 CREATE TABLE `t1` (
312
`id` mediumint(9) NOT NULL,
313
`b1` bit(8) DEFAULT NULL,
314
`vc` varchar(255) DEFAULT NULL,
315
`bc` char(255) DEFAULT NULL,
316
`d` decimal(10,4) DEFAULT '0.0000',
317
`f` float DEFAULT '0',
318
`total` bigint(20) unsigned DEFAULT NULL,
319
`y` year(4) DEFAULT NULL,
320
`t` date DEFAULT NULL
321
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4 */
323
SHOW CREATE TABLE t1;
325
t1 CREATE TABLE `t1` (
326
`id` mediumint(9) NOT NULL,
327
`b1` bit(8) DEFAULT NULL,
328
`vc` varchar(255) DEFAULT NULL,
329
`bc` char(255) DEFAULT NULL,
330
`d` decimal(10,4) DEFAULT '0.0000',
331
`f` float DEFAULT '0',
332
`total` bigint(20) unsigned DEFAULT NULL,
333
`y` year(4) DEFAULT NULL,
334
`t` date DEFAULT NULL
335
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4 */
336
"--- Insert into t1 --" as "";
337
--- Select from t1 on master ---
338
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
339
id hex(b1) vc bc d f total y t
340
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
341
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
342
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
343
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
344
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
345
--- Select from t1 on slave ---
346
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
347
id hex(b1) vc bc d f total y t
348
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
349
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
350
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
351
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
352
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
353
--- Update t1 on master --
354
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
355
--- Check the update on master ---
356
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
357
id hex(b1) vc bc d f total y t
358
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
359
--- Check Update on slave ---
360
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
361
id hex(b1) vc bc d f total y t
362
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
363
--- Remove a record from t1 on master ---
364
DELETE FROM t1 WHERE id = 42;
365
--- Show current count on master for t1 ---
366
SELECT COUNT(*) FROM t1;
369
--- Show current count on slave for t1 ---
370
SELECT COUNT(*) FROM t1;
374
ALTER TABLE t1 MODIFY vc TEXT;
376
SHOW CREATE TABLE t1;
378
t1 CREATE TABLE `t1` (
379
`id` mediumint(9) NOT NULL,
380
`b1` bit(8) DEFAULT NULL,
382
`bc` char(255) DEFAULT NULL,
383
`d` decimal(10,4) DEFAULT '0.0000',
384
`f` float DEFAULT '0',
385
`total` bigint(20) unsigned DEFAULT NULL,
386
`y` year(4) DEFAULT NULL,
387
`t` date DEFAULT NULL
388
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4 */
390
SHOW CREATE TABLE t1;
392
t1 CREATE TABLE `t1` (
393
`id` mediumint(9) NOT NULL,
394
`b1` bit(8) 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=latin1 /*!50100 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4 */
403
"--- Insert into t1 --" as "";
404
--- Select from t1 on master ---
405
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
406
id hex(b1) vc bc d f total y t
407
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
408
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
409
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
410
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
411
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
412
--- Select from t1 on slave ---
413
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
414
id hex(b1) vc bc d f total y t
415
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
416
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
417
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
418
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
419
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
420
--- Update t1 on master --
421
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
422
--- Check the update on master ---
423
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
424
id hex(b1) vc bc d f total y t
425
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
426
--- Check Update on slave ---
427
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
428
id hex(b1) vc bc d f total y t
429
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
430
--- Remove a record from t1 on master ---
431
DELETE FROM t1 WHERE id = 42;
432
--- Show current count on master for t1 ---
433
SELECT COUNT(*) FROM t1;
436
--- Show current count on slave for t1 ---
437
SELECT COUNT(*) FROM t1;
441
DROP TABLE IF EXISTS t1;
442
**** Partition by KEY ****
443
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
444
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
445
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
446
y YEAR, t DATE,PRIMARY KEY(id))
450
SHOW CREATE TABLE t1;
452
t1 CREATE TABLE `t1` (
453
`id` mediumint(9) NOT NULL,
454
`b1` bit(8) DEFAULT NULL,
455
`vc` varchar(255) DEFAULT NULL,
456
`bc` char(255) DEFAULT NULL,
457
`d` decimal(10,4) DEFAULT '0.0000',
458
`f` float DEFAULT '0',
459
`total` bigint(20) unsigned DEFAULT NULL,
460
`y` year(4) DEFAULT NULL,
461
`t` date DEFAULT NULL,
463
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
465
SHOW CREATE TABLE t1;
467
t1 CREATE TABLE `t1` (
468
`id` mediumint(9) NOT NULL,
469
`b1` bit(8) DEFAULT NULL,
470
`vc` varchar(255) DEFAULT NULL,
471
`bc` char(255) DEFAULT NULL,
472
`d` decimal(10,4) DEFAULT '0.0000',
473
`f` float DEFAULT '0',
474
`total` bigint(20) unsigned DEFAULT NULL,
475
`y` year(4) DEFAULT NULL,
476
`t` date DEFAULT NULL,
478
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
479
"--- Insert into t1 --" as "";
480
--- Select from t1 on master ---
481
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
482
id hex(b1) vc bc d f total y t
483
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
484
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
485
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
486
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
487
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
488
--- Select from t1 on slave ---
489
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
490
id hex(b1) vc bc d f total y t
491
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
492
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
493
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
494
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
495
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
496
--- Update t1 on master --
497
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
498
--- Check the update on master ---
499
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
500
id hex(b1) vc bc d f total y t
501
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
502
--- Check Update on slave ---
503
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
504
id hex(b1) vc bc d f total y t
505
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
506
--- Remove a record from t1 on master ---
507
DELETE FROM t1 WHERE id = 42;
508
--- Show current count on master for t1 ---
509
SELECT COUNT(*) FROM t1;
512
--- Show current count on slave for t1 ---
513
SELECT COUNT(*) FROM t1;
517
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
519
SHOW CREATE TABLE t1;
521
t1 CREATE TABLE `t1` (
522
`id` mediumint(9) NOT NULL,
523
`b1` bit(8) DEFAULT NULL,
524
`vc` varchar(255) DEFAULT NULL,
525
`bc` char(255) DEFAULT NULL,
526
`d` decimal(10,4) DEFAULT '0.0000',
527
`f` float DEFAULT '0',
528
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
529
`y` year(4) DEFAULT NULL,
530
`t` date DEFAULT NULL,
531
PRIMARY KEY (`id`,`total`)
532
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
534
SHOW CREATE TABLE t1;
536
t1 CREATE TABLE `t1` (
537
`id` mediumint(9) NOT NULL,
538
`b1` bit(8) DEFAULT NULL,
539
`vc` varchar(255) DEFAULT NULL,
540
`bc` char(255) DEFAULT NULL,
541
`d` decimal(10,4) DEFAULT '0.0000',
542
`f` float DEFAULT '0',
543
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
544
`y` year(4) DEFAULT NULL,
545
`t` date DEFAULT NULL,
546
PRIMARY KEY (`id`,`total`)
547
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
548
"--- Insert into t1 --" as "";
549
--- Select from t1 on master ---
550
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
551
id hex(b1) vc bc d f total y t
552
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
553
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
554
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
555
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
556
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
557
--- Select from t1 on slave ---
558
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
559
id hex(b1) vc bc d f total y t
560
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
561
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
562
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
563
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
564
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
565
--- Update t1 on master --
566
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
567
--- Check the update on master ---
568
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
569
id hex(b1) vc bc d f total y t
570
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
571
--- Check Update on slave ---
572
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
573
id hex(b1) vc bc d f total y t
574
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
575
--- Remove a record from t1 on master ---
576
DELETE FROM t1 WHERE id = 42;
577
--- Show current count on master for t1 ---
578
SELECT COUNT(*) FROM t1;
581
--- Show current count on slave for t1 ---
582
SELECT COUNT(*) FROM t1;
586
ALTER TABLE t1 MODIFY vc TEXT;
588
SHOW CREATE TABLE t1;
590
t1 CREATE TABLE `t1` (
591
`id` mediumint(9) NOT NULL,
592
`b1` bit(8) 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 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
603
SHOW CREATE TABLE t1;
605
t1 CREATE TABLE `t1` (
606
`id` mediumint(9) NOT NULL,
607
`b1` bit(8) DEFAULT NULL,
609
`bc` char(255) DEFAULT NULL,
610
`d` decimal(10,4) DEFAULT '0.0000',
611
`f` float DEFAULT '0',
612
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
613
`y` year(4) DEFAULT NULL,
614
`t` date DEFAULT NULL,
615
PRIMARY KEY (`id`,`total`)
616
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 4 */
617
"--- Insert into t1 --" as "";
618
--- Select from t1 on master ---
619
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
620
id hex(b1) vc bc d f total y t
621
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
622
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
623
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
624
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
625
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
626
--- Select from t1 on slave ---
627
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
628
id hex(b1) vc bc d f total y t
629
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
630
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
631
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
632
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
633
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
634
--- Update t1 on master --
635
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
636
--- Check the update on master ---
637
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
638
id hex(b1) vc bc d f total y t
639
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
640
--- Check Update on slave ---
641
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
642
id hex(b1) vc bc d f total y t
643
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
644
--- Remove a record from t1 on master ---
645
DELETE FROM t1 WHERE id = 42;
646
--- Show current count on master for t1 ---
647
SELECT COUNT(*) FROM t1;
650
--- Show current count on slave for t1 ---
651
SELECT COUNT(*) FROM t1;
655
DROP TABLE IF EXISTS t1;