1######## Create Table Section ######### 2use test; 3 4#dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, 5eval CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT, 6 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 7 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), 8 fkid INT, filler VARCHAR(255), 9 PRIMARY KEY(id)) 10 ENGINE=$engine_type; 11 12eval CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT, 13 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 14 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), 15 fkid INT, filler VARCHAR(255), 16 PRIMARY KEY(id)) 17 ENGINE=$engine_type 18 PARTITION BY RANGE(id) 19 (PARTITION pa100 values less than (100), 20 PARTITION paMax values less than MAXVALUE); 21 22######## Create SPs, Functions, Views and Triggers Section ############## 23 24delimiter |; 25CREATE PROCEDURE test.proc_norm() 26BEGIN 27 DECLARE ins_count INT DEFAULT 99; 28 DECLARE cur_user VARCHAR(255); 29 DECLARE local_uuid VARCHAR(255); 30 31 SET cur_user= "current_user@localhost"; 32 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; 33 34 WHILE ins_count > 0 DO 35 # Must use local variables for statment based replication 36 INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid, 37 ins_count,'Non partitioned table! Going to test replication for MySQL'); 38 SET ins_count = ins_count - 1; 39 END WHILE; 40 41END| 42 43CREATE PROCEDURE test.proc_byrange() 44BEGIN 45 DECLARE ins_count INT DEFAULT 200; 46 DECLARE cur_user VARCHAR(255); 47 DECLARE local_uuid VARCHAR(255); 48 49 SET cur_user= "current_user@localhost"; 50 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; 51 52 WHILE ins_count > 0 DO 53 INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid, 54 ins_count + 100,'Partitioned table! Going to test replication for MySQL'); 55 SET ins_count = ins_count - 1; 56 END WHILE; 57 58END| 59 60delimiter ;| 61 62############ Finish Setup Section ################### 63 64 65############ Test Section ################### 66 67CALL test.proc_norm(); 68SELECT count(*) as "Master regular" FROM test.regular_tbl; 69CALL test.proc_byrange(); 70SELECT count(*) as "Master byrange" FROM test.byrange_tbl; 71show create table test.byrange_tbl; 72show create table test.regular_tbl; 73ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl; 74--replace_column 2 date-time 3 USER 4 UUID 75SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; 76--replace_column 2 date-time 3 USER 4 UUID 77SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; 78--replace_column 2 date-time 3 USER 4 UUID 79SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; 80--replace_column 2 date-time 3 USER 4 UUID 81SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; 82 83--source include/sync_slave_sql_with_master.inc 84connection slave; 85show create table test.byrange_tbl; 86show create table test.regular_tbl; 87SELECT count(*) "Slave norm" FROM test.regular_tbl; 88SELECT count(*) "Slave byrange" FROM test.byrange_tbl; 89--replace_column 2 date-time 3 USER 4 UUID 90SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; 91--replace_column 2 date-time 3 USER 4 UUID 92SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; 93--replace_column 2 date-time 3 USER 4 UUID 94SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; 95--replace_column 2 date-time 3 USER 4 UUID 96SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; 97 98###### CLEAN UP SECTION ############## 99 100connection master; 101DROP PROCEDURE test.proc_norm; 102DROP PROCEDURE test.proc_byrange; 103DROP TABLE test.regular_tbl; 104DROP TABLE test.byrange_tbl; 105