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