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
--- Doing pre test cleanup ---
8
DROP TABLE IF EXISTS t1;
9
CREATE LOGFILE GROUP lg1
10
ADD UNDOFILE 'undofile.dat'
14
ALTER LOGFILE GROUP lg1
15
ADD UNDOFILE 'undofile02.dat'
19
ADD DATAFILE 'datafile.dat'
24
ADD DATAFILE 'datafile02.dat'
27
--- Start test 2 partition RANGE testing --
29
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
30
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
31
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
33
TABLESPACE ts1 STORAGE DISK
35
PARTITION BY RANGE (YEAR(t))
36
(PARTITION p0 VALUES LESS THAN (1901),
37
PARTITION p1 VALUES LESS THAN (1946),
38
PARTITION p2 VALUES LESS THAN (1966),
39
PARTITION p3 VALUES LESS THAN (1986),
40
PARTITION p4 VALUES LESS THAN (2005),
41
PARTITION p5 VALUES LESS THAN MAXVALUE);
42
--- Show table on master ---
45
t1 CREATE TABLE `t1` (
46
`id` mediumint(9) NOT NULL,
47
`b1` bit(8) DEFAULT NULL,
48
`vc` varchar(63) DEFAULT NULL,
49
`bc` char(63) DEFAULT NULL,
50
`d` decimal(10,4) DEFAULT '0.0000',
51
`f` float DEFAULT '0',
52
`total` bigint(20) unsigned DEFAULT NULL,
53
`y` year(4) DEFAULT NULL,
55
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster, PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster, PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster, PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster, PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster)
56
--- Show table on slave --
59
t1 CREATE TABLE `t1` (
60
`id` mediumint(9) NOT NULL,
61
`b1` bit(8) DEFAULT NULL,
62
`vc` varchar(63) DEFAULT NULL,
63
`bc` char(63) DEFAULT NULL,
64
`d` decimal(10,4) DEFAULT '0.0000',
65
`f` float DEFAULT '0',
66
`total` bigint(20) unsigned DEFAULT NULL,
67
`y` year(4) DEFAULT NULL,
69
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster, PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster, PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster, PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster, PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster)
70
--- Perform basic operation on master ---
71
--- and ensure replicated correctly ---
72
"--- Insert into t1 --" as "";
73
--- Select from t1 on master ---
74
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
75
id hex(b1) vc bc d f total y t
76
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
77
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
78
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
79
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
80
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
81
--- Select from t1 on slave ---
82
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
83
id hex(b1) vc bc d f total y t
84
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
85
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
86
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
87
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
88
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
89
--- Update t1 on master --
90
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
91
--- Check the update on master ---
92
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
93
id hex(b1) vc bc d f total y t
94
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
95
--- Check Update on slave ---
96
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
97
id hex(b1) vc bc d f total y t
98
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
99
--- Remove a record from t1 on master ---
100
DELETE FROM t1 WHERE id = 42;
101
--- Show current count on master for t1 ---
102
SELECT COUNT(*) FROM t1;
105
--- Show current count on slave for t1 ---
106
SELECT COUNT(*) FROM t1;
110
--- Check that simple Alter statements are replicated correctly ---
111
ALTER TABLE t1 MODIFY vc VARCHAR(255);
112
--- Show the new improved table on the master ---
113
SHOW CREATE TABLE t1;
115
t1 CREATE TABLE `t1` (
116
`id` mediumint(9) NOT NULL,
117
`b1` bit(8) DEFAULT NULL,
118
`vc` varchar(255) DEFAULT NULL,
119
`bc` char(63) DEFAULT NULL,
120
`d` decimal(10,4) DEFAULT '0.0000',
121
`f` float DEFAULT '0',
122
`total` bigint(20) unsigned DEFAULT NULL,
123
`y` year(4) DEFAULT NULL,
124
`t` date DEFAULT NULL
125
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster, PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster, PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster, PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster, PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster)
126
--- Make sure that our tables on slave are still same engine ---
127
--- and that the alter statements replicated correctly ---
128
SHOW CREATE TABLE t1;
130
t1 CREATE TABLE `t1` (
131
`id` mediumint(9) NOT NULL,
132
`b1` bit(8) DEFAULT NULL,
133
`vc` varchar(255) DEFAULT NULL,
134
`bc` char(63) DEFAULT NULL,
135
`d` decimal(10,4) DEFAULT '0.0000',
136
`f` float DEFAULT '0',
137
`total` bigint(20) unsigned DEFAULT NULL,
138
`y` year(4) DEFAULT NULL,
139
`t` date DEFAULT NULL
140
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster, PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster, PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster, PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster, PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster)
141
--- Perform basic operation on master ---
142
--- and ensure replicated correctly ---
143
"--- Insert into t1 --" as "";
144
--- Select from t1 on master ---
145
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
146
id hex(b1) vc bc d f total y t
147
2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
148
4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
149
42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
150
142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
151
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
152
--- Select from t1 on slave ---
153
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
154
id hex(b1) vc bc d f total y t
155
2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14
156
4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14
157
42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14
158
142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14
159
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14
160
--- Update t1 on master --
161
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
162
--- Check the update on master ---
163
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
164
id hex(b1) vc bc d f total y t
165
412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
166
--- Check Update on slave ---
167
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
168
id hex(b1) vc bc d f total y t
169
412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
170
--- Remove a record from t1 on master ---
171
DELETE FROM t1 WHERE id = 42;
172
--- Show current count on master for t1 ---
173
SELECT COUNT(*) FROM t1;
176
--- Show current count on slave for t1 ---
177
SELECT COUNT(*) FROM t1;
181
--- End test 2 partition RANGE testing ---
183
DROP TABLE IF EXISTS t1;
184
--- Start test 3 partition LIST testing ---
186
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
187
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
188
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
190
TABLESPACE ts1 STORAGE DISK
192
PARTITION BY LIST(id)
193
(PARTITION p0 VALUES IN (2, 4),
194
PARTITION p1 VALUES IN (42, 142));
195
--- Test 3 Alter to add partition ---
196
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
197
--- Show table on master ---
198
SHOW CREATE TABLE t1;
200
t1 CREATE TABLE `t1` (
201
`id` mediumint(9) NOT NULL,
202
`b1` bit(8) DEFAULT NULL,
203
`vc` varchar(63) DEFAULT NULL,
204
`bc` char(63) DEFAULT NULL,
205
`d` decimal(10,4) DEFAULT '0.0000',
206
`f` float DEFAULT '0',
207
`total` bigint(20) unsigned DEFAULT NULL,
208
`y` year(4) DEFAULT NULL,
209
`t` date DEFAULT NULL
210
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster, PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster, PARTITION p2 VALUES IN (412) ENGINE = ndbcluster)
211
--- Show table on slave ---
212
SHOW CREATE TABLE t1;
214
t1 CREATE TABLE `t1` (
215
`id` mediumint(9) NOT NULL,
216
`b1` bit(8) DEFAULT NULL,
217
`vc` varchar(63) DEFAULT NULL,
218
`bc` char(63) DEFAULT NULL,
219
`d` decimal(10,4) DEFAULT '0.0000',
220
`f` float DEFAULT '0',
221
`total` bigint(20) unsigned DEFAULT NULL,
222
`y` year(4) DEFAULT NULL,
223
`t` date DEFAULT NULL
224
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster, PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster, PARTITION p2 VALUES IN (412) ENGINE = ndbcluster)
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 MODIFY vc VARCHAR(255);
267
--- Show the new improved table on the master ---
268
SHOW CREATE TABLE t1;
270
t1 CREATE TABLE `t1` (
271
`id` mediumint(9) NOT NULL,
272
`b1` bit(8) DEFAULT NULL,
273
`vc` varchar(255) DEFAULT NULL,
274
`bc` char(63) DEFAULT NULL,
275
`d` decimal(10,4) DEFAULT '0.0000',
276
`f` float DEFAULT '0',
277
`total` bigint(20) unsigned DEFAULT NULL,
278
`y` year(4) DEFAULT NULL,
279
`t` date DEFAULT NULL
280
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster, PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster, PARTITION p2 VALUES IN (412) ENGINE = ndbcluster)
281
--- Make sure that our tables on slave are still same engine ---
282
--- and that the alter statements replicated correctly ---
283
SHOW CREATE TABLE t1;
285
t1 CREATE TABLE `t1` (
286
`id` mediumint(9) NOT NULL,
287
`b1` bit(8) DEFAULT NULL,
288
`vc` varchar(255) DEFAULT NULL,
289
`bc` char(63) DEFAULT NULL,
290
`d` decimal(10,4) DEFAULT '0.0000',
291
`f` float DEFAULT '0',
292
`total` bigint(20) unsigned DEFAULT NULL,
293
`y` year(4) DEFAULT NULL,
294
`t` date DEFAULT NULL
295
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY LIST (id) (PARTITION p0 VALUES IN (2,4) ENGINE = ndbcluster, PARTITION p1 VALUES IN (42,142) ENGINE = ndbcluster, PARTITION p2 VALUES IN (412) ENGINE = ndbcluster)
296
--- Perform basic operation on master ---
297
--- and ensure replicated correctly ---
298
"--- Insert into t1 --" as "";
299
--- Select from t1 on master ---
300
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
301
id hex(b1) vc bc d f total y t
302
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
303
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
304
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
305
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
306
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
307
--- Select from t1 on slave ---
308
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
309
id hex(b1) vc bc d f total y t
310
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
311
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
312
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
313
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
314
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
315
--- Update t1 on master --
316
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
317
--- Check the update on master ---
318
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
319
id hex(b1) vc bc d f total y t
320
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
321
--- Check Update on slave ---
322
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
323
id hex(b1) vc bc d f total y t
324
412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
325
--- Remove a record from t1 on master ---
326
DELETE FROM t1 WHERE id = 42;
327
--- Show current count on master for t1 ---
328
SELECT COUNT(*) FROM t1;
331
--- Show current count on slave for t1 ---
332
SELECT COUNT(*) FROM t1;
336
--- End test 3 partition LIST testing ---
338
DROP TABLE IF EXISTS t1;
339
--- Start test 4 partition HASH testing ---
341
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
342
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
343
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
345
TABLESPACE ts1 STORAGE DISK
347
PARTITION BY HASH( YEAR(t) )
349
--- show that tables have been created correctly ---
350
SHOW CREATE TABLE t1;
352
t1 CREATE TABLE `t1` (
353
`id` mediumint(9) NOT NULL,
354
`b1` bit(8) DEFAULT NULL,
355
`vc` varchar(63) DEFAULT NULL,
356
`bc` char(63) DEFAULT NULL,
357
`d` decimal(10,4) DEFAULT '0.0000',
358
`f` float DEFAULT '0',
359
`total` bigint(20) unsigned DEFAULT NULL,
360
`y` year(4) DEFAULT NULL,
361
`t` date DEFAULT NULL
362
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4
363
SHOW CREATE TABLE t1;
365
t1 CREATE TABLE `t1` (
366
`id` mediumint(9) NOT NULL,
367
`b1` bit(8) DEFAULT NULL,
368
`vc` varchar(63) DEFAULT NULL,
369
`bc` char(63) DEFAULT NULL,
370
`d` decimal(10,4) DEFAULT '0.0000',
371
`f` float DEFAULT '0',
372
`total` bigint(20) unsigned DEFAULT NULL,
373
`y` year(4) DEFAULT NULL,
374
`t` date DEFAULT NULL
375
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4
376
--- Perform basic operation on master ---
377
--- and ensure replicated correctly ---
378
"--- Insert into t1 --" as "";
379
--- Select from t1 on master ---
380
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
381
id hex(b1) vc bc d f total y t
382
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
383
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
384
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
385
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
386
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
387
--- Select from t1 on slave ---
388
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
389
id hex(b1) vc bc d f total y t
390
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
391
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
392
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
393
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
394
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
395
--- Update t1 on master --
396
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
397
--- Check the update on master ---
398
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
399
id hex(b1) vc bc d f total y t
400
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
401
--- Check Update on slave ---
402
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
403
id hex(b1) vc bc d f total y t
404
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
405
--- Remove a record from t1 on master ---
406
DELETE FROM t1 WHERE id = 42;
407
--- Show current count on master for t1 ---
408
SELECT COUNT(*) FROM t1;
411
--- Show current count on slave for t1 ---
412
SELECT COUNT(*) FROM t1;
416
--- Check that simple Alter statements are replicated correctly ---
417
ALTER TABLE t1 MODIFY vc VARCHAR(255);
418
--- Show the new improved table on the master ---
419
SHOW CREATE TABLE t1;
421
t1 CREATE TABLE `t1` (
422
`id` mediumint(9) NOT NULL,
423
`b1` bit(8) DEFAULT NULL,
424
`vc` varchar(255) DEFAULT NULL,
425
`bc` char(63) DEFAULT NULL,
426
`d` decimal(10,4) DEFAULT '0.0000',
427
`f` float DEFAULT '0',
428
`total` bigint(20) unsigned DEFAULT NULL,
429
`y` year(4) DEFAULT NULL,
430
`t` date DEFAULT NULL
431
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4
432
--- Make sure that our tables on slave are still same engine ---
433
--- and that the alter statements replicated correctly ---
434
SHOW CREATE TABLE t1;
436
t1 CREATE TABLE `t1` (
437
`id` mediumint(9) NOT NULL,
438
`b1` bit(8) DEFAULT NULL,
439
`vc` varchar(255) DEFAULT NULL,
440
`bc` char(63) DEFAULT NULL,
441
`d` decimal(10,4) DEFAULT '0.0000',
442
`f` float DEFAULT '0',
443
`total` bigint(20) unsigned DEFAULT NULL,
444
`y` year(4) DEFAULT NULL,
445
`t` date DEFAULT NULL
446
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH ( YEAR(t)) PARTITIONS 4
447
--- Perform basic operation on master ---
448
--- and ensure replicated correctly ---
449
"--- Insert into t1 --" as "";
450
--- Select from t1 on master ---
451
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
452
id hex(b1) vc bc d f total y t
453
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
454
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
455
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
456
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
457
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
458
--- Select from t1 on slave ---
459
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
460
id hex(b1) vc bc d f total y t
461
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
462
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
463
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
464
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
465
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
466
--- Update t1 on master --
467
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
468
--- Check the update on master ---
469
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
470
id hex(b1) vc bc d f total y t
471
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
472
--- Check Update on slave ---
473
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
474
id hex(b1) vc bc d f total y t
475
412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22
476
--- Remove a record from t1 on master ---
477
DELETE FROM t1 WHERE id = 42;
478
--- Show current count on master for t1 ---
479
SELECT COUNT(*) FROM t1;
482
--- Show current count on slave for t1 ---
483
SELECT COUNT(*) FROM t1;
487
--- End test 4 partition HASH testing ---
489
DROP TABLE IF EXISTS t1;
490
--- Start test 5 partition by key testing ---
491
--- Create Table Section ---
492
CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
493
bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
494
f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
495
y YEAR, t DATE,PRIMARY KEY(id))
496
TABLESPACE ts1 STORAGE DISK
500
--- Show that tables on master are ndbcluster tables ---
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(63) DEFAULT NULL,
507
`bc` char(63) 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
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
515
--- Show that tables on slave ---
516
SHOW CREATE TABLE t1;
518
t1 CREATE TABLE `t1` (
519
`id` mediumint(9) NOT NULL,
520
`b1` bit(8) DEFAULT NULL,
521
`vc` varchar(63) DEFAULT NULL,
522
`bc` char(63) DEFAULT NULL,
523
`d` decimal(10,4) DEFAULT '0.0000',
524
`f` float DEFAULT '0',
525
`total` bigint(20) unsigned DEFAULT NULL,
526
`y` year(4) DEFAULT NULL,
527
`t` date DEFAULT NULL,
529
) TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
530
--- Perform basic operation on master ---
531
--- and ensure replicated correctly ---
532
"--- Insert into t1 --" as "";
533
--- Select from t1 on master ---
534
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
535
id hex(b1) vc bc d f total y t
536
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
537
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
538
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
539
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
540
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
541
--- Select from t1 on slave ---
542
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
543
id hex(b1) vc bc d f total y t
544
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
545
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
546
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
547
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
548
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
549
--- Update t1 on master --
550
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
551
--- Check the update on master ---
552
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
553
id hex(b1) vc bc d f total y t
554
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
555
--- Check Update on slave ---
556
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
557
id hex(b1) vc bc d f total y t
558
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
559
--- Remove a record from t1 on master ---
560
DELETE FROM t1 WHERE id = 42;
561
--- Show current count on master for t1 ---
562
SELECT COUNT(*) FROM t1;
565
--- Show current count on slave for t1 ---
566
SELECT COUNT(*) FROM t1;
570
--- Check that simple Alter statements are replicated correctly ---
571
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
572
--- Show the new improved table on the master ---
573
SHOW CREATE TABLE t1;
575
t1 CREATE TABLE `t1` (
576
`id` mediumint(9) NOT NULL,
577
`b1` bit(8) DEFAULT NULL,
578
`vc` varchar(63) DEFAULT NULL,
579
`bc` char(63) DEFAULT NULL,
580
`d` decimal(10,4) DEFAULT '0.0000',
581
`f` float DEFAULT '0',
582
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
583
`y` year(4) DEFAULT NULL,
584
`t` date DEFAULT NULL,
585
PRIMARY KEY (`id`,`total`)
586
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
587
--- Make sure that our tables on slave are still right type ---
588
--- and that the alter statements replicated correctly ---
589
SHOW CREATE TABLE t1;
591
t1 CREATE TABLE `t1` (
592
`id` mediumint(9) NOT NULL,
593
`b1` bit(8) DEFAULT NULL,
594
`vc` varchar(63) DEFAULT NULL,
595
`bc` char(63) DEFAULT NULL,
596
`d` decimal(10,4) DEFAULT '0.0000',
597
`f` float DEFAULT '0',
598
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
599
`y` year(4) DEFAULT NULL,
600
`t` date DEFAULT NULL,
601
PRIMARY KEY (`id`,`total`)
602
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
603
--- Perform basic operation on master ---
604
--- and ensure replicated correctly ---
605
"--- Insert into t1 --" as "";
606
--- Select from t1 on master ---
607
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
608
id hex(b1) vc bc d f total y t
609
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
610
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
611
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
612
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
613
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
614
--- Select from t1 on slave ---
615
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
616
id hex(b1) vc bc d f total y t
617
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
618
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
619
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
620
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
621
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
622
--- Update t1 on master --
623
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
624
--- Check the update on master ---
625
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
626
id hex(b1) vc bc d f total y t
627
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
628
--- Check Update on slave ---
629
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
630
id hex(b1) vc bc d f total y t
631
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
632
--- Remove a record from t1 on master ---
633
DELETE FROM t1 WHERE id = 42;
634
--- Show current count on master for t1 ---
635
SELECT COUNT(*) FROM t1;
638
--- Show current count on slave for t1 ---
639
SELECT COUNT(*) FROM t1;
643
--- Check that simple Alter statements are replicated correctly ---
644
ALTER TABLE t1 MODIFY vc VARCHAR(255);
645
--- Show the new improved table on the master ---
646
SHOW CREATE TABLE t1;
648
t1 CREATE TABLE `t1` (
649
`id` mediumint(9) NOT NULL,
650
`b1` bit(8) DEFAULT NULL,
651
`vc` varchar(255) DEFAULT NULL,
652
`bc` char(63) DEFAULT NULL,
653
`d` decimal(10,4) DEFAULT '0.0000',
654
`f` float DEFAULT '0',
655
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
656
`y` year(4) DEFAULT NULL,
657
`t` date DEFAULT NULL,
658
PRIMARY KEY (`id`,`total`)
659
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
660
--- Make sure that our tables on slave are still same engine ---
661
--- and that the alter statements replicated correctly ---
662
SHOW CREATE TABLE t1;
664
t1 CREATE TABLE `t1` (
665
`id` mediumint(9) NOT NULL,
666
`b1` bit(8) DEFAULT NULL,
667
`vc` varchar(255) DEFAULT NULL,
668
`bc` char(63) DEFAULT NULL,
669
`d` decimal(10,4) DEFAULT '0.0000',
670
`f` float DEFAULT '0',
671
`total` bigint(20) unsigned NOT NULL DEFAULT '0',
672
`y` year(4) DEFAULT NULL,
673
`t` date DEFAULT NULL,
674
PRIMARY KEY (`id`,`total`)
675
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4
676
--- Perform basic operation on master ---
677
--- and ensure replicated correctly ---
678
"--- Insert into t1 --" as "";
679
--- Select from t1 on master ---
680
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
681
id hex(b1) vc bc d f total y t
682
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
683
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
684
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
685
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
686
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
687
--- Select from t1 on slave ---
688
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
689
id hex(b1) vc bc d f total y t
690
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
691
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
692
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
693
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
694
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
695
--- Update t1 on master --
696
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
697
--- Check the update on master ---
698
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
699
id hex(b1) vc bc d f total y t
700
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
701
--- Check Update on slave ---
702
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
703
id hex(b1) vc bc d f total y t
704
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
705
--- Remove a record from t1 on master ---
706
DELETE FROM t1 WHERE id = 42;
707
--- Show current count on master for t1 ---
708
SELECT COUNT(*) FROM t1;
711
--- Show current count on slave for t1 ---
712
SELECT COUNT(*) FROM t1;
716
--- End test 5 key partition testing ---
718
DROP TABLE IF EXISTS t1;
720
drop datafile 'datafile.dat'
723
drop datafile 'datafile02.dat'
725
DROP TABLESPACE ts1 ENGINE=NDB;
726
DROP LOGFILE GROUP lg1 ENGINE=NDB;