1
include/master-slave.inc
3
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
6
DROP TABLE IF EXISTS t1;
7
SET @@BINLOG_FORMAT = ROW;
8
SELECT @@BINLOG_FORMAT;
11
**** Partition RANGE testing ****
12
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
13
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
14
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
16
PARTITION BY RANGE (YEAR(t))
17
(PARTITION p0 VALUES LESS THAN (1901),
18
PARTITION p1 VALUES LESS THAN (1946),
19
PARTITION p2 VALUES LESS THAN (1966),
20
PARTITION p3 VALUES LESS THAN (1986),
21
PARTITION p4 VALUES LESS THAN (2005),
22
PARTITION p5 VALUES LESS THAN MAXVALUE);
26
t1 CREATE TABLE `t1` (
27
`id` mediumint(9) NOT NULL,
28
`b1` bit(8) DEFAULT NULL,
29
`vc` varchar(255) DEFAULT NULL,
30
`bc` char(255) DEFAULT NULL,
31
`d` decimal(10,4) DEFAULT '0.0000',
32
`f` float DEFAULT '0',
33
`total` bigint(20) unsigned DEFAULT NULL,
34
`y` year(4) DEFAULT NULL,
36
) ENGINE=MyISAM DEFAULT CHARSET=latin1
37
/*!50100 PARTITION BY RANGE (YEAR(t))
38
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
39
PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
40
PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
41
PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
42
PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
43
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
47
t1 CREATE TABLE `t1` (
48
`id` mediumint(9) NOT NULL,
49
`b1` bit(8) DEFAULT NULL,
50
`vc` varchar(255) DEFAULT NULL,
51
`bc` char(255) DEFAULT NULL,
52
`d` decimal(10,4) DEFAULT '0.0000',
53
`f` float DEFAULT '0',
54
`total` bigint(20) unsigned DEFAULT NULL,
55
`y` year(4) DEFAULT NULL,
57
) ENGINE=MyISAM DEFAULT CHARSET=latin1
58
/*!50100 PARTITION BY RANGE (YEAR(t))
59
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
60
PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
61
PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
62
PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
63
PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
64
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
65
"--- Insert into t1 --" as "";
66
--- Select from t1 on master ---
67
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
68
id hex(b1) vc bc d f total y t
69
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
70
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
71
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
72
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
73
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
74
--- Select from t1 on slave ---
75
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
76
id hex(b1) vc bc d f total y t
77
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
78
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
79
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
80
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
81
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
82
--- Update t1 on master --
83
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
84
--- Check the update on master ---
85
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
86
id hex(b1) vc bc d f total y t
87
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
88
--- Check Update on slave ---
89
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
90
id hex(b1) vc bc d f total y t
91
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
92
--- Remove a record from t1 on master ---
93
DELETE FROM t1 WHERE id = 42;
94
--- Show current count on master for t1 ---
95
SELECT COUNT(*) FROM t1;
98
--- Show current count on slave for t1 ---
99
SELECT COUNT(*) FROM t1;
103
ALTER TABLE t1 MODIFY vc TEXT;
105
SHOW CREATE TABLE t1;
107
t1 CREATE TABLE `t1` (
108
`id` mediumint(9) NOT NULL,
109
`b1` bit(8) DEFAULT NULL,
111
`bc` char(255) DEFAULT NULL,
112
`d` decimal(10,4) DEFAULT '0.0000',
113
`f` float DEFAULT '0',
114
`total` bigint(20) unsigned DEFAULT NULL,
115
`y` year(4) DEFAULT NULL,
116
`t` date DEFAULT NULL
117
) ENGINE=MyISAM DEFAULT CHARSET=latin1
118
/*!50100 PARTITION BY RANGE (YEAR(t))
119
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
120
PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
121
PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
122
PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
123
PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
124
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
126
SHOW CREATE TABLE t1;
128
t1 CREATE TABLE `t1` (
129
`id` mediumint(9) NOT NULL,
130
`b1` bit(8) DEFAULT NULL,
132
`bc` char(255) DEFAULT NULL,
133
`d` decimal(10,4) DEFAULT '0.0000',
134
`f` float DEFAULT '0',
135
`total` bigint(20) unsigned DEFAULT NULL,
136
`y` year(4) DEFAULT NULL,
137
`t` date DEFAULT NULL
138
) ENGINE=MyISAM DEFAULT CHARSET=latin1
139
/*!50100 PARTITION BY RANGE (YEAR(t))
140
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = MyISAM,
141
PARTITION p1 VALUES LESS THAN (1946) ENGINE = MyISAM,
142
PARTITION p2 VALUES LESS THAN (1966) ENGINE = MyISAM,
143
PARTITION p3 VALUES LESS THAN (1986) ENGINE = MyISAM,
144
PARTITION p4 VALUES LESS THAN (2005) ENGINE = MyISAM,
145
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
146
"--- Insert into t1 --" as "";
147
--- Select from t1 on master ---
148
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
149
id hex(b1) vc bc d f total y t
150
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
151
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
152
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
153
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
154
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
155
--- Select from t1 on slave ---
156
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
157
id hex(b1) vc bc d f total y t
158
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
159
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
160
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
161
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
162
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
163
--- Update t1 on master --
164
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
165
--- Check the update on master ---
166
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
167
id hex(b1) vc bc d f total y t
168
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
169
--- Check Update on slave ---
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
--- Remove a record from t1 on master ---
174
DELETE FROM t1 WHERE id = 42;
175
--- Show current count on master for t1 ---
176
SELECT COUNT(*) FROM t1;
179
--- Show current count on slave for t1 ---
180
SELECT COUNT(*) FROM t1;
184
DROP TABLE IF EXISTS t1;
185
**** Partition LIST testing ****
186
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
187
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
188
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
190
PARTITION BY LIST(id)
191
(PARTITION p0 VALUES IN (2, 4),
192
PARTITION p1 VALUES IN (42, 142),
193
PARTITION p2 VALUES IN (412));
195
SHOW CREATE TABLE t1;
197
t1 CREATE TABLE `t1` (
198
`id` mediumint(9) NOT NULL,
199
`b1` bit(8) DEFAULT NULL,
200
`vc` varchar(255) DEFAULT NULL,
201
`bc` char(255) DEFAULT NULL,
202
`d` decimal(10,4) DEFAULT '0.0000',
203
`f` float DEFAULT '0',
204
`total` bigint(20) unsigned DEFAULT NULL,
205
`y` year(4) DEFAULT NULL,
206
`t` date DEFAULT NULL
207
) ENGINE=MyISAM DEFAULT CHARSET=latin1
208
/*!50100 PARTITION BY LIST (id)
209
(PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
210
PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
211
PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
213
SHOW CREATE TABLE t1;
215
t1 CREATE TABLE `t1` (
216
`id` mediumint(9) NOT NULL,
217
`b1` bit(8) DEFAULT NULL,
218
`vc` varchar(255) DEFAULT NULL,
219
`bc` char(255) DEFAULT NULL,
220
`d` decimal(10,4) DEFAULT '0.0000',
221
`f` float DEFAULT '0',
222
`total` bigint(20) unsigned DEFAULT NULL,
223
`y` year(4) DEFAULT NULL,
224
`t` date DEFAULT NULL
225
) ENGINE=MyISAM DEFAULT CHARSET=latin1
226
/*!50100 PARTITION BY LIST (id)
227
(PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
228
PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
229
PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
230
"--- Insert into t1 --" as "";
231
--- Select from t1 on master ---
232
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
233
id hex(b1) vc bc d f total y t
234
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
235
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
236
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
237
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
238
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
239
--- Select from t1 on slave ---
240
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
241
id hex(b1) vc bc d f total y t
242
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
243
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
244
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
245
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
246
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
247
--- Update t1 on master --
248
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
249
--- Check the update on master ---
250
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
251
id hex(b1) vc bc d f total y t
252
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
253
--- Check Update on slave ---
254
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
255
id hex(b1) vc bc d f total y t
256
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
257
--- Remove a record from t1 on master ---
258
DELETE FROM t1 WHERE id = 42;
259
--- Show current count on master for t1 ---
260
SELECT COUNT(*) FROM t1;
263
--- Show current count on slave for t1 ---
264
SELECT COUNT(*) FROM t1;
268
ALTER TABLE t1 MODIFY vc TEXT;
270
SHOW CREATE TABLE t1;
272
t1 CREATE TABLE `t1` (
273
`id` mediumint(9) NOT NULL,
274
`b1` bit(8) DEFAULT NULL,
276
`bc` char(255) DEFAULT NULL,
277
`d` decimal(10,4) DEFAULT '0.0000',
278
`f` float DEFAULT '0',
279
`total` bigint(20) unsigned DEFAULT NULL,
280
`y` year(4) DEFAULT NULL,
281
`t` date DEFAULT NULL
282
) ENGINE=MyISAM DEFAULT CHARSET=latin1
283
/*!50100 PARTITION BY LIST (id)
284
(PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
285
PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
286
PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
288
SHOW CREATE TABLE t1;
290
t1 CREATE TABLE `t1` (
291
`id` mediumint(9) NOT NULL,
292
`b1` bit(8) DEFAULT NULL,
294
`bc` char(255) DEFAULT NULL,
295
`d` decimal(10,4) DEFAULT '0.0000',
296
`f` float DEFAULT '0',
297
`total` bigint(20) unsigned DEFAULT NULL,
298
`y` year(4) DEFAULT NULL,
299
`t` date DEFAULT NULL
300
) ENGINE=MyISAM DEFAULT CHARSET=latin1
301
/*!50100 PARTITION BY LIST (id)
302
(PARTITION p0 VALUES IN (2,4) ENGINE = MyISAM,
303
PARTITION p1 VALUES IN (42,142) ENGINE = MyISAM,
304
PARTITION p2 VALUES IN (412) ENGINE = MyISAM) */
305
"--- Insert into t1 --" as "";
306
--- Select from t1 on master ---
307
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
308
id hex(b1) vc bc d f total y t
309
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
310
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
311
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
312
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
313
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
314
--- Select from t1 on slave ---
315
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
316
id hex(b1) vc bc d f total y t
317
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
318
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
319
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
320
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
321
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
322
--- Update t1 on master --
323
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
324
--- Check the update on master ---
325
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
326
id hex(b1) vc bc d f total y t
327
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
328
--- Check Update on slave ---
329
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
330
id hex(b1) vc bc d f total y t
331
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
332
--- Remove a record from t1 on master ---
333
DELETE FROM t1 WHERE id = 42;
334
--- Show current count on master for t1 ---
335
SELECT COUNT(*) FROM t1;
338
--- Show current count on slave for t1 ---
339
SELECT COUNT(*) FROM t1;
343
DROP TABLE IF EXISTS t1;
344
**** Partition HASH testing ****
345
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
346
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
347
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
349
PARTITION BY HASH( YEAR(t) )
352
SHOW CREATE TABLE t1;
354
t1 CREATE TABLE `t1` (
355
`id` mediumint(9) NOT NULL,
356
`b1` bit(8) DEFAULT NULL,
357
`vc` varchar(255) DEFAULT NULL,
358
`bc` char(255) DEFAULT NULL,
359
`d` decimal(10,4) DEFAULT '0.0000',
360
`f` float DEFAULT '0',
361
`total` bigint(20) unsigned DEFAULT NULL,
362
`y` year(4) DEFAULT NULL,
363
`t` date DEFAULT NULL
364
) ENGINE=MyISAM DEFAULT CHARSET=latin1
365
/*!50100 PARTITION BY HASH ( YEAR(t))
368
SHOW CREATE TABLE t1;
370
t1 CREATE TABLE `t1` (
371
`id` mediumint(9) NOT NULL,
372
`b1` bit(8) DEFAULT NULL,
373
`vc` varchar(255) DEFAULT NULL,
374
`bc` char(255) DEFAULT NULL,
375
`d` decimal(10,4) DEFAULT '0.0000',
376
`f` float DEFAULT '0',
377
`total` bigint(20) unsigned DEFAULT NULL,
378
`y` year(4) DEFAULT NULL,
379
`t` date DEFAULT NULL
380
) ENGINE=MyISAM DEFAULT CHARSET=latin1
381
/*!50100 PARTITION BY HASH ( YEAR(t))
383
"--- Insert into t1 --" as "";
384
--- Select from t1 on master ---
385
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
386
id hex(b1) vc bc d f total y t
387
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
388
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
389
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
390
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
391
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
392
--- Select from t1 on slave ---
393
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
394
id hex(b1) vc bc d f total y t
395
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
396
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
397
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
398
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
399
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
400
--- Update t1 on master --
401
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
402
--- Check the update on master ---
403
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
404
id hex(b1) vc bc d f total y t
405
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
406
--- Check Update on slave ---
407
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
408
id hex(b1) vc bc d f total y t
409
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
410
--- Remove a record from t1 on master ---
411
DELETE FROM t1 WHERE id = 42;
412
--- Show current count on master for t1 ---
413
SELECT COUNT(*) FROM t1;
416
--- Show current count on slave for t1 ---
417
SELECT COUNT(*) FROM t1;
421
ALTER TABLE t1 MODIFY vc TEXT;
423
SHOW CREATE TABLE t1;
425
t1 CREATE TABLE `t1` (
426
`id` mediumint(9) NOT NULL,
427
`b1` bit(8) DEFAULT NULL,
429
`bc` char(255) DEFAULT NULL,
430
`d` decimal(10,4) DEFAULT '0.0000',
431
`f` float DEFAULT '0',
432
`total` bigint(20) unsigned DEFAULT NULL,
433
`y` year(4) DEFAULT NULL,
434
`t` date DEFAULT NULL
435
) ENGINE=MyISAM DEFAULT CHARSET=latin1
436
/*!50100 PARTITION BY HASH ( YEAR(t))
439
SHOW CREATE TABLE t1;
441
t1 CREATE TABLE `t1` (
442
`id` mediumint(9) NOT NULL,
443
`b1` bit(8) DEFAULT NULL,
445
`bc` char(255) DEFAULT NULL,
446
`d` decimal(10,4) DEFAULT '0.0000',
447
`f` float DEFAULT '0',
448
`total` bigint(20) unsigned DEFAULT NULL,
449
`y` year(4) DEFAULT NULL,
450
`t` date DEFAULT NULL
451
) ENGINE=MyISAM DEFAULT CHARSET=latin1
452
/*!50100 PARTITION BY HASH ( YEAR(t))
454
"--- Insert into t1 --" as "";
455
--- Select from t1 on master ---
456
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
457
id hex(b1) vc bc d f total y t
458
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
459
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
460
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
461
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
462
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
463
--- Select from t1 on slave ---
464
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
465
id hex(b1) vc bc d f total y t
466
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
467
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
468
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
469
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
470
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
471
--- Update t1 on master --
472
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
473
--- Check the update on master ---
474
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
475
id hex(b1) vc bc d f total y t
476
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
477
--- Check Update on slave ---
478
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
479
id hex(b1) vc bc d f total y t
480
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
481
--- Remove a record from t1 on master ---
482
DELETE FROM t1 WHERE id = 42;
483
--- Show current count on master for t1 ---
484
SELECT COUNT(*) FROM t1;
487
--- Show current count on slave for t1 ---
488
SELECT COUNT(*) FROM t1;
492
DROP TABLE IF EXISTS t1;
493
**** Partition by KEY ****
494
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
495
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
496
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
497
y YEAR, t DATE,PRIMARY KEY(id))
501
SHOW CREATE TABLE t1;
503
t1 CREATE TABLE `t1` (
504
`id` mediumint(9) NOT NULL,
505
`b1` bit(8) DEFAULT NULL,
506
`vc` varchar(255) DEFAULT NULL,
507
`bc` char(255) DEFAULT NULL,
508
`d` decimal(10,4) DEFAULT '0.0000',
509
`f` float DEFAULT '0',
510
`total` bigint(20) unsigned DEFAULT NULL,
511
`y` year(4) DEFAULT NULL,
512
`t` date DEFAULT NULL,
514
) ENGINE=MyISAM DEFAULT CHARSET=latin1
515
/*!50100 PARTITION BY KEY ()
518
SHOW CREATE TABLE t1;
520
t1 CREATE TABLE `t1` (
521
`id` mediumint(9) NOT NULL,
522
`b1` bit(8) DEFAULT NULL,
523
`vc` varchar(255) DEFAULT NULL,
524
`bc` char(255) DEFAULT NULL,
525
`d` decimal(10,4) DEFAULT '0.0000',
526
`f` float DEFAULT '0',
527
`total` bigint(20) unsigned DEFAULT NULL,
528
`y` year(4) DEFAULT NULL,
529
`t` date DEFAULT NULL,
531
) ENGINE=MyISAM DEFAULT CHARSET=latin1
532
/*!50100 PARTITION BY KEY ()
534
"--- Insert into t1 --" as "";
535
--- Select from t1 on master ---
536
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
537
id hex(b1) vc bc d f total y t
538
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
539
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
540
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
541
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
542
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
543
--- Select from t1 on slave ---
544
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
545
id hex(b1) vc bc d f total y t
546
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
547
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
548
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
549
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
550
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
551
--- Update t1 on master --
552
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
553
--- Check the update on master ---
554
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
555
id hex(b1) vc bc d f total y t
556
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
557
--- Check Update on slave ---
558
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
559
id hex(b1) vc bc d f total y t
560
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
561
--- Remove a record from t1 on master ---
562
DELETE FROM t1 WHERE id = 42;
563
--- Show current count on master for t1 ---
564
SELECT COUNT(*) FROM t1;
567
--- Show current count on slave for t1 ---
568
SELECT COUNT(*) FROM t1;
572
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
574
SHOW CREATE TABLE t1;
576
t1 CREATE TABLE `t1` (
577
`id` mediumint(9) NOT NULL,
578
`b1` bit(8) DEFAULT NULL,
579
`vc` varchar(255) DEFAULT NULL,
580
`bc` char(255) DEFAULT NULL,
581
`d` decimal(10,4) DEFAULT '0.0000',
582
`f` float DEFAULT '0',
583
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
584
`y` year(4) DEFAULT NULL,
585
`t` date DEFAULT NULL,
586
PRIMARY KEY (`id`,`total`)
587
) ENGINE=MyISAM DEFAULT CHARSET=latin1
588
/*!50100 PARTITION BY KEY ()
591
SHOW CREATE TABLE t1;
593
t1 CREATE TABLE `t1` (
594
`id` mediumint(9) NOT NULL,
595
`b1` bit(8) DEFAULT NULL,
596
`vc` varchar(255) DEFAULT NULL,
597
`bc` char(255) DEFAULT NULL,
598
`d` decimal(10,4) DEFAULT '0.0000',
599
`f` float DEFAULT '0',
600
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
601
`y` year(4) DEFAULT NULL,
602
`t` date DEFAULT NULL,
603
PRIMARY KEY (`id`,`total`)
604
) ENGINE=MyISAM DEFAULT CHARSET=latin1
605
/*!50100 PARTITION BY KEY ()
607
"--- Insert into t1 --" as "";
608
--- Select from t1 on master ---
609
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
610
id hex(b1) vc bc d f total y t
611
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
612
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
613
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
614
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
615
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
616
--- Select from t1 on slave ---
617
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
618
id hex(b1) vc bc d f total y t
619
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
620
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
621
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
622
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
623
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
624
--- Update t1 on master --
625
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
626
--- Check the update on master ---
627
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
628
id hex(b1) vc bc d f total y t
629
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
630
--- Check Update on slave ---
631
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
632
id hex(b1) vc bc d f total y t
633
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
634
--- Remove a record from t1 on master ---
635
DELETE FROM t1 WHERE id = 42;
636
--- Show current count on master for t1 ---
637
SELECT COUNT(*) FROM t1;
640
--- Show current count on slave for t1 ---
641
SELECT COUNT(*) FROM t1;
645
ALTER TABLE t1 MODIFY vc TEXT;
647
SHOW CREATE TABLE t1;
649
t1 CREATE TABLE `t1` (
650
`id` mediumint(9) NOT NULL,
651
`b1` bit(8) DEFAULT NULL,
653
`bc` char(255) DEFAULT NULL,
654
`d` decimal(10,4) DEFAULT '0.0000',
655
`f` float DEFAULT '0',
656
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
657
`y` year(4) DEFAULT NULL,
658
`t` date DEFAULT NULL,
659
PRIMARY KEY (`id`,`total`)
660
) ENGINE=MyISAM DEFAULT CHARSET=latin1
661
/*!50100 PARTITION BY KEY ()
664
SHOW CREATE TABLE t1;
666
t1 CREATE TABLE `t1` (
667
`id` mediumint(9) NOT NULL,
668
`b1` bit(8) DEFAULT NULL,
670
`bc` char(255) DEFAULT NULL,
671
`d` decimal(10,4) DEFAULT '0.0000',
672
`f` float DEFAULT '0',
673
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
674
`y` year(4) DEFAULT NULL,
675
`t` date DEFAULT NULL,
676
PRIMARY KEY (`id`,`total`)
677
) ENGINE=MyISAM DEFAULT CHARSET=latin1
678
/*!50100 PARTITION BY KEY ()
680
"--- Insert into t1 --" as "";
681
--- Select from t1 on master ---
682
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
683
id hex(b1) vc bc d f total y t
684
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
685
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
686
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
687
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
688
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
689
--- Select from t1 on slave ---
690
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
691
id hex(b1) vc bc d f total y t
692
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
693
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
694
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
695
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
696
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
697
--- Update t1 on master --
698
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
699
--- Check the update on master ---
700
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
701
id hex(b1) vc bc d f total y t
702
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
703
--- Check Update on slave ---
704
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
705
id hex(b1) vc bc d f total y t
706
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
707
--- Remove a record from t1 on master ---
708
DELETE FROM t1 WHERE id = 42;
709
--- Show current count on master for t1 ---
710
SELECT COUNT(*) FROM t1;
713
--- Show current count on slave for t1 ---
714
SELECT COUNT(*) FROM t1;
718
DROP TABLE IF EXISTS t1;