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 @old_global_binlog_format= @@global.binlog_format;
8
set @old_session_binlog_format= @@session.binlog_format;
9
SET GLOBAL binlog_format = 'ROW';
10
SET SESSION binlog_format = 'ROW';
11
select @@global.binlog_format, @@session.binlog_format;
12
@@global.binlog_format ROW
13
@@session.binlog_format ROW
14
DROP TABLE IF EXISTS t1, t2, t3;
15
DROP PROCEDURE IF EXISTS p1;
16
DROP PROCEDURE IF EXISTS p2;
17
DROP PROCEDURE IF EXISTS p3;
18
CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
19
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
20
fkid MEDIUMINT, filler VARCHAR(255),
21
PRIMARY KEY(id)) ENGINE='innodb';
22
CREATE TABLE t2(id MEDIUMINT NOT NULL AUTO_INCREMENT,
23
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
24
fkid MEDIUMINT, filler VARCHAR(255),
25
PRIMARY KEY(id)) ENGINE='innodb'
26
PARTITION BY KEY(id) partitions 5;
27
CREATE TABLE t3(id MEDIUMINT NOT NULL AUTO_INCREMENT,
28
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
29
fkid MEDIUMINT, filler VARCHAR(255),
30
PRIMARY KEY(id)) ENGINE='innodb'
31
PARTITION BY RANGE(id)
32
SUBPARTITION BY hash(id) subpartitions 2
33
(PARTITION pa1 values less than (10),
34
PARTITION pa2 values less than (20),
35
PARTITION pa3 values less than (30),
36
PARTITION pa4 values less than (40),
37
PARTITION pa5 values less than (50),
38
PARTITION pa6 values less than (60),
39
PARTITION pa7 values less than (70),
40
PARTITION pa8 values less than (80),
41
PARTITION pa9 values less than (90),
42
PARTITION pa10 values less than (100),
43
PARTITION pa11 values less than MAXVALUE);
46
DECLARE ins_count INT DEFAULT 1000;
47
DECLARE del_count INT;
48
DECLARE cur_user VARCHAR(255);
49
DECLARE local_uuid VARCHAR(255);
50
DECLARE local_time TIMESTAMP;
51
SET local_time= NOW();
52
SET cur_user= CURRENT_USER();
53
SET local_uuid= UUID();
54
WHILE ins_count > 0 DO
55
INSERT INTO t1 VALUES (NULL, NOW(), USER() , UUID(),
56
ins_count,'Going to test MBR for MySQL');
57
SET ins_count = ins_count - 1;
59
SELECT MAX(id) FROM t1 INTO del_count;
60
WHILE del_count > 0 DO
61
DELETE FROM t1 WHERE id = del_count;
62
SET del_count = del_count - 2;
67
DECLARE ins_count INT DEFAULT 1000;
68
DECLARE del_count INT;
69
DECLARE cur_user VARCHAR(255);
70
DECLARE local_uuid VARCHAR(255);
71
DECLARE local_time TIMESTAMP;
72
SET local_time= NOW();
73
SET cur_user= CURRENT_USER();
74
SET local_uuid= UUID();
75
WHILE ins_count > 0 DO
76
INSERT INTO t2 VALUES (NULL, NOW(), USER() , UUID(),
77
ins_count,'Going to test MBR for MySQL');
78
SET ins_count = ins_count - 1;
80
SELECT MAX(id) FROM t2 INTO del_count;
81
WHILE del_count > 0 DO
82
DELETE FROM t2 WHERE id = del_count;
83
SET del_count = del_count - 2;
88
DECLARE ins_count INT DEFAULT 1000;
89
DECLARE del_count INT;
90
DECLARE cur_user VARCHAR(255);
91
DECLARE local_uuid VARCHAR(255);
92
DECLARE local_time TIMESTAMP;
93
SET local_time= NOW();
94
SET cur_user = CURRENT_USER();
95
SET local_uuid=UUID();
96
WHILE ins_count > 0 DO
97
INSERT INTO t3 VALUES (NULL, NOW(), USER(), UUID(),
98
ins_count,'Going to test MBR for MySQL');
99
SET ins_count = ins_count - 1;
101
SELECT MAX(id) FROM t3 INTO del_count;
102
WHILE del_count > 0 DO
103
DELETE FROM t3 WHERE id = del_count;
104
SET del_count = del_count - 2;
108
SELECT count(*) as "Master regular" FROM t1;
111
SELECT count(*) as "Master bykey" FROM t2;
114
SELECT count(*) as "Master byrange" FROM t3;
116
show create table t3;
118
Create Table CREATE TABLE `t3` (
119
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
120
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
121
`user` char(255) DEFAULT NULL,
123
`fkid` mediumint(9) DEFAULT NULL,
124
`filler` varchar(255) DEFAULT NULL,
126
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
127
/*!50100 PARTITION BY RANGE (id)
128
SUBPARTITION BY HASH (id)
130
(PARTITION pa1 VALUES LESS THAN (10) ENGINE = InnoDB,
131
PARTITION pa2 VALUES LESS THAN (20) ENGINE = InnoDB,
132
PARTITION pa3 VALUES LESS THAN (30) ENGINE = InnoDB,
133
PARTITION pa4 VALUES LESS THAN (40) ENGINE = InnoDB,
134
PARTITION pa5 VALUES LESS THAN (50) ENGINE = InnoDB,
135
PARTITION pa6 VALUES LESS THAN (60) ENGINE = InnoDB,
136
PARTITION pa7 VALUES LESS THAN (70) ENGINE = InnoDB,
137
PARTITION pa8 VALUES LESS THAN (80) ENGINE = InnoDB,
138
PARTITION pa9 VALUES LESS THAN (90) ENGINE = InnoDB,
139
PARTITION pa10 VALUES LESS THAN (100) ENGINE = InnoDB,
140
PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
143
Master_Host 127.0.0.1
145
Master_Port MASTER_MYPORT
147
Master_Log_File master-bin.000001
148
Read_Master_Log_Pos #
151
Relay_Master_Log_File master-bin.000001
153
Slave_SQL_Running Yes
157
Replicate_Ignore_Table
158
Replicate_Wild_Do_Table
159
Replicate_Wild_Ignore_Table
163
Exec_Master_Log_Pos #
168
Master_SSL_Allowed No
174
Seconds_Behind_Master #
175
Master_SSL_Verify_Server_Cert No
180
SELECT count(*) "Slave norm" FROM t1;
182
SELECT count(*) "Slave bykey" FROM t2;
184
SELECT count(*) "Slave byrange" FROM t3;
186
set @@global.binlog_format= @old_global_binlog_format;
187
set @@session.binlog_format= @old_session_binlog_format;
188
DROP TABLE t1, t2, t3;
189
DROP PROCEDURE IF EXISTS p1;
190
DROP PROCEDURE IF EXISTS p2;
191
DROP PROCEDURE IF EXISTS p3;