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
set @@global.slave_exec_mode= 'IDEMPOTENT';
8
CREATE TABLE mysql.ndb_apply_status
9
( server_id INT UNSIGNED NOT NULL,
10
epoch BIGINT UNSIGNED NOT NULL,
11
log_name VARCHAR(255) BINARY NOT NULL,
12
start_pos BIGINT UNSIGNED NOT NULL,
13
end_pos BIGINT UNSIGNED NOT NULL,
14
PRIMARY KEY USING HASH (server_id)) ENGINE=INNODB;
15
--- Doing pre test cleanup ---
16
DROP TABLE IF EXISTS t1;
17
--- Start test 1 Basic testing ---
18
--- Create Table Section ---
19
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
20
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
21
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
22
y YEAR, t DATE,PRIMARY KEY(id));
23
--- Show table on master ---
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,
35
`t` date DEFAULT NULL,
37
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
38
--- Show table on slave ---
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,
52
) ENGINE=InnoDB DEFAULT CHARSET=latin1
53
--- Perform basic operation on master ---
54
--- and ensure replicated correctly ---
55
"--- Insert into t1 --" as "";
56
--- Select from t1 on master ---
57
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
58
id hex(b1) vc bc d f total y t
59
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
60
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
61
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
62
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
63
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
64
--- Select from t1 on slave ---
65
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
66
id hex(b1) vc bc d f total y t
67
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
68
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
69
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
70
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
71
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
72
--- Update t1 on master --
73
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
74
--- Check the update on master ---
75
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
76
id hex(b1) vc bc d f total y t
77
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
78
--- Check Update on slave ---
79
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
80
id hex(b1) vc bc d f total y t
81
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
82
--- Remove a record from t1 on master ---
83
DELETE FROM t1 WHERE id = 42;
84
--- Show current count on master for t1 ---
85
SELECT COUNT(*) FROM t1;
88
--- Show current count on slave for t1 ---
89
SELECT COUNT(*) FROM t1;
93
--- Check that simple Alter statements are replicated correctly --
94
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
95
ALTER TABLE t1 MODIFY vc TEXT;
96
--- Show the new improved table on the master ---
99
t1 CREATE TABLE `t1` (
100
`id` mediumint(9) NOT NULL,
101
`b1` bit(8) DEFAULT NULL,
103
`bc` char(255) DEFAULT NULL,
104
`d` decimal(10,4) DEFAULT '0.0000',
105
`f` float DEFAULT '0',
106
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
107
`y` year(4) DEFAULT NULL,
108
`t` date DEFAULT NULL,
109
PRIMARY KEY (`id`,`total`)
110
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
111
--- Make sure that our tables on slave are still same engine ---
112
--- and that the alter statements replicated correctly ---
113
SHOW CREATE TABLE t1;
115
t1 CREATE TABLE `t1` (
116
`id` mediumint(9) NOT NULL,
117
`b1` bit(8) DEFAULT NULL,
119
`bc` char(255) DEFAULT NULL,
120
`d` decimal(10,4) DEFAULT '0.0000',
121
`f` float DEFAULT '0',
122
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
123
`y` year(4) DEFAULT NULL,
124
`t` date DEFAULT NULL,
125
PRIMARY KEY (`id`,`total`)
126
) ENGINE=InnoDB DEFAULT CHARSET=latin1
127
--- Perform basic operation on master ---
128
--- and ensure replicated correctly ---
129
"--- Insert into t1 --" as "";
130
--- Select from t1 on master ---
131
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
132
id hex(b1) vc bc d f total y t
133
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
134
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
135
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
136
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
137
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
138
--- Select from t1 on slave ---
139
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
140
id hex(b1) vc bc d f total y t
141
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
142
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
143
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
144
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
145
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
146
--- Update t1 on master --
147
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
148
--- Check the update on master ---
149
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
150
id hex(b1) vc bc d f total y t
151
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
152
--- Check Update on slave ---
153
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
154
id hex(b1) vc bc d f total y t
155
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
156
--- Remove a record from t1 on master ---
157
DELETE FROM t1 WHERE id = 42;
158
--- Show current count on master for t1 ---
159
SELECT COUNT(*) FROM t1;
162
--- Show current count on slave for t1 ---
163
SELECT COUNT(*) FROM t1;
167
--- End test 1 Basic testing ---
169
DROP TABLE IF EXISTS t1;
170
--- Start test 2 partition RANGE testing --
172
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
173
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
174
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
176
PARTITION BY RANGE (YEAR(t))
177
(PARTITION p0 VALUES LESS THAN (1901),
178
PARTITION p1 VALUES LESS THAN (1946),
179
PARTITION p2 VALUES LESS THAN (1966),
180
PARTITION p3 VALUES LESS THAN (1986),
181
PARTITION p4 VALUES LESS THAN (2005),
182
PARTITION p5 VALUES LESS THAN MAXVALUE);
183
--- Show table on master ---
184
SHOW CREATE TABLE t1;
186
t1 CREATE TABLE `t1` (
187
`id` mediumint(9) NOT NULL,
188
`b1` bit(8) DEFAULT NULL,
189
`vc` varchar(255) DEFAULT NULL,
190
`bc` char(255) DEFAULT NULL,
191
`d` decimal(10,4) DEFAULT '0.0000',
192
`f` float DEFAULT '0',
193
`total` bigint(20) unsigned DEFAULT NULL,
194
`y` year(4) DEFAULT NULL,
195
`t` date DEFAULT NULL
196
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
197
/*!50100 PARTITION BY RANGE (YEAR(t))
198
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster,
199
PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster,
200
PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster,
201
PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster,
202
PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster,
203
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster) */
204
--- Show table on slave --
205
SHOW CREATE TABLE t1;
207
t1 CREATE TABLE `t1` (
208
`id` mediumint(9) NOT NULL,
209
`b1` bit(8) DEFAULT NULL,
210
`vc` varchar(255) DEFAULT NULL,
211
`bc` char(255) DEFAULT NULL,
212
`d` decimal(10,4) DEFAULT '0.0000',
213
`f` float DEFAULT '0',
214
`total` bigint(20) unsigned DEFAULT NULL,
215
`y` year(4) DEFAULT NULL,
216
`t` date DEFAULT NULL
217
) ENGINE=InnoDB DEFAULT CHARSET=latin1
218
/*!50100 PARTITION BY RANGE (YEAR(t))
219
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = InnoDB,
220
PARTITION p1 VALUES LESS THAN (1946) ENGINE = InnoDB,
221
PARTITION p2 VALUES LESS THAN (1966) ENGINE = InnoDB,
222
PARTITION p3 VALUES LESS THAN (1986) ENGINE = InnoDB,
223
PARTITION p4 VALUES LESS THAN (2005) ENGINE = InnoDB,
224
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
225
--- Perform basic operation on master ---
226
--- and ensure replicated correctly ---
227
"--- Insert into t1 --" as "";
228
--- Select from t1 on master ---
229
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
230
id hex(b1) vc bc d f total y t
231
2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
232
4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
233
42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
234
142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
235
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
236
--- Select from t1 on slave ---
237
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
238
id hex(b1) vc bc d f total y t
239
2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
240
4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
241
42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
242
142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
243
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
244
--- Update t1 on master --
245
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
246
--- Check the update on master ---
247
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
248
id hex(b1) vc bc d f total y t
249
412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
250
--- Check Update on slave ---
251
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
252
id hex(b1) vc bc d f total y t
253
412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
254
--- Remove a record from t1 on master ---
255
DELETE FROM t1 WHERE id = 42;
256
--- Show current count on master for t1 ---
257
SELECT COUNT(*) FROM t1;
260
--- Show current count on slave for t1 ---
261
SELECT COUNT(*) FROM t1;
265
--- Check that simple Alter statements are replicated correctly ---
266
ALTER TABLE t1 ADD PRIMARY KEY(t,id);
267
ALTER TABLE t1 MODIFY vc TEXT;
268
--- Show the new improved table on the master ---
269
SHOW CREATE TABLE t1;
271
t1 CREATE TABLE `t1` (
272
`id` mediumint(9) NOT NULL,
273
`b1` bit(8) DEFAULT NULL,
275
`bc` char(255) DEFAULT NULL,
276
`d` decimal(10,4) DEFAULT '0.0000',
277
`f` float DEFAULT '0',
278
`total` bigint(20) unsigned DEFAULT NULL,
279
`y` year(4) DEFAULT NULL,
280
`t` date NOT NULL DEFAULT '0000-00-00',
281
PRIMARY KEY (`t`,`id`)
282
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
283
/*!50100 PARTITION BY RANGE (YEAR(t))
284
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster,
285
PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster,
286
PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster,
287
PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster,
288
PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster,
289
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster) */
290
--- Make sure that our tables on slave are still same engine ---
291
--- and that the alter statements replicated correctly ---
292
SHOW CREATE TABLE t1;
294
t1 CREATE TABLE `t1` (
295
`id` mediumint(9) NOT NULL,
296
`b1` bit(8) DEFAULT NULL,
298
`bc` char(255) DEFAULT NULL,
299
`d` decimal(10,4) DEFAULT '0.0000',
300
`f` float DEFAULT '0',
301
`total` bigint(20) unsigned DEFAULT NULL,
302
`y` year(4) DEFAULT NULL,
303
`t` date NOT NULL DEFAULT '0000-00-00',
304
PRIMARY KEY (`t`,`id`)
305
) ENGINE=InnoDB DEFAULT CHARSET=latin1
306
/*!50100 PARTITION BY RANGE (YEAR(t))
307
(PARTITION p0 VALUES LESS THAN (1901) ENGINE = InnoDB,
308
PARTITION p1 VALUES LESS THAN (1946) ENGINE = InnoDB,
309
PARTITION p2 VALUES LESS THAN (1966) ENGINE = InnoDB,
310
PARTITION p3 VALUES LESS THAN (1986) ENGINE = InnoDB,
311
PARTITION p4 VALUES LESS THAN (2005) ENGINE = InnoDB,
312
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
313
--- Perform basic operation on master ---
314
--- and ensure replicated correctly ---
315
"--- Insert into t1 --" as "";
316
--- Select from t1 on master ---
317
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
318
id hex(b1) vc bc d f total y t
319
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
320
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
321
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
322
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
323
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
324
--- Select from t1 on slave ---
325
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
326
id hex(b1) vc bc d f total y t
327
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
328
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
329
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
330
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
331
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
332
--- Update t1 on master --
333
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
334
--- Check the update on master ---
335
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
336
id hex(b1) vc bc d f total y t
337
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
338
--- Check Update on slave ---
339
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
340
id hex(b1) vc bc d f total y t
341
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
342
--- Remove a record from t1 on master ---
343
DELETE FROM t1 WHERE id = 42;
344
--- Show current count on master for t1 ---
345
SELECT COUNT(*) FROM t1;
348
--- Show current count on slave for t1 ---
349
SELECT COUNT(*) FROM t1;
353
--- End test 2 partition RANGE testing ---
355
DROP TABLE IF EXISTS t1;
356
--- Start test 3 partition LIST testing ---
358
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
359
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
360
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
362
PARTITION BY LIST(id)
363
(PARTITION p0 VALUES IN (2, 4),
364
PARTITION p1 VALUES IN (42, 142));
365
--- Test 3 Alter to add partition ---
366
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
367
--- Show table on master ---
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=ndbcluster DEFAULT CHARSET=latin1
381
/*!50100 PARTITION BY LIST (id)
382
(PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster,
383
PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster,
384
PARTITION p2 VALUES IN (412) ENGINE = ndbcluster) */
385
--- Show table on slave ---
386
SHOW CREATE TABLE t1;
388
t1 CREATE TABLE `t1` (
389
`id` mediumint(9) NOT NULL,
390
`b1` bit(8) DEFAULT NULL,
391
`vc` varchar(255) DEFAULT NULL,
392
`bc` char(255) DEFAULT NULL,
393
`d` decimal(10,4) DEFAULT '0.0000',
394
`f` float DEFAULT '0',
395
`total` bigint(20) unsigned DEFAULT NULL,
396
`y` year(4) DEFAULT NULL,
397
`t` date DEFAULT NULL
398
) ENGINE=InnoDB DEFAULT CHARSET=latin1
399
/*!50100 PARTITION BY LIST (id)
400
(PARTITION p0 VALUES IN (2,4) ENGINE = InnoDB,
401
PARTITION p1 VALUES IN (42,142) ENGINE = InnoDB,
402
PARTITION p2 VALUES IN (412) ENGINE = InnoDB) */
403
--- Perform basic operation on master ---
404
--- and ensure replicated correctly ---
405
"--- Insert into t1 --" as "";
406
--- Select from t1 on master ---
407
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
408
id hex(b1) vc bc d f total y t
409
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
410
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
411
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
412
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
413
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
414
--- Select from t1 on slave ---
415
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
416
id hex(b1) vc bc d f total y t
417
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
418
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
419
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
420
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
421
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
422
--- Update t1 on master --
423
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
424
--- Check the update on master ---
425
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
426
id hex(b1) vc bc d f total y t
427
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
428
--- Check Update on slave ---
429
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
430
id hex(b1) vc bc d f total y t
431
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
432
--- Remove a record from t1 on master ---
433
DELETE FROM t1 WHERE id = 42;
434
--- Show current count on master for t1 ---
435
SELECT COUNT(*) FROM t1;
438
--- Show current count on slave for t1 ---
439
SELECT COUNT(*) FROM t1;
443
--- Check that simple Alter statements are replicated correctly ---
444
ALTER TABLE t1 ADD PRIMARY KEY(id);
445
ALTER TABLE t1 MODIFY vc TEXT;
446
--- Show the new improved table on the master ---
447
SHOW CREATE TABLE t1;
449
t1 CREATE TABLE `t1` (
450
`id` mediumint(9) NOT NULL,
451
`b1` bit(8) DEFAULT NULL,
453
`bc` char(255) DEFAULT NULL,
454
`d` decimal(10,4) DEFAULT '0.0000',
455
`f` float DEFAULT '0',
456
`total` bigint(20) unsigned DEFAULT NULL,
457
`y` year(4) DEFAULT NULL,
458
`t` date DEFAULT NULL,
460
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
461
/*!50100 PARTITION BY LIST (id)
462
(PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster,
463
PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster,
464
PARTITION p2 VALUES IN (412) ENGINE = ndbcluster) */
465
--- Make sure that our tables on slave are still same engine ---
466
--- and that the alter statements replicated correctly ---
467
SHOW CREATE TABLE t1;
469
t1 CREATE TABLE `t1` (
470
`id` mediumint(9) NOT NULL,
471
`b1` bit(8) 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,
480
) ENGINE=InnoDB DEFAULT CHARSET=latin1
481
/*!50100 PARTITION BY LIST (id)
482
(PARTITION p0 VALUES IN (2,4) ENGINE = InnoDB,
483
PARTITION p1 VALUES IN (42,142) ENGINE = InnoDB,
484
PARTITION p2 VALUES IN (412) ENGINE = InnoDB) */
485
--- Perform basic operation on master ---
486
--- and ensure replicated correctly ---
487
"--- Insert into t1 --" as "";
488
--- Select from t1 on master ---
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
--- Select from t1 on slave ---
497
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
498
id hex(b1) vc bc d f total y t
499
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
500
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
501
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
502
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
503
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
504
--- Update t1 on master --
505
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
506
--- Check the update on master ---
507
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
508
id hex(b1) vc bc d f total y t
509
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
510
--- Check Update on slave ---
511
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
512
id hex(b1) vc bc d f total y t
513
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
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;
520
--- Show current count on slave for t1 ---
521
SELECT COUNT(*) FROM t1;
525
--- End test 3 partition LIST testing ---
527
DROP TABLE IF EXISTS t1;
528
--- Start test 4 partition HASH testing ---
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,
534
PARTITION BY HASH( YEAR(t) )
536
--- show that tables have been created correctly ---
537
SHOW CREATE TABLE t1;
539
t1 CREATE TABLE `t1` (
540
`id` mediumint(9) NOT NULL,
541
`b1` bit(8) DEFAULT NULL,
542
`vc` varchar(255) DEFAULT NULL,
543
`bc` char(255) DEFAULT NULL,
544
`d` decimal(10,4) DEFAULT '0.0000',
545
`f` float DEFAULT '0',
546
`total` bigint(20) unsigned DEFAULT NULL,
547
`y` year(4) DEFAULT NULL,
548
`t` date DEFAULT NULL
549
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
550
/*!50100 PARTITION BY HASH ( YEAR(t))
552
SHOW CREATE TABLE t1;
554
t1 CREATE TABLE `t1` (
555
`id` mediumint(9) NOT NULL,
556
`b1` bit(8) DEFAULT NULL,
557
`vc` varchar(255) DEFAULT NULL,
558
`bc` char(255) DEFAULT NULL,
559
`d` decimal(10,4) DEFAULT '0.0000',
560
`f` float DEFAULT '0',
561
`total` bigint(20) unsigned DEFAULT NULL,
562
`y` year(4) DEFAULT NULL,
563
`t` date DEFAULT NULL
564
) ENGINE=InnoDB DEFAULT CHARSET=latin1
565
/*!50100 PARTITION BY HASH ( YEAR(t))
567
--- Perform basic operation on master ---
568
--- and ensure replicated correctly ---
569
"--- Insert into t1 --" as "";
570
--- Select from t1 on master ---
571
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
572
id hex(b1) vc bc d f total y t
573
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
574
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
575
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
576
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
577
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
578
--- Select from t1 on slave ---
579
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
580
id hex(b1) vc bc d f total y t
581
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
582
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
583
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
584
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
585
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
586
--- Update t1 on master --
587
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
588
--- Check the update on master ---
589
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
590
id hex(b1) vc bc d f total y t
591
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
592
--- Check Update on slave ---
593
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
594
id hex(b1) vc bc d f total y t
595
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
596
--- Remove a record from t1 on master ---
597
DELETE FROM t1 WHERE id = 42;
598
--- Show current count on master for t1 ---
599
SELECT COUNT(*) FROM t1;
602
--- Show current count on slave for t1 ---
603
SELECT COUNT(*) FROM t1;
607
--- Check that simple Alter statements are replicated correctly ---
608
ALTER TABLE t1 ADD PRIMARY KEY(t,id);
609
ALTER TABLE t1 MODIFY vc TEXT;
610
--- Show the new improved table on the master ---
611
SHOW CREATE TABLE t1;
613
t1 CREATE TABLE `t1` (
614
`id` mediumint(9) NOT NULL,
615
`b1` bit(8) DEFAULT NULL,
617
`bc` char(255) DEFAULT NULL,
618
`d` decimal(10,4) DEFAULT '0.0000',
619
`f` float DEFAULT '0',
620
`total` bigint(20) unsigned DEFAULT NULL,
621
`y` year(4) DEFAULT NULL,
622
`t` date NOT NULL DEFAULT '0000-00-00',
623
PRIMARY KEY (`t`,`id`)
624
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
625
/*!50100 PARTITION BY HASH ( YEAR(t))
627
--- Make sure that our tables on slave are still same engine ---
628
--- and that the alter statements replicated correctly ---
629
SHOW CREATE TABLE t1;
631
t1 CREATE TABLE `t1` (
632
`id` mediumint(9) NOT NULL,
633
`b1` bit(8) DEFAULT NULL,
635
`bc` char(255) DEFAULT NULL,
636
`d` decimal(10,4) DEFAULT '0.0000',
637
`f` float DEFAULT '0',
638
`total` bigint(20) unsigned DEFAULT NULL,
639
`y` year(4) DEFAULT NULL,
640
`t` date NOT NULL DEFAULT '0000-00-00',
641
PRIMARY KEY (`t`,`id`)
642
) ENGINE=InnoDB DEFAULT CHARSET=latin1
643
/*!50100 PARTITION BY HASH ( YEAR(t))
645
--- Perform basic operation on master ---
646
--- and ensure replicated correctly ---
647
"--- Insert into t1 --" as "";
648
--- Select from t1 on master ---
649
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
650
id hex(b1) vc bc d f total y t
651
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
652
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
653
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
654
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
655
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
656
--- Select from t1 on slave ---
657
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
658
id hex(b1) vc bc d f total y t
659
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
660
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
661
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
662
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
663
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
664
--- Update t1 on master --
665
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
666
--- Check the update on master ---
667
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
668
id hex(b1) vc bc d f total y t
669
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
670
--- Check Update on slave ---
671
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
672
id hex(b1) vc bc d f total y t
673
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
674
--- Remove a record from t1 on master ---
675
DELETE FROM t1 WHERE id = 42;
676
--- Show current count on master for t1 ---
677
SELECT COUNT(*) FROM t1;
680
--- Show current count on slave for t1 ---
681
SELECT COUNT(*) FROM t1;
685
--- End test 4 partition HASH testing ---
687
DROP TABLE IF EXISTS t1;
688
--- Start test 5 partition by key testing ---
689
--- Create Table Section ---
690
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
691
bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
692
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
693
y YEAR, t DATE,PRIMARY KEY(id))
696
--- Show that tables on master are ndbcluster tables ---
697
SHOW CREATE TABLE t1;
699
t1 CREATE TABLE `t1` (
700
`id` mediumint(9) NOT NULL,
701
`b1` bit(8) DEFAULT NULL,
702
`vc` varchar(255) DEFAULT NULL,
703
`bc` char(255) DEFAULT NULL,
704
`d` decimal(10,4) DEFAULT '0.0000',
705
`f` float DEFAULT '0',
706
`total` bigint(20) unsigned DEFAULT NULL,
707
`y` year(4) DEFAULT NULL,
708
`t` date DEFAULT NULL,
710
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
711
/*!50100 PARTITION BY KEY ()
713
--- Show that tables on slave ---
714
SHOW CREATE TABLE t1;
716
t1 CREATE TABLE `t1` (
717
`id` mediumint(9) NOT NULL,
718
`b1` bit(8) DEFAULT NULL,
719
`vc` varchar(255) DEFAULT NULL,
720
`bc` char(255) DEFAULT NULL,
721
`d` decimal(10,4) DEFAULT '0.0000',
722
`f` float DEFAULT '0',
723
`total` bigint(20) unsigned DEFAULT NULL,
724
`y` year(4) DEFAULT NULL,
725
`t` date DEFAULT NULL,
727
) ENGINE=InnoDB DEFAULT CHARSET=latin1
728
/*!50100 PARTITION BY KEY ()
730
--- Perform basic operation on master ---
731
--- and ensure replicated correctly ---
732
"--- Insert into t1 --" as "";
733
--- Select from t1 on master ---
734
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
735
id hex(b1) vc bc d f total y t
736
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
737
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
738
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
739
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
740
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
741
--- Select from t1 on slave ---
742
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
743
id hex(b1) vc bc d f total y t
744
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
745
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
746
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
747
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
748
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
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
--- Check Update on slave ---
756
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
757
id hex(b1) vc bc d f total y t
758
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
759
--- Remove a record from t1 on master ---
760
DELETE FROM t1 WHERE id = 42;
761
--- Show current count on master for t1 ---
762
SELECT COUNT(*) FROM t1;
765
--- Show current count on slave for t1 ---
766
SELECT COUNT(*) FROM t1;
770
--- Check that simple Alter statements are replicated correctly ---
771
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
772
--- Show the new improved table on the master ---
773
SHOW CREATE TABLE t1;
775
t1 CREATE TABLE `t1` (
776
`id` mediumint(9) NOT NULL,
777
`b1` bit(8) DEFAULT NULL,
778
`vc` varchar(255) DEFAULT NULL,
779
`bc` char(255) DEFAULT NULL,
780
`d` decimal(10,4) DEFAULT '0.0000',
781
`f` float DEFAULT '0',
782
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
783
`y` year(4) DEFAULT NULL,
784
`t` date DEFAULT NULL,
785
PRIMARY KEY (`id`,`total`)
786
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
787
/*!50100 PARTITION BY KEY ()
789
--- Make sure that our tables on slave are still right type ---
790
--- and that the alter statements replicated correctly ---
791
SHOW CREATE TABLE t1;
793
t1 CREATE TABLE `t1` (
794
`id` mediumint(9) NOT NULL,
795
`b1` bit(8) DEFAULT NULL,
796
`vc` varchar(255) DEFAULT NULL,
797
`bc` char(255) DEFAULT NULL,
798
`d` decimal(10,4) DEFAULT '0.0000',
799
`f` float DEFAULT '0',
800
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
801
`y` year(4) DEFAULT NULL,
802
`t` date DEFAULT NULL,
803
PRIMARY KEY (`id`,`total`)
804
) ENGINE=InnoDB DEFAULT CHARSET=latin1
805
/*!50100 PARTITION BY KEY ()
807
--- Perform basic operation on master ---
808
--- and ensure replicated correctly ---
809
"--- Insert into t1 --" as "";
810
--- Select from t1 on master ---
811
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
812
id hex(b1) vc bc d f total y t
813
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
814
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
815
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
816
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
817
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
818
--- Select from t1 on slave ---
819
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
820
id hex(b1) vc bc d f total y t
821
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
822
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
823
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
824
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
825
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
826
--- Update t1 on master --
827
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
828
--- Check the update on master ---
829
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
830
id hex(b1) vc bc d f total y t
831
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
832
--- Check Update on slave ---
833
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
834
id hex(b1) vc bc d f total y t
835
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
836
--- Remove a record from t1 on master ---
837
DELETE FROM t1 WHERE id = 42;
838
--- Show current count on master for t1 ---
839
SELECT COUNT(*) FROM t1;
842
--- Show current count on slave for t1 ---
843
SELECT COUNT(*) FROM t1;
847
--- Check that simple Alter statements are replicated correctly ---
848
ALTER TABLE t1 MODIFY vc TEXT;
849
--- Show the new improved table on the master ---
850
SHOW CREATE TABLE t1;
852
t1 CREATE TABLE `t1` (
853
`id` mediumint(9) NOT NULL,
854
`b1` bit(8) DEFAULT NULL,
856
`bc` char(255) DEFAULT NULL,
857
`d` decimal(10,4) DEFAULT '0.0000',
858
`f` float DEFAULT '0',
859
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
860
`y` year(4) DEFAULT NULL,
861
`t` date DEFAULT NULL,
862
PRIMARY KEY (`id`,`total`)
863
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
864
/*!50100 PARTITION BY KEY ()
866
--- Make sure that our tables on slave are still same engine ---
867
--- and that the alter statements replicated correctly ---
868
SHOW CREATE TABLE t1;
870
t1 CREATE TABLE `t1` (
871
`id` mediumint(9) NOT NULL,
872
`b1` bit(8) DEFAULT NULL,
874
`bc` char(255) DEFAULT NULL,
875
`d` decimal(10,4) DEFAULT '0.0000',
876
`f` float DEFAULT '0',
877
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
878
`y` year(4) DEFAULT NULL,
879
`t` date DEFAULT NULL,
880
PRIMARY KEY (`id`,`total`)
881
) ENGINE=InnoDB DEFAULT CHARSET=latin1
882
/*!50100 PARTITION BY KEY ()
884
--- Perform basic operation on master ---
885
--- and ensure replicated correctly ---
886
"--- Insert into t1 --" as "";
887
--- Select from t1 on master ---
888
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
889
id hex(b1) vc bc d f total y t
890
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
891
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
892
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
893
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
894
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
895
--- Select from t1 on slave ---
896
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
897
id hex(b1) vc bc d f total y t
898
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
899
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
900
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
901
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
902
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
903
--- Update t1 on master --
904
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
905
--- Check the update on master ---
906
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
907
id hex(b1) vc bc d f total y t
908
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
909
--- Check Update on slave ---
910
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
911
id hex(b1) vc bc d f total y t
912
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
913
--- Remove a record from t1 on master ---
914
DELETE FROM t1 WHERE id = 42;
915
--- Show current count on master for t1 ---
916
SELECT COUNT(*) FROM t1;
919
--- Show current count on slave for t1 ---
920
SELECT COUNT(*) FROM t1;
924
--- End test 5 key partition testing ---
926
DROP TABLE IF EXISTS t1;
927
drop table mysql.ndb_apply_status;