include/master-slave.inc
[connection master]
use test;
CREATE TABLE test.regular_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON 
UPDATE CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb';
CREATE TABLE test.bykey_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb'
PARTITION BY KEY(id) partitions 5;
CREATE TABLE test.byrange_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb'
PARTITION BY RANGE(id)
SUBPARTITION BY hash(id) subpartitions 2
(PARTITION pa1 values less than (10),
PARTITION pa2 values less than (20),
PARTITION pa3 values less than (30),
PARTITION pa4 values less than (40),
PARTITION pa5 values less than (50),
PARTITION pa6 values less than (60),
PARTITION pa7 values less than (70),
PARTITION pa8 values less than (80),
PARTITION pa9 values less than (90),
PARTITION pa10 values less than (100),
PARTITION pa11 values less than MAXVALUE);
CREATE PROCEDURE test.proc_norm()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user= CURRENT_USER();
SET local_uuid= UUID();
WHILE ins_count > 0 DO
INSERT INTO test.regular_tbl VALUES (NULL, NOW(), USER() , UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) FROM test.regular_tbl INTO del_count;
WHILE del_count > 0 DO
DELETE FROM test.regular_tbl WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
Warnings:
Level	Warning
Code	1287
Message	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
CREATE PROCEDURE test.proc_bykey()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user= CURRENT_USER();
SET local_uuid= UUID();
WHILE ins_count > 0 DO
INSERT INTO test.bykey_tbl VALUES (NULL, NOW(), USER() , UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) FROM test.bykey_tbl INTO del_count;
WHILE del_count > 0 DO
DELETE FROM test.bykey_tbl WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
Warnings:
Level	Warning
Code	1287
Message	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
CREATE PROCEDURE test.proc_byrange()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user = CURRENT_USER();
SET local_uuid=UUID();
WHILE ins_count > 0 DO
INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), USER(), UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) FROM test.byrange_tbl INTO del_count;
WHILE del_count > 0 DO
DELETE FROM test.byrange_tbl WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
Warnings:
Level	Warning
Code	1287
Message	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
begin;
CALL test.proc_norm();
commit;
SELECT count(*) as "Master regular" FROM test.regular_tbl;
Master regular	500
begin;
CALL test.proc_bykey();
commit;
SELECT count(*) as "Master bykey" FROM test.bykey_tbl;
Master bykey	500
begin;
CALL test.proc_byrange();
commit;
SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
Master byrange	500
connection slave;
connection slave;
show create table test.byrange_tbl;
Table	byrange_tbl
Create Table	CREATE TABLE `byrange_tbl` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user` char(255) DEFAULT NULL,
  `uuidf` longblob DEFAULT NULL,
  `fkid` mediumint(9) DEFAULT NULL,
  `filler` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
SUBPARTITIONS 2
(PARTITION `pa1` VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION `pa2` VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION `pa3` VALUES LESS THAN (30) ENGINE = InnoDB,
 PARTITION `pa4` VALUES LESS THAN (40) ENGINE = InnoDB,
 PARTITION `pa5` VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION `pa6` VALUES LESS THAN (60) ENGINE = InnoDB,
 PARTITION `pa7` VALUES LESS THAN (70) ENGINE = InnoDB,
 PARTITION `pa8` VALUES LESS THAN (80) ENGINE = InnoDB,
 PARTITION `pa9` VALUES LESS THAN (90) ENGINE = InnoDB,
 PARTITION `pa10` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `pa11` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SELECT count(*) "Slave norm" FROM test.regular_tbl;
Slave norm	500
SELECT count(*) "Slave bykey" FROM test.bykey_tbl;
Slave bykey	500
SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
Slave byrange	500
connection master;
DROP PROCEDURE test.proc_norm;
DROP PROCEDURE test.proc_bykey;
DROP PROCEDURE test.proc_byrange;
DROP TABLE test.regular_tbl;
DROP TABLE test.bykey_tbl;
DROP TABLE test.byrange_tbl;
include/rpl_end.inc