include/master-slave.inc [connection master] SET storage_engine=ndb; === NDB -> NDB === --- Doing pre test cleanup --- DROP TABLE IF EXISTS t1; --- Create Table Section --- CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 INT, 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)); --- Show table on master --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) 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=ndbcluster DEFAULT CHARSET=latin1 --- Show table on slave --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) 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=ndbcluster DEFAULT CHARSET=latin1 STOP SLAVE; RESET SLAVE; RESET MASTER; START SLAVE; --- Populate t1 with data --- --- Select from t1 on master --- select * from t1 order by id; id 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 --- Select from t1 on slave --- select * from t1 order by id; id 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 --- Perform basic operation on master --- --- and ensure replicated correctly --- --- Update t1 on master -- UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" WHERE id < 100 ORDER BY id; --- Check the update on master --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 --- Check Update on slave --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 412; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 TRUNCATE TABLE t1; --- Check that simple Alter statements are replicated correctly -- ALTER TABLE t1 DROP PRIMARY KEY; ALTER TABLE t1 MODIFY vc char(32); --- Show the new improved table on the master --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) DEFAULT NULL, `vc` char(32) 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=ndbcluster DEFAULT CHARSET=latin1 --- Make sure that our tables on slave are still same engine --- --- and that the alter statements replicated correctly --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) DEFAULT NULL, `vc` char(32) 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=ndbcluster DEFAULT CHARSET=latin1 STOP SLAVE; RESET SLAVE; RESET MASTER; START SLAVE; --- Populate t1 with data --- --- Select from t1 on master --- select * from t1 order by id; id b1 vc bc d f total y t 2 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 --- Select from t1 on slave --- select * from t1 order by id; id b1 vc bc d f total y t 2 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 42 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 --- Perform basic operation on master --- --- and ensure replicated correctly --- --- Update t1 on master -- UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" WHERE id < 100 ORDER BY id; --- Check the update on master --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 --- Check Update on slave --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 412; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 TRUNCATE TABLE t1; --- Check that replication works when slave has more columns than master ALTER TABLE t1 ADD PRIMARY KEY(id,total); ALTER TABLE t1 MODIFY vc TEXT; INSERT INTO t1 VALUES(3,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer', 654321.4321,15.21,0,1965,"1905-11-14"); INSERT INTO t1 VALUES(20,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer', 654321.4321,15.21,0,1965,"1965-11-14"); INSERT INTO t1 VALUES(50,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer', 654321.4321,15.21,0,1965,"1985-11-14"); --- Add columns on slave --- ALTER TABLE t1 ADD (u int, v char(16) default 'default'); UPDATE t1 SET u=7 WHERE id < 50; UPDATE t1 SET v='explicit' WHERE id >10; --- Show changed table on slave --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) DEFAULT NULL, `vc` text, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT '0.0000', `f` float DEFAULT '0', `total` bigint(20) unsigned NOT NULL DEFAULT '0', `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL, `u` int(11) DEFAULT NULL, `v` char(16) DEFAULT 'default', PRIMARY KEY (`id`,`total`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY id; id b1 vc bc d f total y t u v 3 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 7 default 20 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 7 explicit 50 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 NULL explicit STOP SLAVE; RESET SLAVE; RESET MASTER; START SLAVE; --- Populate t1 with data --- --- Select from t1 on master --- select * from t1 order by id; id 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 3 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-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 20 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-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 50 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-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 --- Select from t1 on slave --- select * from t1 order by id; id b1 vc bc d f total y t u v 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 NULL default 3 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 7 default 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 NULL default 20 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 7 explicit 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 NULL default 50 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 NULL explicit 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 NULL default 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 NULL default --- Perform basic operation on master --- --- and ensure replicated correctly --- --- Update t1 on master -- UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" WHERE id < 100 ORDER BY id; --- Check the update on master --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 3 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 20 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 50 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 --- Check Update on slave --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t u v 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 3 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 default 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 20 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 explicit 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 50 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL explicit --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 412; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 7 --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 7 TRUNCATE TABLE t1; TRUNCATE TABLE t1; --- Check that replication works when master has more columns than slave --- Remove columns on slave --- ALTER TABLE t1 DROP COLUMN v; ALTER TABLE t1 DROP COLUMN u; ALTER TABLE t1 DROP COLUMN t; ALTER TABLE t1 DROP COLUMN y; --- Show changed table on slave --- SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` int(11) DEFAULT NULL, `vc` text, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT '0.0000', `f` float DEFAULT '0', `total` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`,`total`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 STOP SLAVE; RESET SLAVE; RESET MASTER; START SLAVE; --- Populate t1 with data --- --- Select from t1 on master --- select * from t1 order by id; id 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 --- Select from t1 on slave --- select * from t1 order by id; id b1 vc bc d f total 2 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 4 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 42 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 --- Perform basic operation on master --- --- and ensure replicated correctly --- --- Update t1 on master -- UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" WHERE id < 100 ORDER BY id; --- Check the update on master --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total y t 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 --- Check Update on slave --- SELECT * FROM t1 WHERE id < 100 ORDER BY id; id b1 vc bc d f total 2 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 4 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 42 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 --- Remove a record from t1 on master --- DELETE FROM t1 WHERE id = 412; --- Show current count on master for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 --- Show current count on slave for t1 --- SELECT COUNT(*) FROM t1; COUNT(*) 4 TRUNCATE TABLE t1; TRUNCATE TABLE t1; --- Do Cleanup -- DROP TABLE IF EXISTS t1; include/rpl_end.inc