1include/master-slave.inc 2[connection master] 3SET default_storage_engine=ndb; 4 5=== NDB -> MYISAM === 6 7[connection slave] 8set @old_slave_exec_mode= @@global.slave_exec_mode; 9set @@global.slave_exec_mode= 'IDEMPOTENT'; 10CREATE TABLE mysql.ndb_apply_status 11( server_id INT UNSIGNED NOT NULL, 12epoch BIGINT UNSIGNED NOT NULL, 13log_name VARCHAR(255) BINARY NOT NULL, 14start_pos BIGINT UNSIGNED NOT NULL, 15end_pos BIGINT UNSIGNED NOT NULL, 16PRIMARY KEY USING HASH (server_id)) ENGINE=MYISAM; 17SET @old_slave_storage_engine=@@global.default_storage_engine; 18SET @@global.default_storage_engine=myisam; 19include/start_slave.inc 20--- Doing pre test cleanup --- 21DROP TABLE IF EXISTS t1; 22--- Create Table Section --- 23CREATE TABLE t1 (id MEDIUMINT NOT NULL, 24b1 INT, 25vc VARCHAR(255), 26bc CHAR(255), 27d DECIMAL(10,4) DEFAULT 0, 28f FLOAT DEFAULT 0, 29total BIGINT UNSIGNED, 30y YEAR, 31t DATE, 32PRIMARY KEY(id)); 33--- Show table on master --- 34SHOW CREATE TABLE t1; 35Table Create Table 36t1 CREATE TABLE `t1` ( 37 `id` mediumint(9) NOT NULL, 38 `b1` int(11) DEFAULT NULL, 39 `vc` varchar(255) DEFAULT NULL, 40 `bc` char(255) DEFAULT NULL, 41 `d` decimal(10,4) DEFAULT '0.0000', 42 `f` float DEFAULT '0', 43 `total` bigint(20) unsigned DEFAULT NULL, 44 `y` year(4) DEFAULT NULL, 45 `t` date DEFAULT NULL, 46 PRIMARY KEY (`id`) 47) ENGINE=ndbcluster DEFAULT CHARSET=latin1 48--- Show table on slave --- 49SHOW CREATE TABLE t1; 50Table Create Table 51t1 CREATE TABLE `t1` ( 52 `id` mediumint(9) NOT NULL, 53 `b1` int(11) DEFAULT NULL, 54 `vc` varchar(255) DEFAULT NULL, 55 `bc` char(255) DEFAULT NULL, 56 `d` decimal(10,4) DEFAULT '0.0000', 57 `f` float DEFAULT '0', 58 `total` bigint(20) unsigned DEFAULT NULL, 59 `y` year(4) DEFAULT NULL, 60 `t` date DEFAULT NULL, 61 PRIMARY KEY (`id`) 62) ENGINE=MyISAM DEFAULT CHARSET=latin1 63STOP SLAVE; 64RESET SLAVE; 65RESET MASTER; 66START SLAVE; 67--- Populate t1 with data --- 68--- Select from t1 on master --- 69select * 70from t1 71order by id; 72id b1 vc bc d f total y t 732 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 744 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 7542 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 76142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 77412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 78--- Select from t1 on slave --- 79select * 80from t1 81order by id; 82id b1 vc bc d f total y t 832 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 844 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 8542 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 86142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 87412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 88--- Perform basic operation on master --- 89--- and ensure replicated correctly --- 90--- Update t1 on master -- 91UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 92WHERE id < 100 93ORDER BY id; 94--- Check the update on master --- 95SELECT * 96FROM t1 97WHERE id < 100 98ORDER BY id; 99id b1 vc bc d f total y t 1002 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 1014 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 10242 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 103--- Check Update on slave --- 104SELECT * 105FROM t1 106WHERE id < 100 107ORDER BY id; 108id b1 vc bc d f total y t 1092 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 1104 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 11142 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 112--- Remove a record from t1 on master --- 113DELETE FROM t1 WHERE id = 412; 114--- Show current count on master for t1 --- 115SELECT COUNT(*) FROM t1; 116COUNT(*) 1174 118--- Show current count on slave for t1 --- 119SELECT COUNT(*) FROM t1; 120COUNT(*) 1214 122TRUNCATE TABLE t1; 123--- Check that simple Alter statements are replicated correctly -- 124ALTER TABLE t1 DROP PRIMARY KEY; 125ALTER TABLE t1 MODIFY vc char(32); 126--- Show the new improved table on the master --- 127SHOW CREATE TABLE t1; 128Table Create Table 129t1 CREATE TABLE `t1` ( 130 `id` mediumint(9) NOT NULL, 131 `b1` int(11) DEFAULT NULL, 132 `vc` char(32) DEFAULT NULL, 133 `bc` char(255) DEFAULT NULL, 134 `d` decimal(10,4) DEFAULT '0.0000', 135 `f` float DEFAULT '0', 136 `total` bigint(20) unsigned DEFAULT NULL, 137 `y` year(4) DEFAULT NULL, 138 `t` date DEFAULT NULL 139) ENGINE=ndbcluster DEFAULT CHARSET=latin1 140--- Make sure that our tables on slave are still same engine --- 141--- and that the alter statements replicated correctly --- 142SHOW CREATE TABLE t1; 143Table Create Table 144t1 CREATE TABLE `t1` ( 145 `id` mediumint(9) NOT NULL, 146 `b1` int(11) DEFAULT NULL, 147 `vc` char(32) DEFAULT NULL, 148 `bc` char(255) DEFAULT NULL, 149 `d` decimal(10,4) DEFAULT '0.0000', 150 `f` float DEFAULT '0', 151 `total` bigint(20) unsigned DEFAULT NULL, 152 `y` year(4) DEFAULT NULL, 153 `t` date DEFAULT NULL 154) ENGINE=MyISAM DEFAULT CHARSET=latin1 155STOP SLAVE; 156RESET SLAVE; 157RESET MASTER; 158START SLAVE; 159--- Populate t1 with data --- 160--- Select from t1 on master --- 161select * 162from t1 163order by id; 164id b1 vc bc d f total y t 1652 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 1664 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 16742 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 168142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 169412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 170--- Select from t1 on slave --- 171select * 172from t1 173order by id; 174id b1 vc bc d f total y t 1752 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 1764 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 17742 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 178142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 179412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 180--- Perform basic operation on master --- 181--- and ensure replicated correctly --- 182--- Update t1 on master -- 183UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 184WHERE id < 100 185ORDER BY id; 186--- Check the update on master --- 187SELECT * 188FROM t1 189WHERE id < 100 190ORDER BY id; 191id b1 vc bc d f total y t 1922 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 1934 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 19442 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 195--- Check Update on slave --- 196SELECT * 197FROM t1 198WHERE id < 100 199ORDER BY id; 200id b1 vc bc d f total y t 2012 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 2024 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 20342 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 204--- Remove a record from t1 on master --- 205DELETE FROM t1 WHERE id = 412; 206--- Show current count on master for t1 --- 207SELECT COUNT(*) FROM t1; 208COUNT(*) 2094 210--- Show current count on slave for t1 --- 211SELECT COUNT(*) FROM t1; 212COUNT(*) 2134 214TRUNCATE TABLE t1; 215--- Check that replication works when slave has more columns than master 216ALTER TABLE t1 ADD PRIMARY KEY(id,total); 217ALTER TABLE t1 MODIFY vc TEXT; 218INSERT INTO t1 VALUES(3,1,'Testing MySQL databases is a cool ', 219'Must make it bug free for the customer', 220654321.4321,15.21,0,1965,"1905-11-14"); 221INSERT INTO t1 VALUES(20,1,'Testing MySQL databases is a cool ', 222'Must make it bug free for the customer', 223654321.4321,15.21,0,1965,"1965-11-14"); 224INSERT INTO t1 VALUES(50,1,'Testing MySQL databases is a cool ', 225'Must make it bug free for the customer', 226654321.4321,15.21,0,1965,"1985-11-14"); 227--- Add columns on slave --- 228ALTER TABLE t1 ADD (u int, v char(16) default 'default'); 229UPDATE t1 SET u=7 WHERE id < 50; 230UPDATE t1 SET v='explicit' WHERE id >10; 231--- Show changed table on slave --- 232SHOW CREATE TABLE t1; 233Table Create Table 234t1 CREATE TABLE `t1` ( 235 `id` mediumint(9) NOT NULL, 236 `b1` int(11) DEFAULT NULL, 237 `vc` text, 238 `bc` char(255) DEFAULT NULL, 239 `d` decimal(10,4) DEFAULT '0.0000', 240 `f` float DEFAULT '0', 241 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 242 `y` year(4) DEFAULT NULL, 243 `t` date DEFAULT NULL, 244 `u` int(11) DEFAULT NULL, 245 `v` char(16) DEFAULT 'default', 246 PRIMARY KEY (`id`,`total`) 247) ENGINE=MyISAM DEFAULT CHARSET=latin1 248SELECT * 249FROM t1 250ORDER BY id; 251id b1 vc bc d f total y t u v 2523 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 25320 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 25450 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 255STOP SLAVE; 256RESET SLAVE; 257RESET MASTER; 258START SLAVE; 259--- Populate t1 with data --- 260--- Select from t1 on master --- 261select * 262from t1 263order by id; 264id b1 vc bc d f total y t 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 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 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 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 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 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 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 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 273--- Select from t1 on slave --- 274select * 275from t1 276order by id; 277id b1 vc bc d f total y t u v 2782 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 2793 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 2804 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 28120 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 28242 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 28350 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 284142 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 285412 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 286--- Perform basic operation on master --- 287--- and ensure replicated correctly --- 288--- Update t1 on master -- 289UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 290WHERE id < 100 291ORDER BY id; 292--- Check the update on master --- 293SELECT * 294FROM t1 295WHERE id < 100 296ORDER BY id; 297id b1 vc bc d f total y t 2982 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 2993 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 3004 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 30120 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 30242 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 30350 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 304--- Check Update on slave --- 305SELECT * 306FROM t1 307WHERE id < 100 308ORDER BY id; 309id b1 vc bc d f total y t u v 3102 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 3113 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 default 3124 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 31320 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 explicit 31442 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 31550 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL explicit 316--- Remove a record from t1 on master --- 317DELETE FROM t1 WHERE id = 412; 318--- Show current count on master for t1 --- 319SELECT COUNT(*) FROM t1; 320COUNT(*) 3217 322--- Show current count on slave for t1 --- 323SELECT COUNT(*) FROM t1; 324COUNT(*) 3257 326TRUNCATE TABLE t1; 327TRUNCATE TABLE t1; 328--- Check that replication works when master has more columns than slave 329--- Remove columns on slave --- 330ALTER TABLE t1 DROP COLUMN v; 331ALTER TABLE t1 DROP COLUMN u; 332ALTER TABLE t1 DROP COLUMN t; 333ALTER TABLE t1 DROP COLUMN y; 334--- Show changed table on slave --- 335SHOW CREATE TABLE t1; 336Table Create Table 337t1 CREATE TABLE `t1` ( 338 `id` mediumint(9) NOT NULL, 339 `b1` int(11) DEFAULT NULL, 340 `vc` text, 341 `bc` char(255) DEFAULT NULL, 342 `d` decimal(10,4) DEFAULT '0.0000', 343 `f` float DEFAULT '0', 344 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 345 PRIMARY KEY (`id`,`total`) 346) ENGINE=MyISAM DEFAULT CHARSET=latin1 347STOP SLAVE; 348RESET SLAVE; 349RESET MASTER; 350START SLAVE; 351--- Populate t1 with data --- 352--- Select from t1 on master --- 353select * 354from t1 355order by id; 356id b1 vc bc d f total y t 3572 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 3584 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 35942 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 360142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 361412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 362--- Select from t1 on slave --- 363select * 364from t1 365order by id; 366id b1 vc bc d f total 3672 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 3684 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 36942 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 370142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 371412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 372--- Perform basic operation on master --- 373--- and ensure replicated correctly --- 374--- Update t1 on master -- 375UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 376WHERE id < 100 377ORDER BY id; 378--- Check the update on master --- 379SELECT * 380FROM t1 381WHERE id < 100 382ORDER BY id; 383id b1 vc bc d f total y t 3842 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 3854 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 38642 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 387--- Check Update on slave --- 388SELECT * 389FROM t1 390WHERE id < 100 391ORDER BY id; 392id b1 vc bc d f total 3932 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 3944 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 39542 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 396--- Remove a record from t1 on master --- 397DELETE FROM t1 WHERE id = 412; 398--- Show current count on master for t1 --- 399SELECT COUNT(*) FROM t1; 400COUNT(*) 4014 402--- Show current count on slave for t1 --- 403SELECT COUNT(*) FROM t1; 404COUNT(*) 4054 406TRUNCATE TABLE t1; 407TRUNCATE TABLE t1; 408--- Do Cleanup -- 409DROP TABLE IF EXISTS t1; 410[connection slave] 411include/stop_slave.inc 412 413=== NDB -> INNODB === 414 415[connection slave] 416alter table mysql.ndb_apply_status engine=innodb; 417SET @@global.default_storage_engine=innodb; 418include/start_slave.inc 419--- Doing pre test cleanup --- 420DROP TABLE IF EXISTS t1; 421--- Create Table Section --- 422CREATE TABLE t1 (id MEDIUMINT NOT NULL, 423b1 INT, 424vc VARCHAR(255), 425bc CHAR(255), 426d DECIMAL(10,4) DEFAULT 0, 427f FLOAT DEFAULT 0, 428total BIGINT UNSIGNED, 429y YEAR, 430t DATE, 431PRIMARY KEY(id)); 432--- Show table on master --- 433SHOW CREATE TABLE t1; 434Table Create Table 435t1 CREATE TABLE `t1` ( 436 `id` mediumint(9) NOT NULL, 437 `b1` int(11) DEFAULT NULL, 438 `vc` varchar(255) DEFAULT NULL, 439 `bc` char(255) DEFAULT NULL, 440 `d` decimal(10,4) DEFAULT '0.0000', 441 `f` float DEFAULT '0', 442 `total` bigint(20) unsigned DEFAULT NULL, 443 `y` year(4) DEFAULT NULL, 444 `t` date DEFAULT NULL, 445 PRIMARY KEY (`id`) 446) ENGINE=ndbcluster DEFAULT CHARSET=latin1 447--- Show table on slave --- 448SHOW CREATE TABLE t1; 449Table Create Table 450t1 CREATE TABLE `t1` ( 451 `id` mediumint(9) NOT NULL, 452 `b1` int(11) DEFAULT NULL, 453 `vc` varchar(255) DEFAULT NULL, 454 `bc` char(255) DEFAULT NULL, 455 `d` decimal(10,4) DEFAULT '0.0000', 456 `f` float DEFAULT '0', 457 `total` bigint(20) unsigned DEFAULT NULL, 458 `y` year(4) DEFAULT NULL, 459 `t` date DEFAULT NULL, 460 PRIMARY KEY (`id`) 461) ENGINE=InnoDB DEFAULT CHARSET=latin1 462STOP SLAVE; 463RESET SLAVE; 464RESET MASTER; 465START SLAVE; 466--- Populate t1 with data --- 467--- Select from t1 on master --- 468select * 469from t1 470order by id; 471id b1 vc bc d f total y t 4722 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4734 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 47442 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 475142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 476412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 477--- Select from t1 on slave --- 478select * 479from t1 480order by id; 481id b1 vc bc d f total y t 4822 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 4834 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 48442 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 485142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 486412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 487--- Perform basic operation on master --- 488--- and ensure replicated correctly --- 489--- Update t1 on master -- 490UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 491WHERE id < 100 492ORDER BY id; 493--- Check the update on master --- 494SELECT * 495FROM t1 496WHERE id < 100 497ORDER BY id; 498id b1 vc bc d f total y t 4992 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 5004 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 50142 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 502--- Check Update on slave --- 503SELECT * 504FROM t1 505WHERE id < 100 506ORDER BY id; 507id b1 vc bc d f total y t 5082 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 5094 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 51042 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 511--- Remove a record from t1 on master --- 512DELETE FROM t1 WHERE id = 412; 513--- Show current count on master for t1 --- 514SELECT COUNT(*) FROM t1; 515COUNT(*) 5164 517--- Show current count on slave for t1 --- 518SELECT COUNT(*) FROM t1; 519COUNT(*) 5204 521TRUNCATE TABLE t1; 522--- Check that simple Alter statements are replicated correctly -- 523ALTER TABLE t1 DROP PRIMARY KEY; 524ALTER TABLE t1 MODIFY vc char(32); 525--- Show the new improved table on the master --- 526SHOW CREATE TABLE t1; 527Table Create Table 528t1 CREATE TABLE `t1` ( 529 `id` mediumint(9) NOT NULL, 530 `b1` int(11) DEFAULT NULL, 531 `vc` char(32) DEFAULT NULL, 532 `bc` char(255) DEFAULT NULL, 533 `d` decimal(10,4) DEFAULT '0.0000', 534 `f` float DEFAULT '0', 535 `total` bigint(20) unsigned DEFAULT NULL, 536 `y` year(4) DEFAULT NULL, 537 `t` date DEFAULT NULL 538) ENGINE=ndbcluster DEFAULT CHARSET=latin1 539--- Make sure that our tables on slave are still same engine --- 540--- and that the alter statements replicated correctly --- 541SHOW CREATE TABLE t1; 542Table Create Table 543t1 CREATE TABLE `t1` ( 544 `id` mediumint(9) NOT NULL, 545 `b1` int(11) DEFAULT NULL, 546 `vc` char(32) DEFAULT NULL, 547 `bc` char(255) DEFAULT NULL, 548 `d` decimal(10,4) DEFAULT '0.0000', 549 `f` float DEFAULT '0', 550 `total` bigint(20) unsigned DEFAULT NULL, 551 `y` year(4) DEFAULT NULL, 552 `t` date DEFAULT NULL 553) ENGINE=InnoDB DEFAULT CHARSET=latin1 554STOP SLAVE; 555RESET SLAVE; 556RESET MASTER; 557START SLAVE; 558--- Populate t1 with data --- 559--- Select from t1 on master --- 560select * 561from t1 562order by id; 563id b1 vc bc d f total y t 5642 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 5654 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 56642 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 567142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 568412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 569--- Select from t1 on slave --- 570select * 571from t1 572order by id; 573id b1 vc bc d f total y t 5742 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 5754 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 57642 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 577142 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 578412 1 Testing MySQL databases is a coo Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 579--- Perform basic operation on master --- 580--- and ensure replicated correctly --- 581--- Update t1 on master -- 582UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 583WHERE id < 100 584ORDER BY id; 585--- Check the update on master --- 586SELECT * 587FROM t1 588WHERE id < 100 589ORDER BY id; 590id b1 vc bc d f total y t 5912 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 5924 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 59342 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 594--- Check Update on slave --- 595SELECT * 596FROM t1 597WHERE id < 100 598ORDER BY id; 599id b1 vc bc d f total y t 6002 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 6014 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 60242 0 Testing MySQL databases is a coo updated 654321.4321 15.21 0 1965 2006-02-22 603--- Remove a record from t1 on master --- 604DELETE FROM t1 WHERE id = 412; 605--- Show current count on master for t1 --- 606SELECT COUNT(*) FROM t1; 607COUNT(*) 6084 609--- Show current count on slave for t1 --- 610SELECT COUNT(*) FROM t1; 611COUNT(*) 6124 613TRUNCATE TABLE t1; 614--- Check that replication works when slave has more columns than master 615ALTER TABLE t1 ADD PRIMARY KEY(id,total); 616ALTER TABLE t1 MODIFY vc TEXT; 617INSERT INTO t1 VALUES(3,1,'Testing MySQL databases is a cool ', 618'Must make it bug free for the customer', 619654321.4321,15.21,0,1965,"1905-11-14"); 620INSERT INTO t1 VALUES(20,1,'Testing MySQL databases is a cool ', 621'Must make it bug free for the customer', 622654321.4321,15.21,0,1965,"1965-11-14"); 623INSERT INTO t1 VALUES(50,1,'Testing MySQL databases is a cool ', 624'Must make it bug free for the customer', 625654321.4321,15.21,0,1965,"1985-11-14"); 626--- Add columns on slave --- 627ALTER TABLE t1 ADD (u int, v char(16) default 'default'); 628UPDATE t1 SET u=7 WHERE id < 50; 629UPDATE t1 SET v='explicit' WHERE id >10; 630--- Show changed table on slave --- 631SHOW CREATE TABLE t1; 632Table Create Table 633t1 CREATE TABLE `t1` ( 634 `id` mediumint(9) NOT NULL, 635 `b1` int(11) DEFAULT NULL, 636 `vc` text, 637 `bc` char(255) DEFAULT NULL, 638 `d` decimal(10,4) DEFAULT '0.0000', 639 `f` float DEFAULT '0', 640 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 641 `y` year(4) DEFAULT NULL, 642 `t` date DEFAULT NULL, 643 `u` int(11) DEFAULT NULL, 644 `v` char(16) DEFAULT 'default', 645 PRIMARY KEY (`id`,`total`) 646) ENGINE=InnoDB DEFAULT CHARSET=latin1 647SELECT * 648FROM t1 649ORDER BY id; 650id b1 vc bc d f total y t u v 6513 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 65220 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 65350 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 654STOP SLAVE; 655RESET SLAVE; 656RESET MASTER; 657START SLAVE; 658--- Populate t1 with data --- 659--- Select from t1 on master --- 660select * 661from t1 662order by id; 663id b1 vc bc d f total y t 6642 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 6653 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 6664 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 66720 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 66842 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 66950 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 670142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 671412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 672--- Select from t1 on slave --- 673select * 674from t1 675order by id; 676id b1 vc bc d f total y t u v 6772 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 6783 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 6794 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 68020 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 68142 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 68250 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 683142 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 684412 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 685--- Perform basic operation on master --- 686--- and ensure replicated correctly --- 687--- Update t1 on master -- 688UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 689WHERE id < 100 690ORDER BY id; 691--- Check the update on master --- 692SELECT * 693FROM t1 694WHERE id < 100 695ORDER BY id; 696id b1 vc bc d f total y t 6972 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 6983 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 6994 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 70020 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 70142 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 70250 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 703--- Check Update on slave --- 704SELECT * 705FROM t1 706WHERE id < 100 707ORDER BY id; 708id b1 vc bc d f total y t u v 7092 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 7103 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 default 7114 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 71220 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7 explicit 71342 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL default 71450 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 NULL explicit 715--- Remove a record from t1 on master --- 716DELETE FROM t1 WHERE id = 412; 717--- Show current count on master for t1 --- 718SELECT COUNT(*) FROM t1; 719COUNT(*) 7207 721--- Show current count on slave for t1 --- 722SELECT COUNT(*) FROM t1; 723COUNT(*) 7247 725TRUNCATE TABLE t1; 726TRUNCATE TABLE t1; 727--- Check that replication works when master has more columns than slave 728--- Remove columns on slave --- 729ALTER TABLE t1 DROP COLUMN v; 730ALTER TABLE t1 DROP COLUMN u; 731ALTER TABLE t1 DROP COLUMN t; 732ALTER TABLE t1 DROP COLUMN y; 733--- Show changed table on slave --- 734SHOW CREATE TABLE t1; 735Table Create Table 736t1 CREATE TABLE `t1` ( 737 `id` mediumint(9) NOT NULL, 738 `b1` int(11) DEFAULT NULL, 739 `vc` text, 740 `bc` char(255) DEFAULT NULL, 741 `d` decimal(10,4) DEFAULT '0.0000', 742 `f` float DEFAULT '0', 743 `total` bigint(20) unsigned NOT NULL DEFAULT '0', 744 PRIMARY KEY (`id`,`total`) 745) ENGINE=InnoDB DEFAULT CHARSET=latin1 746STOP SLAVE; 747RESET SLAVE; 748RESET MASTER; 749START SLAVE; 750--- Populate t1 with data --- 751--- Select from t1 on master --- 752select * 753from t1 754order by id; 755id b1 vc bc d f total y t 7562 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1965-11-14 7574 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1985-11-14 75842 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1905-11-14 759142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 1995-11-14 760412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 1965 2005-11-14 761--- Select from t1 on slave --- 762select * 763from t1 764order by id; 765id b1 vc bc d f total 7662 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 7674 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 76842 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 769142 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 770412 1 Testing MySQL databases is a cool Must make it bug free for the customer 654321.4321 15.21 0 771--- Perform basic operation on master --- 772--- and ensure replicated correctly --- 773--- Update t1 on master -- 774UPDATE t1 SET b1 = 0, bc='updated', t="2006-02-22" 775WHERE id < 100 776ORDER BY id; 777--- Check the update on master --- 778SELECT * 779FROM t1 780WHERE id < 100 781ORDER BY id; 782id b1 vc bc d f total y t 7832 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 7844 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 78542 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 1965 2006-02-22 786--- Check Update on slave --- 787SELECT * 788FROM t1 789WHERE id < 100 790ORDER BY id; 791id b1 vc bc d f total 7922 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 7934 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 79442 0 Testing MySQL databases is a cool updated 654321.4321 15.21 0 795--- Remove a record from t1 on master --- 796DELETE FROM t1 WHERE id = 412; 797--- Show current count on master for t1 --- 798SELECT COUNT(*) FROM t1; 799COUNT(*) 8004 801--- Show current count on slave for t1 --- 802SELECT COUNT(*) FROM t1; 803COUNT(*) 8044 805TRUNCATE TABLE t1; 806TRUNCATE TABLE t1; 807--- Do Cleanup -- 808DROP TABLE IF EXISTS t1; 809[connection slave] 810include/stop_slave.inc 811[connection slave] 812drop table mysql.ndb_apply_status; 813set @@global.slave_exec_mode= @old_slave_exec_mode; 814SET @@global.default_storage_engine=@old_slave_storage_engine; 815