1include/master-slave.inc 2[connection master] 3use test; 4CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT, 5dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 6CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), 7fkid INT, filler VARCHAR(255), 8PRIMARY KEY(id)) 9ENGINE='Memory'; 10CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT, 11dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 12CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), 13fkid INT, filler VARCHAR(255), 14PRIMARY KEY(id)) 15ENGINE='Memory' 16PARTITION BY RANGE(id) 17(PARTITION pa100 values less than (100), 18PARTITION paMax values less than MAXVALUE); 19CREATE PROCEDURE test.proc_norm() 20BEGIN 21DECLARE ins_count INT DEFAULT 99; 22DECLARE cur_user VARCHAR(255); 23DECLARE local_uuid VARCHAR(255); 24SET cur_user= "current_user@localhost"; 25SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; 26WHILE ins_count > 0 DO 27# Must use local variables for statment based replication 28INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid, 29ins_count,'Non partitioned table! Going to test replication for MySQL'); 30SET ins_count = ins_count - 1; 31END WHILE; 32END| 33CREATE PROCEDURE test.proc_byrange() 34BEGIN 35DECLARE ins_count INT DEFAULT 200; 36DECLARE cur_user VARCHAR(255); 37DECLARE local_uuid VARCHAR(255); 38SET cur_user= "current_user@localhost"; 39SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; 40WHILE ins_count > 0 DO 41INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid, 42ins_count + 100,'Partitioned table! Going to test replication for MySQL'); 43SET ins_count = ins_count - 1; 44END WHILE; 45END| 46CALL test.proc_norm(); 47SELECT count(*) as "Master regular" FROM test.regular_tbl; 48Master regular 4999 50CALL test.proc_byrange(); 51SELECT count(*) as "Master byrange" FROM test.byrange_tbl; 52Master byrange 53200 54show create table test.byrange_tbl; 55Table Create Table 56byrange_tbl CREATE TABLE `byrange_tbl` ( 57 `id` int(11) NOT NULL AUTO_INCREMENT, 58 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 59 `user` char(255) DEFAULT NULL, 60 `uuidf` varbinary(255) DEFAULT NULL, 61 `fkid` int(11) DEFAULT NULL, 62 `filler` varchar(255) DEFAULT NULL, 63 PRIMARY KEY (`id`) 64) ENGINE=MEMORY AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 65 PARTITION BY RANGE (`id`) 66(PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MEMORY, 67 PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MEMORY) 68show create table test.regular_tbl; 69Table Create Table 70regular_tbl CREATE TABLE `regular_tbl` ( 71 `id` int(11) NOT NULL AUTO_INCREMENT, 72 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 73 `user` char(255) DEFAULT NULL, 74 `uuidf` varbinary(255) DEFAULT NULL, 75 `fkid` int(11) DEFAULT NULL, 76 `filler` varchar(255) DEFAULT NULL, 77 PRIMARY KEY (`id`) 78) ENGINE=MEMORY AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 79ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl; 80SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; 81id dt user uuidf fkid filler 8299 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL 8398 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL 84SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; 85id dt user uuidf fkid filler 86100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL 87101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL 88SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; 89id dt user uuidf fkid filler 9099 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL 9198 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL 92SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; 93id dt user uuidf fkid filler 941 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL 952 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL 96connection slave; 97connection slave; 98show create table test.byrange_tbl; 99Table Create Table 100byrange_tbl CREATE TABLE `byrange_tbl` ( 101 `id` int(11) NOT NULL AUTO_INCREMENT, 102 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 103 `user` char(255) DEFAULT NULL, 104 `uuidf` varbinary(255) DEFAULT NULL, 105 `fkid` int(11) DEFAULT NULL, 106 `filler` varchar(255) DEFAULT NULL, 107 PRIMARY KEY (`id`) 108) ENGINE=MEMORY AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 109 PARTITION BY RANGE (`id`) 110(PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MEMORY, 111 PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MEMORY) 112show create table test.regular_tbl; 113Table Create Table 114regular_tbl CREATE TABLE `regular_tbl` ( 115 `id` int(11) NOT NULL AUTO_INCREMENT, 116 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 117 `user` char(255) DEFAULT NULL, 118 `uuidf` varbinary(255) DEFAULT NULL, 119 `fkid` int(11) DEFAULT NULL, 120 `filler` varchar(255) DEFAULT NULL, 121 PRIMARY KEY (`id`) 122) ENGINE=MEMORY AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 123SELECT count(*) "Slave norm" FROM test.regular_tbl; 124Slave norm 12599 126SELECT count(*) "Slave byrange" FROM test.byrange_tbl; 127Slave byrange 128200 129SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; 130id dt user uuidf fkid filler 13199 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL 13298 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL 133SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; 134id dt user uuidf fkid filler 135100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL 136101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL 137SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; 138id dt user uuidf fkid filler 13999 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL 14098 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL 141SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; 142id dt user uuidf fkid filler 1431 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL 1442 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL 145connection master; 146DROP PROCEDURE test.proc_norm; 147DROP PROCEDURE test.proc_byrange; 148DROP TABLE test.regular_tbl; 149DROP TABLE test.byrange_tbl; 150include/rpl_end.inc 151