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