1--source include/have_debug.inc 2--source include/have_rocksdb.inc 3--source include/master-slave.inc 4 5 6# initialization/insert 7--source include/rpl_connection_master.inc 8create table t1 (id int primary key, value int) Engine=RocksDB; 9insert into t1 values (1,1), (2,2), (3,3), (4,4); 10--source include/sync_slave_sql_with_master.inc 11 12--let $diff_tables= master:t1, slave:t1 13 14--echo 15--echo # regular update/delete. With rocks_read_free_rpl=PK_SK, rocksdb_rows_read does not increase on slaves 16--echo 17--source include/rpl_connection_slave.inc 18select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 19--source include/rpl_connection_master.inc 20update t1 set value=value+1 where id=1; 21delete from t1 where id=4; 22select * from t1; 23--source include/sync_slave_sql_with_master.inc 24--source include/rpl_connection_slave.inc 25select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 26select * from t1; 27 28 29--echo 30--echo # "rocks_read_free_rpl=PK_SK" makes "row not found error" not happen anymore 31--echo 32--source include/rpl_connection_slave.inc 33--source include/stop_slave.inc 34delete from t1 where id in (2, 3); 35--source include/start_slave.inc 36select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 37 38--source include/rpl_connection_master.inc 39update t1 set value=value+1 where id=3; 40delete from t1 where id=2; 41select * from t1; 42--source include/sync_slave_sql_with_master.inc 43--source include/rpl_connection_slave.inc 44select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 45select * from t1; 46 47 48--echo 49--echo ## tables without primary key -- read free replication should be disabled 50--echo 51--echo 52--echo #no index 53--echo 54--source include/rpl_connection_master.inc 55drop table t1; 56create table t1 (c1 int, c2 int) Engine=RocksDB; 57insert into t1 values (1,1), (2,2),(3,3),(4,4),(5,5); 58--source include/sync_slave_sql_with_master.inc 59--source include/rpl_connection_slave.inc 60select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 61--source include/rpl_connection_master.inc 62update t1 set c2=100 where c1=3; 63delete from t1 where c1 <= 2; 64--source include/sync_slave_sql_with_master.inc 65--source include/rpl_connection_slave.inc 66select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 67select * from t1; 68 69--echo 70--echo #secondary index only 71--echo 72--source include/rpl_connection_master.inc 73drop table t1; 74create table t1 (c1 int, c2 int, index i(c1)) Engine=RocksDB; 75insert into t1 values (1,1), (2,2),(3,3),(4,4),(5,5); 76--source include/sync_slave_sql_with_master.inc 77--source include/rpl_connection_slave.inc 78select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 79--source include/rpl_connection_master.inc 80update t1 set c2=100 where c1=3; 81delete from t1 where c1 <= 2; 82--source include/sync_slave_sql_with_master.inc 83--source include/rpl_connection_slave.inc 84select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 85select * from t1; 86 87 88 89--echo 90--echo ## large row operations -- primary key modification, secondary key modification 91--echo 92--source include/rpl_connection_master.inc 93drop table t1; 94create table t1 (id1 bigint, id2 bigint, c1 bigint, c2 bigint, c3 bigint, c4 bigint, c5 bigint, c6 bigint, c7 bigint, primary key (id1, id2), index i(c1, c2)) Engine=RocksDB; 95 96--disable_query_log 97let $i=1; 98while ($i<=10000) 99{ 100 eval insert t1(id1,id2,c1,c2,c3,c4,c5,c6,c7) 101 values($i,0,$i,0,0,0,0,0,0); 102 inc $i; 103} 104--enable_query_log 105 106--source include/sync_slave_sql_with_master.inc 107--source include/rpl_connection_slave.inc 108select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 109--source include/rpl_connection_master.inc 110 111--echo 112--echo #updating all secondary keys by 1 113--echo 114--disable_query_log 115let $i=1; 116while ($i<=10000) 117{ 118 eval update t1 set c2=c2+1 where id1=$i and id2=0; 119 inc $i; 120} 121--enable_query_log 122--source include/sync_slave_sql_with_master.inc 123--source include/rpl_connection_slave.inc 124select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 125--source include/rpl_connection_master.inc 126--source include/diff_tables.inc 127 128--echo 129--echo #updating all primary keys by 2 130--echo 131--source include/rpl_connection_slave.inc 132select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 133--source include/rpl_connection_master.inc 134--disable_query_log 135let $i=1; 136while ($i<=10000) 137{ 138 eval update t1 set id2=id2+2 where id1=$i and id2=0; 139 inc $i; 140} 141--enable_query_log 142--source include/sync_slave_sql_with_master.inc 143--source include/rpl_connection_slave.inc 144select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 145--source include/rpl_connection_master.inc 146--source include/diff_tables.inc 147 148--echo 149--echo #updating secondary keys after truncating t1 on slave 150--echo 151--source include/rpl_connection_slave.inc 152truncate table t1; 153select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 154--source include/rpl_connection_master.inc 155update t1 set c2=c2+10; 156--source include/sync_slave_sql_with_master.inc 157--source include/rpl_connection_slave.inc 158select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 159--source include/rpl_connection_master.inc 160--source include/diff_tables.inc 161 162--echo 163--echo #updating primary keys after truncating t1 on slave 164--echo 165--source include/rpl_connection_slave.inc 166truncate table t1; 167select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 168--source include/rpl_connection_master.inc 169update t1 set id2=id2+10; 170--source include/sync_slave_sql_with_master.inc 171--source include/rpl_connection_slave.inc 172select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 173--source include/rpl_connection_master.inc 174--source include/diff_tables.inc 175 176--echo 177--echo #deleting half rows 178--echo 179--source include/rpl_connection_slave.inc 180select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 181--source include/rpl_connection_master.inc 182delete from t1 where id1 <= 5000; 183--source include/sync_slave_sql_with_master.inc 184--source include/rpl_connection_slave.inc 185select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 186--source include/rpl_connection_master.inc 187--source include/diff_tables.inc 188 189--echo 190--echo # rocksdb_read_free_rpl = PK_ONLY i.e. it only works on tables with only PK 191--echo 192--source include/rpl_connection_slave.inc 193--echo [on slave] 194--source include/stop_slave.inc 195set @@global.rocksdb_read_free_rpl = PK_ONLY; 196--source include/start_slave.inc 197--source include/rpl_connection_master.inc 198--echo [on master] 199create table t2 (id int primary key, i1 int, i2 int, value int) Engine=RocksDB; 200create table u2 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)) Engine=RocksDB; 201insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 202insert into u2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 203--source include/sync_slave_sql_with_master.inc 204 205# make a mismatch between the slave and the master 206--source include/rpl_connection_slave.inc 207--echo [on slave] 208delete from t2 where id <= 2; 209delete from u2 where id <= 2; 210 211# make changes on the master 212--source include/rpl_connection_master.inc 213--echo [on master] 214update t2 set i2=100, value=100 where id=1; 215update u2 set i2=100, value=100 where id=1; 216 217--source include/rpl_connection_slave.inc 218--echo [on slave] 219call mtr.add_suppression("Slave SQL.*Could not execute Update_rows event on table test.u2.*Error_code.*"); 220call mtr.add_suppression("Slave: Can't find record in 'u2'.*"); 221# wait until we have the expected error 222--let $slave_sql_errno= convert_error(ER_KEY_NOT_FOUND) 223--source include/wait_for_slave_sql_error.inc 224 225# query the t2 table on the slave 226--source include/rpl_connection_slave.inc 227select count(*) from t2 force index(primary); 228select * from t2 where id=1; 229select i1 from t2 where i1=1; 230select i2 from t2 where i2=100; 231 232# query the u2 table on the slave 233select count(*) from u2 force index(primary); 234select count(*) from u2 force index(i1); 235select count(*) from u2 force index(i2); 236select * from u2 where id=1; 237select i1 from u2 where i1=1; 238select i2 from u2 where i2=100; 239 240# the slave replication thread stopped because of the errors; 241# cleanup the problem and restart it 242--disable_query_log 243insert into u2 values(1,1,1,1), (2,2,2,2); 244start slave sql_thread; 245--source include/wait_for_slave_sql_to_start.inc 246--enable_query_log 247 248--source include/rpl_connection_slave.inc 249--echo [on slave] 250--source include/stop_slave.inc 251set @@global.rocksdb_read_free_rpl = PK_SK; 252--source include/start_slave.inc 253 254if (0) { # Percona Server disabled rocksdb_read_free_rpl_tables as it's dangerous to use 255--echo 256--echo # some tables with read-free replication on and some with it off 257--echo 258# We'll set the table filter to all tables starting with 't' 259--source include/rpl_connection_slave.inc 260--echo [on slave] 261--source include/stop_slave.inc 262set @@global.rocksdb_read_free_rpl_tables = "t.*"; 263--source include/start_slave.inc 264--source include/rpl_connection_master.inc 265--echo [on master] 266drop table if exists t2; 267drop table if exists u2; 268create table t2 (id int primary key, i1 int, i2 int, value int) Engine=RocksDB; 269create table u2 (id int primary key, i1 int, i2 int, value int) Engine=RocksDB; 270insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 271insert into u2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 272--source include/sync_slave_sql_with_master.inc 273 274# make a mismatch between the slave and the master 275--source include/rpl_connection_slave.inc 276--echo [on slave] 277delete from t2 where id <= 2; 278delete from u2 where id <= 2; 279 280# make changes on the master 281--source include/rpl_connection_master.inc 282--echo [on master] 283update t2 set i2=100, value=100 where id=1; 284update u2 set i2=100, value=100 where id=1; 285 286--source include/rpl_connection_slave.inc 287--echo [on slave] 288call mtr.add_suppression("Slave SQL.*Could not execute Update_rows event on table test.u2.*Error_code.*"); 289call mtr.add_suppression("Slave: Can't find record in 'u2'.*"); 290# wait until we have the expected error 291--let $slave_sql_errno= convert_error(ER_KEY_NOT_FOUND) 292--source include/wait_for_slave_sql_error.inc 293 294# query the t2 table on the slave 295--source include/rpl_connection_slave.inc 296select count(*) from t2 force index(primary); 297select * from t2 where id=1; 298select i1 from t2 where i1=1; 299select i2 from t2 where i2=100; 300 301# query the u2 table on the slave 302select count(*) from u2 force index(primary); 303select * from u2 where id=1; 304select i1 from u2 where i1=1; 305select i2 from u2 where i2=100; 306 307# the slave replication thread stopped because of the errors; 308# cleanup the problem and restart it 309--disable_query_log 310insert into u2 values(1,1,1,1), (2,2,2,2); 311start slave sql_thread; 312--source include/wait_for_slave_sql_to_start.inc 313--enable_query_log 314 315--source include/rpl_connection_slave.inc 316--echo [on slave] 317--source include/stop_slave.inc 318set @@global.rocksdb_read_free_rpl_tables = ".*"; 319--source include/start_slave.inc 320} # if (0) 321 322--echo 323--echo # secondary keys lose rows 324--echo 325--source include/rpl_connection_master.inc 326--echo [on master] 327create table t3 (id int primary key, i1 int, i2 int, value int, index(i1), 328index(i2)) Engine=RocksDB; 329insert into t3 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 330--source include/sync_slave_sql_with_master.inc 331 332# make a mismatch between the slave and the master 333--source include/rpl_connection_slave.inc 334--echo [on slave] 335delete from t3 where id <= 2; 336 337# make changes on the master 338--source include/rpl_connection_master.inc 339--echo [on master] 340update t3 set i2=100, value=100 where id=1; 341 342# make sure the slave is caught up 343--source include/sync_slave_sql_with_master.inc 344 345# query the t3 table on the slave 346--source include/rpl_connection_slave.inc 347select count(*) from t3 force index(primary); 348select count(*) from t3 force index(i1); 349select count(*) from t3 force index(i2); 350select * from t3 where id=1; 351select i1 from t3 where i1=1; 352select i2 from t3 where i2=100; 353 354--echo 355--echo # secondary keys have extra rows 356--echo 357--source include/rpl_connection_master.inc 358--echo [on master] 359create table t4 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)) Engine=RocksDB; 360insert into t4 values (1,1,1,1),(2,2,2,2),(3,3,3,3); 361--source include/sync_slave_sql_with_master.inc 362 363# make a mismatch between the slave and the master 364--source include/rpl_connection_slave.inc 365--echo [on slave] 366update t4 set i1=100 where id=1; 367 368# make changes on the master 369--source include/rpl_connection_master.inc 370--echo [on master] 371delete from t4 where id=1; 372 373# make sure the slave is caught up 374--source include/sync_slave_sql_with_master.inc 375 376# query the t4 table on the slave 377--source include/rpl_connection_slave.inc 378--echo [on slave] 379select count(*) from t4 force index(primary); 380select count(*) from t4 force index(i1); 381select count(*) from t4 force index(i2); 382select i1 from t4 where i1=100; 383 384--echo 385--echo # inserts are also read-free 386--echo 387--source include/rpl_connection_master.inc 388--echo [on master] 389drop table if exists t2; 390drop table if exists t3; 391create table t2 (id int primary key, i1 int, i2 int) Engine=RocksDB; 392create table t3 (id int primary key, i1 int, i2 int, key(i1)) Engine=RocksDB; 393--source include/rpl_connection_slave.inc 394select variable_value into @up from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 395--source include/rpl_connection_master.inc 396insert into t2 values(1, 1, 1); 397insert into t2 values(2, 2, 2); 398insert into t3 values(1, 1, 1); 399insert into t3 values(2, 2, 2); 400--source include/sync_slave_sql_with_master.inc 401--source include/rpl_connection_slave.inc 402select case when variable_value-@up > 0 then 'false' else 'true' end as read_free from performance_schema.global_status where variable_name='rocksdb_num_get_for_update_calls'; 403select * from t2; 404select * from t3; 405 406# cleanup 407--source include/rpl_connection_master.inc 408drop table t1, t2, t3, t4, u2; 409 410--source include/rpl_end.inc 411 412