EVOLUTION-MANAGER

Edit File: rpl_partition_myisam.result

include/master-slave.inc [connection master] use test; CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT, dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), fkid INT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='MyISAM'; CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT, dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255), fkid INT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='MyISAM' PARTITION BY RANGE(id) (PARTITION pa100 values less than (100), PARTITION paMax values less than MAXVALUE); CREATE PROCEDURE test.proc_norm() BEGIN DECLARE ins_count INT DEFAULT 99; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); SET cur_user= "current_user@localhost"; SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; WHILE ins_count > 0 DO # Must use local variables for statment based replication INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid, ins_count,'Non partitioned table! Going to test replication for MySQL'); SET ins_count = ins_count - 1; END WHILE; END| CREATE PROCEDURE test.proc_byrange() BEGIN DECLARE ins_count INT DEFAULT 200; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); SET cur_user= "current_user@localhost"; SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c"; WHILE ins_count > 0 DO INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid, ins_count + 100,'Partitioned table! Going to test replication for MySQL'); SET ins_count = ins_count - 1; END WHILE; END| CALL test.proc_norm(); SELECT count(*) as "Master regular" FROM test.regular_tbl; Master regular 99 CALL test.proc_byrange(); SELECT count(*) as "Master byrange" FROM test.byrange_tbl; Master byrange 200 show create table test.byrange_tbl; Table Create Table byrange_tbl CREATE TABLE `byrange_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `user` char(255) DEFAULT NULL, `uuidf` varbinary(255) DEFAULT NULL, `fkid` int(11) DEFAULT NULL, `filler` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 PARTITION BY RANGE (`id`) (PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) show create table test.regular_tbl; Table Create Table regular_tbl CREATE TABLE `regular_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `user` char(255) DEFAULT NULL, `uuidf` varbinary(255) DEFAULT NULL, `fkid` int(11) DEFAULT NULL, `filler` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl; SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; id dt user uuidf fkid filler 99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL 98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; id dt user uuidf fkid filler 100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL 101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; id dt user uuidf fkid filler 99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL 98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; id dt user uuidf fkid filler 1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL 2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL connection slave; connection slave; show create table test.byrange_tbl; Table Create Table byrange_tbl CREATE TABLE `byrange_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `user` char(255) DEFAULT NULL, `uuidf` varbinary(255) DEFAULT NULL, `fkid` int(11) DEFAULT NULL, `filler` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 PARTITION BY RANGE (`id`) (PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) show create table test.regular_tbl; Table Create Table regular_tbl CREATE TABLE `regular_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `user` char(255) DEFAULT NULL, `uuidf` varbinary(255) DEFAULT NULL, `fkid` int(11) DEFAULT NULL, `filler` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 SELECT count(*) "Slave norm" FROM test.regular_tbl; Slave norm 99 SELECT count(*) "Slave byrange" FROM test.byrange_tbl; Slave byrange 200 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2; id dt user uuidf fkid filler 99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL 98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2; id dt user uuidf fkid filler 100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL 101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2; id dt user uuidf fkid filler 99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL 98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2; id dt user uuidf fkid filler 1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL 2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL connection master; DROP PROCEDURE test.proc_norm; DROP PROCEDURE test.proc_byrange; DROP TABLE test.regular_tbl; DROP TABLE test.byrange_tbl; include/rpl_end.inc