1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6Setup circular replication 7RESET MASTER; 8SLAVE_SERVER_ID: 3 9CHANGE MASTER TO master_host="127.0.0.1",master_port=SLAVE_PORT,master_user="root"; 10Warnings: 11Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure. 12Note 1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 13START SLAVE; 14MASTER_SERVER_ID: 1 15Setup ndb_replication and exceptions tables 16Populate ndb_replication table as necessary 17replace into mysql.ndb_replication values 18("test", "t1", 3, 7, NULL), 19("test", "t1", 1, 7, "NDB$EPOCH()"), 20("test", "t2", 3, 7, NULL), 21("test", "t2", 1, 7, "NDB$EPOCH_TRANS()"); 22call mtr.add_suppression("NDB Slave: exceptions table .* has wrong definition .*"); 23call mtr.add_suppression("Exceptions table *"); 24create table test.t1$EX 25(SERVER_ID int unsigned, 26NDB$MASTER_SERVER_ID int unsigned, 27NDB$MASTER_EPOCH bigint unsigned, 28NDB$COUNT int unsigned, 29a int not null, 30NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 31NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 32primary key(SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 33ENGINE=NDB; 34create table test.t1 ( 35a int not null, 36b int not null, 37c varchar(2000), 38primary key (a,b)) engine=ndb; 39MySQLD error output for server 1.1 matching pattern %Exceptions table % 40relevant 41[warning] ndb slave: exceptions table t1$ex has wrong definition (column 1): 42drop table test.t1$EX, test.t1; 43create table test.t1$EX 44(NDB$SERVER_ID int unsigned, 45NDB$MASTER_SERVER_ID int unsigned, 46NDB$MASTER_EPOCH bigint unsigned, 47NDB$COUNT int unsigned, 48a$old int, 49a$new int, 50NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 51NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 52primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 53ENGINE=NDB; 54create table test.t1 ( 55a int not null, 56b int not null, 57c varchar(2000), 58primary key (a,b)) engine=ndb; 59MySQLD error output for server 1.1 matching pattern %Exceptions table % 60relevant 61[warning] ndb slave: exceptions table t1$ex has wrong definition (column 5): old or new values of primary key columns cannot be referenced since primary keys cannot be updated, column a$new in table t1$ex 62drop table test.t1$EX, test.t1; 63create table test.t1$EX 64(NDB$SERVER_ID int unsigned, 65NDB$MASTER_SERVER_ID int unsigned, 66NDB$MASTER_EPOCH bigint unsigned, 67NDB$COUNT int unsigned, 68c$old varchar(2000) not null, 69c$new varchar(2000) not null, 70NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 71NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 72primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 73ENGINE=NDB; 74create table test.t1 ( 75a int not null, 76b int not null, 77c varchar(2000), 78primary key (a,b)) engine=ndb; 79MySQLD error output for server 1.1 matching pattern %Exceptions table % 80relevant 81[warning] ndb slave: exceptions table t1$ex has wrong definition (column 5): old or new column reference c$new in table t1$ex is not nullable and doesn't have a default value 82drop table test.t1$EX, test.t1; 83create table test.t1$EX 84(NDB$SERVER_ID int unsigned, 85NDB$MASTER_SERVER_ID int unsigned, 86NDB$MASTER_EPOCH bigint unsigned, 87NDB$COUNT int unsigned, 88a int not null, 89NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 90NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 91primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 92ENGINE=NDB; 93create table test.t1 ( 94a int not null, 95b int not null, 96c varchar(2000), 97primary key (a,b)) engine=ndb; 98create table test.t2$ex 99(ndb$server_id int unsigned, 100ndb$master_server_id int unsigned, 101ndb$master_epoch bigint unsigned, 102ndb$count int unsigned, 103d varchar(2000), 104c int not null default 99, 105b int not null, 106a int not null, 107d$old varchar(2000), 108d$new varchar(2000), 109ndb$op_type enum('write_row','update_row', 'delete_row') not null, 110ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 111ndb$orig_transid bigint unsigned not null, 112e int, 113primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 114engine=ndb; 115create table test.t2 ( 116x int default 21, 117a int not null, 118b int not null, 119c int, 120d varchar(2000), 121e int, 122primary key(a,b)) engine=ndb; 123Add some data 124stop slave; 125Slave contents 126select * from t1 order by a; 127a b c 1281 2 Initial data 1 1292 3 Initial data 2 1303 4 Initial data 3 1314 5 Initial data 4 1325 6 Initial data 5 1336 7 Initial data 6 1347 8 Initial data 7 1358 9 Initial data 8 1369 10 Initial data 9 13710 11 Initial data 10 138select * from test.t2 order by a; 139x a b c d e 14021 1 2 3 Initial data 1 17 14121 2 3 4 Initial data 2 17 14221 3 4 5 Initial data 3 17 14321 4 5 6 Initial data 4 17 14421 5 6 7 Initial data 5 17 14521 6 7 8 Initial data 6 17 14621 7 8 9 Initial data 7 17 14721 8 9 10 Initial data 8 17 14821 9 10 11 Initial data 9 17 14921 10 11 12 Initial data 10 17 150Master contents 151select * from test.t1 order by a; 152a b c 1531 2 Master updated data 1 1542 3 Master updated data 2 1553 4 Master updated data 3 1564 5 Master updated data 4 1575 6 Master updated data 5 1586 7 Master updated data 6 1597 8 Master updated data 7 1608 9 Master updated data 8 1619 10 Master updated data 9 16210 11 Master updated data 10 163select * from test.t2 order by a; 164x a b c d e 16521 1 2 3 Master updated data 1 17 16621 2 3 4 Master updated data 2 17 16721 3 4 5 Master updated data 3 17 16821 4 5 6 Master updated data 4 17 16921 5 6 7 Master updated data 5 17 17021 6 7 8 Master updated data 6 17 17121 7 8 9 Master updated data 7 17 17221 8 9 10 Master updated data 8 17 17321 9 10 11 Master updated data 9 17 17421 10 11 12 Master updated data 10 17 175Slave contents 176select * from test.t1 order by a; 177a b c 1781 2 Slave updated data 1 1792 3 Slave updated data 2 1803 4 Slave updated data 3 1814 5 Slave updated data 4 1825 6 Slave updated data 5 1836 7 Slave updated data 6 1847 8 Slave updated data 7 1858 9 Slave updated data 8 1869 10 Slave updated data 9 18710 11 Slave updated data 10 188select * from test.t2 order by a; 189x a b c d e 19021 1 2 3 Slave updated data 1 17 19121 2 3 4 Slave updated data 2 17 19221 3 4 5 Slave updated data 3 17 19321 4 5 6 Slave updated data 4 17 19421 5 6 7 Slave updated data 5 17 19521 6 7 8 Slave updated data 6 17 19621 7 8 9 Slave updated data 7 17 19721 8 9 10 Slave updated data 8 17 19821 9 10 11 Slave updated data 9 17 19921 10 11 12 Slave updated data 10 17 200Primary should have rejected change from Secondary, keeping its value 201select * from t1 order by a; 202a b c 2031 2 Master updated data 1 2042 3 Master updated data 2 2053 4 Master updated data 3 2064 5 Master updated data 4 2075 6 Master updated data 5 2086 7 Master updated data 6 2097 8 Master updated data 7 2108 9 Master updated data 8 2119 10 Master updated data 9 21210 11 Master updated data 10 213select * from t2 order by a; 214x a b c d e 21521 1 2 3 Master updated data 1 17 21621 2 3 4 Master updated data 2 17 21721 3 4 5 Master updated data 3 17 21821 4 5 6 Master updated data 4 17 21921 5 6 7 Master updated data 5 17 22021 6 7 8 Master updated data 6 17 22121 7 8 9 Master updated data 7 17 22221 8 9 10 Master updated data 8 17 22321 9 10 11 Master updated data 9 17 22421 10 11 12 Master updated data 10 17 225start slave; 226Secondary should have been realigned to Primary 227Slave contents 228select * from t1 order by a; 229a b c 2301 2 Master updated data 1 2312 3 Master updated data 2 2323 4 Master updated data 3 2334 5 Master updated data 4 2345 6 Master updated data 5 2356 7 Master updated data 6 2367 8 Master updated data 7 2378 9 Master updated data 8 2389 10 Master updated data 9 23910 11 Master updated data 10 240select * from t2 order by a; 241x a b c d e 24221 1 2 3 Master updated data 1 17 24321 2 3 4 Master updated data 2 17 24421 3 4 5 Master updated data 3 17 24521 4 5 6 Master updated data 4 17 24621 5 6 7 Master updated data 5 17 24721 6 7 8 Master updated data 6 17 24821 7 8 9 Master updated data 7 17 24921 8 9 10 Master updated data 8 17 25021 9 10 11 Master updated data 9 17 25121 10 11 12 Master updated data 10 17 252Show rollback of whole secondary transaction 253-------------------------------------------- 254flush logs; 255Slave contents 256select * from t1 order by a; 257a b c 2581 2 Master updated data 1 2592 3 Master updated data 2 2603 4 Master updated data 3 2614 5 Master updated data 4 2625 6 Master updated data 5 2636 7 Master updated data 6 2647 8 Master updated data 7 2658 9 Master updated data 8 2669 10 Master updated data 9 26710 11 Master updated data 10 268select * from t2 order by a; 269x a b c d e 27021 1 2 3 Master updated data 1 17 27121 2 3 4 Master updated data 2 17 27221 3 4 5 Master updated data 3 17 27321 4 5 6 Master updated data 4 17 27421 5 6 7 Master updated data 5 17 27521 6 7 8 Master updated data 6 17 27621 7 8 9 Master updated data 7 17 27721 8 9 10 Master updated data 8 17 27821 9 10 11 Master updated data 9 17 27921 10 11 12 Master updated data 10 17 280select count(*) from t1$EX; 281count(*) 28210 283select NDB$OP_TYPE,NDB$CFT_CAUSE from t1$EX 284order by a; 285NDB$OP_TYPE NDB$CFT_CAUSE 286UPDATE_ROW DATA_IN_CONFLICT 287UPDATE_ROW DATA_IN_CONFLICT 288UPDATE_ROW DATA_IN_CONFLICT 289UPDATE_ROW DATA_IN_CONFLICT 290UPDATE_ROW DATA_IN_CONFLICT 291UPDATE_ROW DATA_IN_CONFLICT 292UPDATE_ROW DATA_IN_CONFLICT 293UPDATE_ROW DATA_IN_CONFLICT 294UPDATE_ROW DATA_IN_CONFLICT 295UPDATE_ROW DATA_IN_CONFLICT 296select count(*) from t2$ex; 297count(*) 29810 299select a, b, c, d, d$old, d$new, e, ndb$count > 0,ndb$op_type,ndb$cft_cause, ndb$orig_transid > 0 from t2$ex order by a; 300a b c d d$old d$new e ndb$count > 0 ndb$op_type ndb$cft_cause ndb$orig_transid > 0 3011 2 3 Slave updated data 1 Initial data 1 Slave updated data 1 17 1 update_row trans_in_conflict 1 3022 3 4 Slave updated data 2 Initial data 2 Slave updated data 2 17 1 update_row trans_in_conflict 1 3033 4 5 Slave updated data 3 Initial data 3 Slave updated data 3 17 1 update_row trans_in_conflict 1 3044 5 6 Slave updated data 4 Initial data 4 Slave updated data 4 17 1 update_row trans_in_conflict 1 3055 6 7 Slave updated data 5 Initial data 5 Slave updated data 5 17 1 update_row trans_in_conflict 1 3066 7 8 Slave updated data 6 Initial data 6 Slave updated data 6 17 1 update_row trans_in_conflict 1 3077 8 9 Slave updated data 7 Initial data 7 Slave updated data 7 17 1 update_row trans_in_conflict 1 3088 9 10 Slave updated data 8 Initial data 8 Slave updated data 8 17 1 update_row trans_in_conflict 1 3099 10 11 Slave updated data 9 Initial data 9 Slave updated data 9 17 1 update_row trans_in_conflict 1 31010 11 12 Slave updated data 10 Initial data 10 Slave updated data 10 17 1 update_row trans_in_conflict 1 311SET NAMES utf8; 312replace into mysql.ndb_replication values 313("test", "ｱｱｱ", 3, 7, NULL), 314("test", "ｱｱｱ", 1, 7, "NDB$EPOCH_TRANS()"); 315create table test.`ｱｱｱ$ex` 316 (ndb$server_id int unsigned, 317ndb$master_server_id int unsigned, 318ndb$master_epoch bigint unsigned, 319ndb$count int unsigned, 320`ｷｷｷ` varchar(2000) charset utf8, 321`ｶｶｶ` int not null, 322ndb$op_type enum('write_row','update_row', 'delete_row') not null, 323ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 324ndb$orig_transid bigint unsigned not null, 325primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 326engine=ndb; 327create table test.`ｱｱｱ` ( 328`ｶｶｶ` int primary key, 329`ｷｷｷ` varchar(2000) charset utf8) engine=ndb; 330SET NAMES latin1; 331MySQLD error output for server 1.1 matching pattern %suspicious % column op_type % 332relevant 333SET NAMES utf8; 334drop table test.`ｱｱｱ`,test.`ｱｱｱ$ex`; 335SET NAMES utf8; 336replace into mysql.ndb_replication values 337("test", "ｱｱｱ", 3, 7, NULL), 338("test", "ｱｱｱ", 1, 7, "NDB$EPOCH_TRANS()"); 339create table test.`ｱｱｱ$ex` 340 (ndb$server_id int unsigned, 341ndb$master_server_id int unsigned, 342ndb$master_epoch bigint unsigned, 343ndb$count int unsigned, 344`ｷｷｷ$old` varchar(2000) charset utf8, 345`ｷｷｷ$new` varchar(2000) charset utf8, 346`ｶｶｶ` int not null, 347ndb$op_type enum('write_row','update_row', 'delete_row') not null, 348ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 349ndb$orig_transid bigint unsigned not null, 350primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 351engine=ndb; 352create table test.`ｱｱｱ` ( 353`ｶｶｶ` int primary key, 354`ｷｷｷ` varchar(2000) charset utf8) engine=ndb; 355SET NAMES latin1; 356MySQLD error output for server 1.1 matching pattern %suspicious % column op_type % 357relevant 358flush logs; 359drop table mysql.ndb_replication; 360drop table test.t1$EX, test.t1, test.t2$ex, test.t2; 361SET NAMES utf8; 362drop table test.`ｱｱｱ`, test.`ｱｱｱ$ex`; 363SET NAMES latin1; 364flush logs; 365stop slave; 366reset slave; 367include/rpl_end.inc 368