EVOLUTION-MANAGER

Edit File: rpl_read_free.result

include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### 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. [connection master] drop table if exists t1; create procedure save_read_stats() begin select rows_requested into @rq from information_schema.table_statistics where table_schema=database() and table_name='t1'; select variable_value into @rr from information_schema.global_status where variable_name='rocksdb_rows_read'; select variable_value into @ru from information_schema.global_status where variable_name='rocksdb_rows_updated'; select variable_value into @rd from information_schema.global_status where variable_name='rocksdb_rows_deleted'; end// create procedure get_read_stats() begin select rows_requested - @rq as rows_requested from information_schema.table_statistics where table_schema=database() and table_name='t1'; select variable_value - @rr as rows_read from information_schema.global_status where variable_name='rocksdb_rows_read'; select variable_value - @ru as rows_updated from information_schema.global_status where variable_name='rocksdb_rows_updated'; select variable_value - @rd as rows_deleted from information_schema.global_status where variable_name='rocksdb_rows_deleted'; end// create table t1 (id int primary key, value int); insert into t1 values (1,1), (2,2), (3,3), (4,4); include/sync_slave_sql_with_master.inc # regular update/delete. With rocks_read_free_rpl_tables=.*, rocksdb_rows_read does not increase on slaves call save_read_stats(); update t1 set value=value+1 where id=1; delete from t1 where id=4; select * from t1; id value 1 2 2 2 3 3 include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 1 rows_deleted 1 select * from t1; id value 1 2 2 2 3 3 # "rocks_read_free_rpl_tables=.*" makes "row not found error" not happen anymore include/stop_slave.inc delete from t1 where id in (2, 3); include/start_slave.inc call save_read_stats(); update t1 set value=value+1 where id=3; delete from t1 where id=2; select * from t1; id value 1 2 3 4 include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 1 rows_deleted 1 select * from t1; id value 1 2 3 4 ## tables without primary key -- read free replication should be disabled #no index drop table t1; create table t1 (c1 int, c2 int); insert into t1 values (1,1), (2,2),(3,3),(4,4),(5,5); include/sync_slave_sql_with_master.inc call save_read_stats(); update t1 set c2=100 where c1=3; delete from t1 where c1 <= 2; include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 5 rows_read 5 rows_updated 1 rows_deleted 2 select * from t1; c1 c2 3 100 4 4 5 5 #secondary index only drop table t1; create table t1 (c1 int, c2 int, index i(c1)); insert into t1 values (1,1), (2,2),(3,3),(4,4),(5,5); include/sync_slave_sql_with_master.inc call save_read_stats(); update t1 set c2=100 where c1=3; delete from t1 where c1 <= 2; include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 3 rows_read 3 rows_updated 1 rows_deleted 2 select * from t1; c1 c2 3 100 4 4 5 5 ## large row operations -- primary key modification, secondary key modification drop table t1; create 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)); include/sync_slave_sql_with_master.inc call save_read_stats(); #updating all seconary keys by 1 include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 10000 rows_deleted 0 include/diff_tables.inc [master:t1, slave:t1] #updating all primary keys by 2 call save_read_stats(); include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 10000 rows_deleted 0 include/diff_tables.inc [master:t1, slave:t1] #updating secondary keys after truncating t1 on slave truncate table t1; call save_read_stats(); update t1 set c2=c2+10; include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 10000 rows_deleted 0 include/diff_tables.inc [master:t1, slave:t1] #updating primary keys after truncating t1 on slave truncate table t1; call save_read_stats(); update t1 set id2=id2+10; include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 10000 rows_deleted 0 include/diff_tables.inc [master:t1, slave:t1] #deleting half rows call save_read_stats(); delete from t1 where id1 <= 5000; include/sync_slave_sql_with_master.inc call get_read_stats(); rows_requested 0 rows_read 0 rows_updated 0 rows_deleted 5000 include/diff_tables.inc [master:t1, slave:t1] [on master] create table t2 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)); create table u2 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)); insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); insert into u2 values (1,1,1,1),(2,2,2,2),(3,3,3,3); include/sync_slave_sql_with_master.inc [on slave] delete from t2 where id <= 2; delete from u2 where id <= 2; [on master] update t2 set i2=100, value=100 where id=1; update u2 set i2=100, value=100 where id=1; [on slave] call mtr.add_suppression("Slave SQL.*Could not execute Update_rows event on table test.u2.*Error_code.*"); call mtr.add_suppression("Slave: Can't find record in 'u2'.*"); include/wait_for_slave_sql_error.inc [errno=1032] select count(*) from t2 force index(primary); count(*) 2 select count(*) from t2 force index(i1); count(*) 1 select count(*) from t2 force index(i2); count(*) 2 select * from t2 where id=1; id i1 i2 value 1 1 100 100 select i1 from t2 where i1=1; i1 select i2 from t2 where i2=100; i2 100 select count(*) from u2 force index(primary); count(*) 1 select count(*) from u2 force index(i1); count(*) 1 select count(*) from u2 force index(i2); count(*) 1 select * from u2 where id=1; id i1 i2 value select i1 from u2 where i1=1; i1 select i2 from u2 where i2=100; i2 include/wait_for_slave_sql_to_start.inc # some tables with read-free replication on and some with it off # secondary keys have extra rows [on master] create table t3 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)); create table u3 (id int primary key, i1 int, i2 int, value int, index(i1), index(i2)); insert into t3 values (1,1,1,1),(2,2,2,2),(3,3,3,3); insert into u3 values (1,1,1,1),(2,2,2,2),(3,3,3,3); include/sync_slave_sql_with_master.inc [on slave] update t3 set i1=100 where id=1; update u3 set i1=100 where id=1; [on master] delete from t3 where id=1; delete from u3 where id=1; include/sync_slave_sql_with_master.inc [on slave] select count(*) from t3 force index(primary); count(*) 2 select count(*) from t3 force index(i1); count(*) 3 select count(*) from t3 force index(i2); count(*) 2 select i1 from t3 where i1=100; i1 100 select count(*) from u3 force index(primary); count(*) 2 select count(*) from u3 force index(i1); count(*) 2 select count(*) from u3 force index(i2); count(*) 2 select i1 from u3 where i1=100; i1 drop table t1, t2, t3, u2, u3; drop procedure save_read_stats; drop procedure get_read_stats; include/rpl_end.inc