1# 2# Test engine native conflict resolution for ndb 3# NDB$EPOCH_TRANS() function 4# 5# 6--source include/have_utf8.inc 7--source include/have_ndb.inc 8--source include/have_binlog_format_mixed_or_row.inc 9--source suite/ndb_rpl/ndb_master-slave.inc 10--source suite/ndb_rpl/t/ndb_trans_conflict_info_init.inc 11 12--echo Setup circular replication 13 14--connection slave 15RESET MASTER; 16let $SLAVE_SERVER_ID= `select @@server_id`; 17echo SLAVE_SERVER_ID: $SLAVE_SERVER_ID; 18 19--connection master 20--replace_result $SLAVE_MYPORT SLAVE_PORT 21--eval CHANGE MASTER TO master_host="127.0.0.1",master_port=$SLAVE_MYPORT,master_user="root" 22START SLAVE; 23let $MASTER_SERVER_ID= `select @@server_id`; 24echo MASTER_SERVER_ID: $MASTER_SERVER_ID; 25 26--echo Setup ndb_replication and exceptions tables 27 28--disable_warnings 29--disable_query_log 30--connection master 31drop table if exists mysql.ndb_replication; 32CREATE TABLE mysql.ndb_replication 33 (db VARBINARY(63), 34 table_name VARBINARY(63), 35 server_id INT UNSIGNED, 36 binlog_type INT UNSIGNED, 37 conflict_fn VARBINARY(128), 38 PRIMARY KEY USING HASH (db,table_name,server_id)) 39 ENGINE=NDB PARTITION BY KEY(db,table_name); 40--enable_warnings 41--enable_query_log 42 43--echo Populate ndb_replication table as necessary 44eval replace into mysql.ndb_replication values 45 ("test", "t1", $SLAVE_SERVER_ID, 7, NULL), 46 ("test", "t1", $MASTER_SERVER_ID, 7, "NDB\$EPOCH()"), 47 ("test", "t2", $SLAVE_SERVER_ID, 7, NULL), 48 ("test", "t2", $MASTER_SERVER_ID, 7, "NDB\$EPOCH_TRANS()"); 49 50# 51# Test illegal exception table definitions 52# 53call mtr.add_suppression("NDB Slave: exceptions table .* has wrong definition .*"); 54call mtr.add_suppression("Exceptions table *"); 55 56# Check that all mandatory columns need NDB$ suffix 57 58create table test.t1$EX 59 (SERVER_ID int unsigned, 60 NDB$MASTER_SERVER_ID int unsigned, 61 NDB$MASTER_EPOCH bigint unsigned, 62 NDB$COUNT int unsigned, 63 a int not null, 64 NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 65 NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 66 primary key(SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 67ENGINE=NDB; 68 69create table test.t1 ( 70 a int not null, 71 b int not null, 72 c varchar(2000), 73 primary key (a,b)) engine=ndb; 74 75--let server_num=1.1 76--let $pattern=%Exceptions table % 77--let $limit=1 78# Note warning is displayed in lower case, real warning is correct 79--source suite/ndb_rpl/t/show_mysqld_warnings.inc 80 81drop table test.t1$EX, test.t1; 82 83# Check that referencing old/new values of primary key is not supported 84 85create table test.t1$EX 86 (NDB$SERVER_ID int unsigned, 87 NDB$MASTER_SERVER_ID int unsigned, 88 NDB$MASTER_EPOCH bigint unsigned, 89 NDB$COUNT int unsigned, 90 a$old int, 91 a$new int, 92 NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 93 NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 94 primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 95ENGINE=NDB; 96 97create table test.t1 ( 98 a int not null, 99 b int not null, 100 c varchar(2000), 101 primary key (a,b)) engine=ndb; 102 103--let server_num=1.1 104--let $pattern=%Exceptions table % 105--let $limit=1 106# Note warning is displayed in lower case, real warning is correct 107--source suite/ndb_rpl/t/show_mysqld_warnings.inc 108 109drop table test.t1$EX, test.t1; 110 111# Check that referencing old/new values allow for null values 112 113create table test.t1$EX 114 (NDB$SERVER_ID int unsigned, 115 NDB$MASTER_SERVER_ID int unsigned, 116 NDB$MASTER_EPOCH bigint unsigned, 117 NDB$COUNT int unsigned, 118 c$old varchar(2000) not null, 119 c$new varchar(2000) not null, 120 NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 121 NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 122 primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 123ENGINE=NDB; 124 125create table test.t1 ( 126 a int not null, 127 b int not null, 128 c varchar(2000), 129 primary key (a,b)) engine=ndb; 130 131--let server_num=1.1 132--let $pattern=%Exceptions table % 133--let $limit=1 134# Note warning is displayed in lower case, real warning is correct 135--source suite/ndb_rpl/t/show_mysqld_warnings.inc 136 137drop table test.t1$EX, test.t1; 138 139create table test.t1$EX 140 (NDB$SERVER_ID int unsigned, 141 NDB$MASTER_SERVER_ID int unsigned, 142 NDB$MASTER_EPOCH bigint unsigned, 143 NDB$COUNT int unsigned, 144 a int not null, 145 NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW', 'DELETE_ROW') NOT NULL, 146 NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, 147 primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) 148ENGINE=NDB; 149 150create table test.t1 ( 151 a int not null, 152 b int not null, 153 c varchar(2000), 154 primary key (a,b)) engine=ndb; 155 156# 157# Test different column order than original table 158# for both primary key and non primary columns. 159# Test non primary key column declared not null, 160# but with a default value. 161# 162create table test.t2$ex 163 (ndb$server_id int unsigned, 164 ndb$master_server_id int unsigned, 165 ndb$master_epoch bigint unsigned, 166 ndb$count int unsigned, 167 d varchar(2000), 168 c int not null default 99, 169 b int not null, 170 a int not null, 171 d$old varchar(2000), 172 d$new varchar(2000), 173 ndb$op_type enum('write_row','update_row', 'delete_row') not null, 174 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 175 ndb$orig_transid bigint unsigned not null, 176 e int, 177 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 178engine=ndb; 179 180create table test.t2 ( 181 x int default 21, 182 a int not null, 183 b int not null, 184 c int, 185 d varchar(2000), 186 e int, 187 primary key(a,b)) engine=ndb; 188 189 190--sync_slave_with_master slave 191 192--connection master 193--echo Add some data 194let $1=10; 195disable_query_log; 196while ($1) 197{ 198 eval insert into test.t1 values($1, $1+1, concat("Initial data ",$1)); 199 eval insert into test.t2(a,b,c,d,e) values($1, $1+1, $1+2, concat("Initial data ",$1),17); 200 dec $1; 201} 202enable_query_log; 203 204--sync_slave_with_master slave 205 206--connection slave 207stop slave; 208 209--connection slave 210--echo Slave contents 211select * from t1 order by a; 212select * from test.t2 order by a; 213 214--connection master 215let $1=10; 216disable_query_log; 217while ($1) 218{ 219 eval update test.t1 set c = concat("Master updated data ",$1) where a = $1; 220 begin; 221 eval update test.t2 set d = concat("Master updated data ",$1) where a = $1 and b = $1+1; 222 commit; 223 dec $1; 224} 225enable_query_log; 226 227--connection slave 228let $1=10; 229disable_query_log; 230while ($1) 231{ 232 eval update test.t1 set c = concat("Slave updated data ",$1) where a = $1; 233 begin; 234 eval update test.t2 set d = concat("Slave updated data ",$1) where a = $1 and b = $1+1; 235 commit; 236 dec $1; 237} 238enable_query_log; 239 240--connection master 241--echo Master contents 242select * from test.t1 order by a; 243select * from test.t2 order by a; 244 245--connection slave 246--echo Slave contents 247select * from test.t1 order by a; 248select * from test.t2 order by a; 249 250 251--sync_slave_with_master master 252 253--echo Primary should have rejected change from Secondary, keeping its value 254 255select * from t1 order by a; 256select * from t2 order by a; 257 258--connection slave 259start slave; 260 261--connection master 262 263--sync_slave_with_master slave 264 265--connection slave 266 267--echo Secondary should have been realigned to Primary 268--echo Slave contents 269select * from t1 order by a; 270select * from t2 order by a; 271 272--echo Show rollback of whole secondary transaction 273--echo -------------------------------------------- 274 275--connection slave 276flush logs; 277--sync_slave_with_master master 278--echo Slave contents 279select * from t1 order by a; 280select * from t2 order by a; 281 282--connection master 283select count(*) from t1$EX; 284select NDB$OP_TYPE,NDB$CFT_CAUSE from t1$EX 285order by a; 286select count(*) from t2$ex; 287select 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; 288 289 290# 291# Test using table name and column names in utf8 292# 293SET NAMES utf8; 294 295eval replace into mysql.ndb_replication values 296 ("test", "ｱｱｱ", $SLAVE_SERVER_ID, 7, NULL), 297 ("test", "ｱｱｱ", $MASTER_SERVER_ID, 7, "NDB\$EPOCH_TRANS()"); 298 299create table test.`ｱｱｱ$ex` 300 (ndb$server_id int unsigned, 301 ndb$master_server_id int unsigned, 302 ndb$master_epoch bigint unsigned, 303 ndb$count int unsigned, 304 `ｷｷｷ` varchar(2000) charset utf8, 305 `ｶｶｶ` int not null, 306 ndb$op_type enum('write_row','update_row', 'delete_row') not null, 307 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 308 ndb$orig_transid bigint unsigned not null, 309 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 310engine=ndb; 311 312create table test.`ｱｱｱ` ( 313 `ｶｶｶ` int primary key, 314 `ｷｷｷ` varchar(2000) charset utf8) engine=ndb; 315 316SET NAMES latin1; 317 318--let $pattern=%suspicious % column op_type % 319--let $limit=1 320--source suite/ndb_rpl/t/show_mysqld_warnings.inc 321 322SET NAMES utf8; 323drop table test.`ｱｱｱ`,test.`ｱｱｱ$ex`; 324 325# 326# Test referencing old/new values of column names in utf8 327# 328SET NAMES utf8; 329 330eval replace into mysql.ndb_replication values 331 ("test", "ｱｱｱ", $SLAVE_SERVER_ID, 7, NULL), 332 ("test", "ｱｱｱ", $MASTER_SERVER_ID, 7, "NDB\$EPOCH_TRANS()"); 333 334create table test.`ｱｱｱ$ex` 335 (ndb$server_id int unsigned, 336 ndb$master_server_id int unsigned, 337 ndb$master_epoch bigint unsigned, 338 ndb$count int unsigned, 339 `ｷｷｷ$old` varchar(2000) charset utf8, 340 `ｷｷｷ$new` varchar(2000) charset utf8, 341 `ｶｶｶ` int not null, 342 ndb$op_type enum('write_row','update_row', 'delete_row') not null, 343 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null, 344 ndb$orig_transid bigint unsigned not null, 345 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) 346engine=ndb; 347 348create table test.`ｱｱｱ` ( 349 `ｶｶｶ` int primary key, 350 `ｷｷｷ` varchar(2000) charset utf8) engine=ndb; 351 352SET NAMES latin1; 353 354--let $pattern=%suspicious % column op_type % 355--let $limit=1 356--source suite/ndb_rpl/t/show_mysqld_warnings.inc 357 358--connection slave 359flush logs; 360--sync_slave_with_master master 361 362# Cleanup 363--connection master 364drop table mysql.ndb_replication; 365drop table test.t1$EX, test.t1, test.t2$ex, test.t2; 366SET NAMES utf8; 367drop table test.`ｱｱｱ`, test.`ｱｱｱ$ex`; 368SET NAMES latin1; 369--sync_slave_with_master slave 370 371--connection slave 372flush logs; 373--sync_slave_with_master master 374stop slave; 375reset slave; 376#change master to master_host=''; 377--source include/rpl_end.inc 378