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