1include/master-slave.inc 2[connection master] 3SET default_storage_engine=ndb; 4 5=== NDB -> NDB === 6 7--- Doing pre test cleanup --- 8DROP TABLE IF EXISTS t1; 9--- Create Table Section --- 10CREATE TABLE t1 (id MEDIUMINT NOT NULL, 11b1 INT, 12vc VARCHAR(255), 13bc CHAR(255), 14d DECIMAL(10,4) DEFAULT 0, 15f FLOAT DEFAULT 0, 16total BIGINT UNSIGNED, 17y YEAR, 18t DATE, 19PRIMARY KEY(id)); 20--- Show table on master --- 21SHOW CREATE TABLE t1; 22Table Create Table 23t1 CREATE TABLE `t1` ( 24 `id` mediumint(9) NOT NULL, 25 `b1` int(11) DEFAULT NULL, 26 `vc` varchar(255) DEFAULT NULL, 27 `bc` char(255) DEFAULT NULL, 28 `d` decimal(10,4) DEFAULT '0.0000', 29 `f` float DEFAULT '0', 30 `total` bigint(20) unsigned DEFAULT NULL, 31 `y` year(4) DEFAULT NULL, 32 `t` date DEFAULT NULL, 33 PRIMARY KEY (`id`) 34) ENGINE=ndbcluster DEFAULT CHARSET=latin1 35--- Show table on slave --- 36SHOW CREATE TABLE t1; 37Table Create Table 38t1 CREATE TABLE `t1` ( 39 `id` mediumint(9) NOT NULL, 40 `b1` int(11) DEFAULT NULL, 41 `vc` varchar(255) DEFAULT NULL, 42 `bc` char(255) DEFAULT NULL, 43 `d` decimal(10,4) DEFAULT '0.0000', 44 `f` float DEFAULT '0', 45 `total` bigint(20) unsigned DEFAULT NULL, 46 `y` year(4) DEFAULT NULL, 47 `t` date DEFAULT NULL, 48 PRIMARY KEY (`id`) 49) ENGINE=ndbcluster DEFAULT CHARSET=latin1 50STOP SLAVE; 51RESET SLAVE; 52RESET MASTER; 53START SLAVE; 54--- Populate t1 with data --- 55--- Select from t1 on master --- 56select * 57from t1 58order by id; 59id b1 vc bc d f total y t 602 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 614 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 6242 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 63142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 64412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 65--- Select from t1 on slave --- 66select * 67from t1 68order by id; 69id b1 vc bc d f total y t 702 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 714 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 7242 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 73142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 74412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 75--- Perform basic operation on master --- 76--- and ensure replicated correctly --- 77--- Update t1 on master -- 78UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 79WHERE id < 100 80ORDER BY id; 81--- Check the update on master --- 82SELECT * 83FROM t1 84WHERE id < 100 85ORDER BY id; 86id b1 vc bc d f total y t 872 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 884 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 8942 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 90--- Check Update on slave --- 91SELECT * 92FROM t1 93WHERE id < 100 94ORDER BY id; 95id b1 vc bc d f total y t 962 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 974 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 9842 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 99--- Remove a record from t1 on master --- 100DELETE FROM t1 WHERE id = 412; 101--- Show current count on master for t1 --- 102SELECT COUNT(*) FROM t1; 103COUNT(*) 1044 105--- Show current count on slave for t1 --- 106SELECT COUNT(*) FROM t1; 107COUNT(*) 1084 109TRUNCATE TABLE t1; 110--- Check that simple Alter statements are replicated correctly -- 111ALTER TABLE t1 DROP PRIMARY KEY; 112ALTER TABLE t1 MODIFY vc char(32); 113--- Show the new improved table on the master --- 114SHOW CREATE TABLE t1; 115Table Create Table 116t1 CREATE TABLE `t1` ( 117 `id` mediumint(9) NOT NULL, 118 `b1` int(11) DEFAULT NULL, 119 `vc` char(32) DEFAULT NULL, 120 `bc` char(255) DEFAULT NULL, 121 `d` decimal(10,4) DEFAULT '0.0000', 122 `f` float DEFAULT '0', 123 `total` bigint(20) unsigned DEFAULT NULL, 124 `y` year(4) DEFAULT NULL, 125 `t` date DEFAULT NULL 126) ENGINE=ndbcluster DEFAULT CHARSET=latin1 127--- Make sure that our tables on slave are still same engine --- 128--- and that the alter statements replicated correctly --- 129SHOW CREATE TABLE t1; 130Table Create Table 131t1 CREATE TABLE `t1` ( 132 `id` mediumint(9) NOT NULL, 133 `b1` int(11) DEFAULT NULL, 134 `vc` char(32) DEFAULT NULL, 135 `bc` char(255) DEFAULT NULL, 136 `d` decimal(10,4) DEFAULT '0.0000', 137 `f` float DEFAULT '0', 138 `total` bigint(20) unsigned DEFAULT NULL, 139 `y` year(4) DEFAULT NULL, 140 `t` date DEFAULT NULL 141) ENGINE=ndbcluster DEFAULT CHARSET=latin1 142STOP SLAVE; 143RESET SLAVE; 144RESET MASTER; 145START SLAVE; 146--- Populate t1 with data --- 147--- Select from t1 on master --- 148select * 149from t1 150order by id; 151id b1 vc bc d f total y t 1522 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 1534 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 15442 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 155142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 156412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 157--- Select from t1 on slave --- 158select * 159from t1 160order by id; 161id b1 vc bc d f total y t 1622 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 1634 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 16442 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 165142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 166412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 167--- Perform basic operation on master --- 168--- and ensure replicated correctly --- 169--- Update t1 on master -- 170UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 171WHERE id < 100 172ORDER BY id; 173--- Check the update on master --- 174SELECT * 175FROM t1 176WHERE id < 100 177ORDER BY id; 178id b1 vc bc d f total y t 1792 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 1804 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 18142 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 182--- Check Update on slave --- 183SELECT * 184FROM t1 185WHERE id < 100 186ORDER BY id; 187id b1 vc bc d f total y t 1882 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 1894 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 19042 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 191--- Remove a record from t1 on master --- 192DELETE FROM t1 WHERE id = 412; 193--- Show current count on master for t1 --- 194SELECT COUNT(*) FROM t1; 195COUNT(*) 1964 197--- Show current count on slave for t1 --- 198SELECT COUNT(*) FROM t1; 199COUNT(*) 2004 201TRUNCATE TABLE t1; 202--- Check that replication works when slave has more columns than master 203ALTER TABLE t1 ADD PRIMARY KEY(id,total); 204ALTER TABLE t1 MODIFY vc TEXT; 205INSERT INTO t1 VALUES(3,1,'Testing MySQL databases is a cool ', 206'Must make it bug free for the customer', 207654321.4321,15.21,0,1965,"1905-11-14"); 208INSERT INTO t1 VALUES(20,1,'Testing MySQL databases is a cool ', 209'Must make it bug free for the customer', 210654321.4321,15.21,0,1965,"1965-11-14"); 211INSERT INTO t1 VALUES(50,1,'Testing MySQL databases is a cool ', 212'Must make it bug free for the customer', 213654321.4321,15.21,0,1965,"1985-11-14"); 214--- Add columns on slave --- 215ALTER TABLE t1 ADD (u int, v char(16) default 'default'); 216UPDATE t1 SET u=7 WHERE id < 50; 217UPDATE t1 SET v='explicit' WHERE id >10; 218--- Show changed table on slave --- 219SHOW CREATE TABLE t1; 220Table Create Table 221t1 CREATE TABLE `t1` ( 222 `id` mediumint(9) NOT NULL, 223 `b1` int(11) DEFAULT NULL, 224 `vc` text, 225 `bc` char(255) DEFAULT NULL, 226 `d` decimal(10,4) DEFAULT '0.0000', 227 `f` float DEFAULT '0', 228 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 229 `y` year(4) DEFAULT NULL, 230 `t` date DEFAULT NULL, 231 `u` int(11) DEFAULT NULL, 232 `v` char(16) DEFAULT 'default', 233 PRIMARY KEY (`id`,`total`) 234) ENGINE=ndbcluster DEFAULT CHARSET=latin1 235SELECT * 236FROM t1 237ORDER BY id; 238id b1 vc bc d f total y t u v 2393 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 24020 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 24150 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 242STOP SLAVE; 243RESET SLAVE; 244RESET MASTER; 245START SLAVE; 246--- Populate t1 with data --- 247--- Select from t1 on master --- 248select * 249from t1 250order by id; 251id b1 vc bc d f total y t 2522 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 2533 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 2544 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 25520 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 25642 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 25750 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 258142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 259412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 260--- Select from t1 on slave --- 261select * 262from t1 263order by id; 264id b1 vc bc d f total y t u v 2652 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 2663 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 2674 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 26820 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 26942 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 27050 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 271142 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 272412 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 273--- Perform basic operation on master --- 274--- and ensure replicated correctly --- 275--- Update t1 on master -- 276UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 277WHERE id < 100 278ORDER BY id; 279--- Check the update on master --- 280SELECT * 281FROM t1 282WHERE id < 100 283ORDER BY id; 284id b1 vc bc d f total y t 2852 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 2863 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 2874 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 28820 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 28942 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 29050 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 291--- Check Update on slave --- 292SELECT * 293FROM t1 294WHERE id < 100 295ORDER BY id; 296id b1 vc bc d f total y t u v 2972 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 2983 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 default 2994 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 30020 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 explicit 30142 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 30250 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL explicit 303--- Remove a record from t1 on master --- 304DELETE FROM t1 WHERE id = 412; 305--- Show current count on master for t1 --- 306SELECT COUNT(*) FROM t1; 307COUNT(*) 3087 309--- Show current count on slave for t1 --- 310SELECT COUNT(*) FROM t1; 311COUNT(*) 3127 313TRUNCATE TABLE t1; 314TRUNCATE TABLE t1; 315--- Check that replication works when master has more columns than slave 316--- Remove columns on slave --- 317ALTER TABLE t1 DROP COLUMN v; 318ALTER TABLE t1 DROP COLUMN u; 319ALTER TABLE t1 DROP COLUMN t; 320ALTER TABLE t1 DROP COLUMN y; 321--- Show changed table on slave --- 322SHOW CREATE TABLE t1; 323Table Create Table 324t1 CREATE TABLE `t1` ( 325 `id` mediumint(9) NOT NULL, 326 `b1` int(11) DEFAULT NULL, 327 `vc` text, 328 `bc` char(255) DEFAULT NULL, 329 `d` decimal(10,4) DEFAULT '0.0000', 330 `f` float DEFAULT '0', 331 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 332 PRIMARY KEY (`id`,`total`) 333) ENGINE=ndbcluster DEFAULT CHARSET=latin1 334STOP SLAVE; 335RESET SLAVE; 336RESET MASTER; 337START SLAVE; 338--- Populate t1 with data --- 339--- Select from t1 on master --- 340select * 341from t1 342order by id; 343id b1 vc bc d f total y t 3442 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 3454 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 34642 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 347142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 348412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 349--- Select from t1 on slave --- 350select * 351from t1 352order by id; 353id b1 vc bc d f total 3542 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 3554 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 35642 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 357142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 358412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 359--- Perform basic operation on master --- 360--- and ensure replicated correctly --- 361--- Update t1 on master -- 362UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 363WHERE id < 100 364ORDER BY id; 365--- Check the update on master --- 366SELECT * 367FROM t1 368WHERE id < 100 369ORDER BY id; 370id b1 vc bc d f total y t 3712 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 3724 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 37342 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 374--- Check Update on slave --- 375SELECT * 376FROM t1 377WHERE id < 100 378ORDER BY id; 379id b1 vc bc d f total 3802 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 3814 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 38242 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 383--- Remove a record from t1 on master --- 384DELETE FROM t1 WHERE id = 412; 385--- Show current count on master for t1 --- 386SELECT COUNT(*) FROM t1; 387COUNT(*) 3884 389--- Show current count on slave for t1 --- 390SELECT COUNT(*) FROM t1; 391COUNT(*) 3924 393TRUNCATE TABLE t1; 394TRUNCATE TABLE t1; 395--- Do Cleanup -- 396DROP TABLE IF EXISTS t1; 397include/rpl_end.inc 398