1--source include/have_partition.inc
2--source include/have_innodb.inc
3--source include/master-slave.inc
4
5--vertical_results
6
7let $engine_type= 'innodb';
8
9set @old_global_binlog_format=  @@global.binlog_format;
10set @old_session_binlog_format=  @@session.binlog_format;
11SET GLOBAL binlog_format = 'ROW';
12SET SESSION binlog_format = 'ROW';
13select @@global.binlog_format, @@session.binlog_format;
14--echo [on slave]
15connection slave;
16set @old_global_binlog_format=  @@global.binlog_format;
17set @old_session_binlog_format=  @@session.binlog_format;
18SET GLOBAL binlog_format = 'ROW';
19SET SESSION binlog_format = 'ROW';
20select @@global.binlog_format, @@session.binlog_format;
21# restart slave so that slave sql thread's binlog format is re-read
22# from @@global.binlog_format
23--source include/stop_slave.inc
24--source include/start_slave.inc
25--echo [on master]
26connection master;
27
28eval CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
29                           dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
30                           fkid MEDIUMINT, filler VARCHAR(255),
31                           PRIMARY KEY(id)) ENGINE=$engine_type;
32
33eval CREATE TABLE t2(id MEDIUMINT NOT NULL AUTO_INCREMENT,
34                           dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
35                           fkid MEDIUMINT, filler VARCHAR(255),
36                           PRIMARY KEY(id)) ENGINE=$engine_type
37				PARTITION BY KEY(id) partitions 5;
38
39eval CREATE TABLE t3(id MEDIUMINT NOT NULL AUTO_INCREMENT,
40                           dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
41                           fkid MEDIUMINT, filler VARCHAR(255),
42                           PRIMARY KEY(id)) ENGINE=$engine_type
43				PARTITION BY RANGE(id)
44                                SUBPARTITION BY hash(id) subpartitions 2
45				(PARTITION pa1 values less than (10),
46				 PARTITION pa2 values less than (20),
47				 PARTITION pa3 values less than (30),
48				 PARTITION pa4 values less than (40),
49				 PARTITION pa5 values less than (50),
50				 PARTITION pa6 values less than (60),
51				 PARTITION pa7 values less than (70),
52				 PARTITION pa8 values less than (80),
53				 PARTITION pa9 values less than (90),
54				 PARTITION pa10 values less than (100),
55				 PARTITION pa11 values less than MAXVALUE);
56
57######## Create SPs, Functions, Views and Triggers Section ##############
58
59delimiter |;
60CREATE PROCEDURE p1()
61BEGIN
62   DECLARE ins_count INT DEFAULT 1000;
63   DECLARE del_count INT;
64   DECLARE cur_user VARCHAR(255);
65   DECLARE local_uuid VARCHAR(255);
66   DECLARE local_time TIMESTAMP;
67
68   SET local_time= NOW();
69   SET cur_user= CURRENT_USER();
70   SET local_uuid= UUID();
71
72   WHILE ins_count > 0 DO
73     INSERT INTO t1 VALUES (NULL, NOW(), USER() , UUID(),
74                                   ins_count,'Going to test MBR for MySQL');
75     SET ins_count = ins_count - 1;
76   END WHILE;
77
78   SELECT MAX(id) FROM t1 INTO del_count;
79   WHILE del_count > 0 DO
80     DELETE FROM t1 WHERE id = del_count;
81     SET del_count = del_count - 2;
82   END WHILE;
83END|
84
85CREATE PROCEDURE p2()
86BEGIN
87   DECLARE ins_count INT DEFAULT 1000;
88   DECLARE del_count INT;
89   DECLARE cur_user VARCHAR(255);
90   DECLARE local_uuid VARCHAR(255);
91   DECLARE local_time TIMESTAMP;
92
93   SET local_time= NOW();
94   SET cur_user= CURRENT_USER();
95   SET local_uuid= UUID();
96
97   WHILE ins_count > 0 DO
98     INSERT INTO t2 VALUES (NULL, NOW(), USER() , UUID(),
99                                   ins_count,'Going to test MBR for MySQL');
100     SET ins_count = ins_count - 1;
101   END WHILE;
102
103   SELECT MAX(id) FROM t2 INTO del_count;
104   WHILE del_count > 0 DO
105     DELETE FROM t2 WHERE id = del_count;
106     SET del_count = del_count - 2;
107   END WHILE;
108END|
109
110CREATE PROCEDURE p3()
111BEGIN
112   DECLARE ins_count INT DEFAULT 1000;
113   DECLARE del_count INT;
114   DECLARE cur_user VARCHAR(255);
115   DECLARE local_uuid VARCHAR(255);
116   DECLARE local_time TIMESTAMP;
117
118   SET local_time= NOW();
119   SET cur_user = CURRENT_USER();
120   SET local_uuid=UUID();
121
122   WHILE ins_count > 0 DO
123     INSERT INTO t3 VALUES (NULL, NOW(), USER(), UUID(),
124                                    ins_count,'Going to test MBR for MySQL');
125     SET ins_count = ins_count - 1;
126   END WHILE;
127
128   SELECT MAX(id) FROM t3 INTO del_count;
129   WHILE del_count > 0 DO
130     DELETE FROM t3 WHERE id = del_count;
131   SET del_count = del_count - 2;
132   END WHILE;
133END|
134
135delimiter ;|
136
137############ Finish Setup Section ###################
138
139
140############ Test Section ###################
141
142CALL p1();
143SELECT count(*) as "Master regular" FROM t1;
144CALL p2();
145SELECT count(*) as "Master bykey" FROM t2;
146CALL p3();
147SELECT count(*) as "Master byrange" FROM t3;
148
149--sync_slave_with_master
150connection slave;
151show create table t3;
152--source include/check_slave_is_running.inc
153SELECT count(*) "Slave norm" FROM t1;
154SELECT count(*) "Slave bykey" FROM t2;
155SELECT count(*) "Slave byrange" FROM t3;
156
157connection master;
158set @@global.binlog_format=  @old_global_binlog_format;
159set @@session.binlog_format=  @old_session_binlog_format;
160DROP TABLE t1, t2, t3;
161DROP PROCEDURE p1;
162DROP PROCEDURE p2;
163DROP PROCEDURE p3;
164--echo [on slave]
165sync_slave_with_master;
166set @@global.binlog_format=  @old_global_binlog_format;
167set @@session.binlog_format=  @old_session_binlog_format;
168
169# End of 5.1 tests
170--source include/rpl_end.inc
171