include/master-slave.inc [connection master] connection master; DROP TABLE IF EXISTS t1; SET @@BINLOG_FORMAT = ROW; **** Partition RANGE testing **** CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE) PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946), PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(`t`)) (PARTITION `p0` VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION `p3` VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION `p4` VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION `p5` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(`t`)) (PARTITION `p0` VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION `p3` VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION `p4` VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION `p5` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; ALTER TABLE t1 MODIFY vc TEXT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(`t`)) (PARTITION `p0` VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION `p3` VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION `p4` VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION `p5` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(`t`)) (PARTITION `p0` VALUES LESS THAN (1901) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (1946) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (1966) ENGINE = MyISAM, PARTITION `p3` VALUES LESS THAN (1986) ENGINE = MyISAM, PARTITION `p4` VALUES LESS THAN (2005) ENGINE = MyISAM, PARTITION `p5` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; DROP TABLE IF EXISTS t1; **** Partition LIST testing **** CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE) PARTITION BY LIST(id) (PARTITION p0 VALUES IN (2, 4), PARTITION p1 VALUES IN (42, 142), PARTITION p2 VALUES IN (412)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (`id`) (PARTITION `p0` VALUES IN (2,4) ENGINE = MyISAM, PARTITION `p1` VALUES IN (42,142) ENGINE = MyISAM, PARTITION `p2` VALUES IN (412) ENGINE = MyISAM) connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (`id`) (PARTITION `p0` VALUES IN (2,4) ENGINE = MyISAM, PARTITION `p1` VALUES IN (42,142) ENGINE = MyISAM, PARTITION `p2` VALUES IN (412) ENGINE = MyISAM) connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; ALTER TABLE t1 MODIFY vc TEXT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (`id`) (PARTITION `p0` VALUES IN (2,4) ENGINE = MyISAM, PARTITION `p1` VALUES IN (42,142) ENGINE = MyISAM, PARTITION `p2` VALUES IN (412) ENGINE = MyISAM) connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (`id`) (PARTITION `p0` VALUES IN (2,4) ENGINE = MyISAM, PARTITION `p1` VALUES IN (42,142) ENGINE = MyISAM, PARTITION `p2` VALUES IN (412) ENGINE = MyISAM) connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; DROP TABLE IF EXISTS t1; **** Partition HASH testing **** CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE) PARTITION BY HASH( YEAR(t) ) PARTITIONS 4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (year(`t`)) PARTITIONS 4 connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (year(`t`)) PARTITIONS 4 connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; ALTER TABLE t1 MODIFY vc TEXT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (year(`t`)) PARTITIONS 4 connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (year(`t`)) PARTITIONS 4 connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; DROP TABLE IF EXISTS t1; **** Partition by KEY **** CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE,PRIMARY KEY(id)) PARTITION BY KEY() PARTITIONS 4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned NOT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`,`total`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` varchar(255) DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned NOT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`,`total`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; connection master; ALTER TABLE t1 MODIFY vc TEXT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned NOT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`,`total`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection slave; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text DEFAULT NULL, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT 0.0000, `f` float DEFAULT 0, `total` bigint(20) unsigned NOT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, PRIMARY KEY (`id`,`total`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY () PARTITIONS 4 connection master; "--- Insert into t1 --" as ""; --- Select from t1 on master --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection slave; --- Select from t1 on slave --- select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id; id hex(b1) vc bc d f total y t 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 connection master; --- Update t1 on master -- UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412; --- Check the update on master --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection slave; --- Check Update on slave --- SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412; id hex(b1) vc bc d f total y t 412 0 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2006-02-22 connection master; --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 42; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection slave; --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 connection master; DELETE FROM t1; DROP TABLE IF EXISTS t1; include/rpl_end.inc