- developers - lists.mariadb.org

[Maria-developers] Please review: MWL#121: DS-MRR support for clustered primary keys
by Sergey Petrunya 22 Jun '10

22 Jun '10

Hello Igor, Please find below the combined patch for MWL#121. It is ready for review. diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result --- maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result 1970-01-01 03:00:00.000000000 +0300 +++ maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result 2010-06-22 23:28:02.000000000 +0400 @@ -0,0 +1,148 @@ +drop table if exists t0,t1,t2,t3; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; +set @save_storage_engine=@@storage_engine; +set storage_engine=innodb; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a char(8), b char(8), filler char(100), primary key(a)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(8) NOT NULL DEFAULT '', + `b` char(8) DEFAULT NULL, + `filler` char(100) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8)); +insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); +This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1 Using join buffer +This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +a b filler a +a-1010=A b-1010=B filler a-1010=A +a-1020=A b-1020=B filler a-1020=A +a-1030=A b-1030=B filler a-1030=A +drop table t1, t2; +create table t1( +a char(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1( +a varchar(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); +insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; +insert into t1 values (11, 11, 11, 'filler'); +insert into t1 values (11, 11, 12, 'filler'); +insert into t1 values (11, 11, 13, 'filler'); +insert into t1 values (11, 22, 1234, 'filler'); +insert into t1 values (11, 33, 124, 'filler'); +insert into t1 values (11, 33, 125, 'filler'); +create table t2 (a int, b int); +insert into t2 values (11,33), (11,22), (11,11); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +11 22 1234 filler 11 22 +11 33 124 filler 11 33 +11 33 125 filler 11 33 +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 33 124 filler 11 33 +11 33 125 filler 11 33 +11 22 1234 filler 11 22 +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +set join_cache_level=6; +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +a b c filler a b +set optimizer_switch='index_condition_pushdown=off'; +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +a b c filler a b +set optimizer_switch='index_condition_pushdown=on'; +drop table t1,t2; +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result.moved maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result.moved --- maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result.moved 1970-01-01 03:00:00.000000000 +0300 +++ maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result.moved 2010-06-22 19:23:18.000000000 +0400 @@ -0,0 +1,122 @@ +drop table if exists t0,t1,t2,t3; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; +set @save_storage_engine=@@storage_engine; +set storage_engine=innodb; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a char(8), b char(8), filler char(100), primary key(a)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(8) NOT NULL DEFAULT '', + `b` char(8) DEFAULT NULL, + `filler` char(100) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8)); +insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); +This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1 Using join buffer +This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +a b filler a +a-1010=A b-1010=B filler a-1010=A +a-1020=A b-1020=B filler a-1020=A +a-1030=A b-1030=B filler a-1030=A +drop table t1, t2; +create table t1( +a char(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1( +a varchar(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); +insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; +insert into t1 values (11, 11, 11, 'filler'); +insert into t1 values (11, 11, 12, 'filler'); +insert into t1 values (11, 11, 13, 'filler'); +insert into t1 values (11, 22, 1234, 'filler'); +insert into t1 values (11, 33, 124, 'filler'); +insert into t1 values (11, 33, 125, 'filler'); +create table t2 (a int, b int); +insert into t2 values (11,33), (11,22), (11,11); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +11 22 1234 filler 11 22 +11 33 124 filler 11 33 +11 33 125 filler 11 33 +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 33 124 filler 11 33 +11 33 125 filler 11 33 +11 22 1234 filler 11 22 +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +set join_cache_level=6; +drop table t1,t2; +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/mysql-test/t/innodb_mrr_cpk.test maria-5.3-dsmrr-for-cpk-noc/mysql-test/t/innodb_mrr_cpk.test --- maria-5.3-dsmrr-for-cpk-clean/mysql-test/t/innodb_mrr_cpk.test 1970-01-01 03:00:00.000000000 +0300 +++ maria-5.3-dsmrr-for-cpk-noc/mysql-test/t/innodb_mrr_cpk.test 2010-06-22 23:28:02.000000000 +0400 @@ -0,0 +1,137 @@ +# +# Tests for DS-MRR over clustered primary key. The only engine that supports +# this is InnoDB/XtraDB. +# +# Basic idea about testing +# - DS-MRR/CPK works only with BKA +# - Should also test index condition pushdown +# - Should also test whatever uses RANGE_SEQ_IF::skip_record() for filtering +# - Also test access using prefix of primary key +# +# - Forget about cost model, BKA's multi_range_read_info() call passes 10 for +# #rows, the call is there at all only for applicability check +# +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; + +set @save_storage_engine=@@storage_engine; +set storage_engine=innodb; + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a char(8), b char(8), filler char(100), primary key(a)); +show create table t1; + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8)); +insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); + +--echo This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; + +--echo This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# Try multi-column indexes +create table t1( + a char(8) character set utf8, b int, filler char(100), + primary key(a,b) +); + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + 1000 + A.a + B.a*10 + C.a*100, + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# Try with dataset that causes identical lookup keys: +insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +drop table t1, t2; + +create table t1( + a varchar(8) character set utf8, b int, filler char(100), + primary key(a,b) +); + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + 1000 + A.a + B.a*10 + C.a*100, + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# +# Try scanning on a CPK prefix +# +explain select * from t1, t2 where t1.a=t2.a; +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# +# The above example is not very interesting, as CPK prefix has +# only one match. Create a dataset where scan on CPK prefix +# would produce multiple matches: +# +create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); +insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; + +insert into t1 values (11, 11, 11, 'filler'); +insert into t1 values (11, 11, 12, 'filler'); +insert into t1 values (11, 11, 13, 'filler'); +insert into t1 values (11, 22, 1234, 'filler'); +insert into t1 values (11, 33, 124, 'filler'); +insert into t1 values (11, 33, 125, 'filler'); + +create table t2 (a int, b int); +insert into t2 values (11,33), (11,22), (11,11); + +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# Check a real resultset for comaprison: +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +set join_cache_level=6; + + +# +# Check that Index Condition Pushdown (BKA) actually works: +# +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; + +set optimizer_switch='index_condition_pushdown=off'; +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +set optimizer_switch='index_condition_pushdown=on'; + +drop table t1,t2; + +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; + diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/mysql-test/t/innodb_mrr_cpk.test.moved maria-5.3-dsmrr-for-cpk-noc/mysql-test/t/innodb_mrr_cpk.test.moved --- maria-5.3-dsmrr-for-cpk-clean/mysql-test/t/innodb_mrr_cpk.test.moved 1970-01-01 03:00:00.000000000 +0300 +++ maria-5.3-dsmrr-for-cpk-noc/mysql-test/t/innodb_mrr_cpk.test.moved 2010-06-22 19:23:18.000000000 +0400 @@ -0,0 +1,128 @@ +# +# Tests for DS-MRR over clustered primary key. The only engine that supports +# this is InnoDB/XtraDB. +# +# Basic idea about testing +# - DS-MRR/CPK works only with BKA +# - Should also test index condition pushdown +# - Should also test whatever uses RANGE_SEQ_IF::skip_record() for filtering +# - Also test access using prefix of primary key +# +# - Forget about cost model, BKA's multi_range_read_info() call passes 10 for +# #rows, the call is there at all only for applicability check +# +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; + +set @save_storage_engine=@@storage_engine; +set storage_engine=innodb; + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a char(8), b char(8), filler char(100), primary key(a)); +show create table t1; + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8)); +insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); + +--echo This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; + +--echo This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# Try multi-column indexes +create table t1( + a char(8) character set utf8, b int, filler char(100), + primary key(a,b) +); + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + 1000 + A.a + B.a*10 + C.a*100, + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +drop table t1, t2; + +create table t1( + a varchar(8) character set utf8, b int, filler char(100), + primary key(a,b) +); + +insert into t1 select + concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), + 1000 + A.a + B.a*10 + C.a*100, + 'filler' +from t0 A, t0 B, t0 C; + +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# +# Try scanning on a CPK prefix +# +explain select * from t1, t2 where t1.a=t2.a; +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# +# The above example is not very interesting, as CPK prefix has +# only one match. Create a dataset where scan on CPK prefix +# would produce multiple matches: +# +create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); +insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; + +insert into t1 values (11, 11, 11, 'filler'); +insert into t1 values (11, 11, 12, 'filler'); +insert into t1 values (11, 11, 13, 'filler'); +insert into t1 values (11, 22, 1234, 'filler'); +insert into t1 values (11, 33, 124, 'filler'); +insert into t1 values (11, 33, 125, 'filler'); + +create table t2 (a int, b int); +insert into t2 values (11,33), (11,22), (11,11); + +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +set join_cache_level=6; + +drop table t1,t2; + +# +# Check that Index Condition Pushdown (BKA) actually works: +# + +# TODO + +# +# Check that record-check-func is done: +# + +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; + diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/r/innodb_mrr_cpk.result maria-5.3-dsmrr-for-cpk-noc/r/innodb_mrr_cpk.result --- maria-5.3-dsmrr-for-cpk-clean/r/innodb_mrr_cpk.result 1970-01-01 03:00:00.000000000 +0300 +++ maria-5.3-dsmrr-for-cpk-noc/r/innodb_mrr_cpk.result 2010-06-22 19:23:14.000000000 +0400 @@ -0,0 +1,122 @@ +drop table if exists t0,t1,t2,t3; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; +set @save_storage_engine=@@storage_engine; +set storage_engine=innodb; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a char(8), b char(8), filler char(100), primary key(a)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(8) NOT NULL DEFAULT '', + `b` char(8) DEFAULT NULL, + `filler` char(100) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8)); +insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); +This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1 Using join buffer +This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +a b filler a +a-1010=A b-1010=B filler a-1010=A +a-1020=A b-1020=B filler a-1020=A +a-1030=A b-1030=B filler a-1030=A +drop table t1, t2; +create table t1( +a char(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1( +a varchar(8) character set utf8, b int, filler char(100), +primary key(a,b) +); +insert into t1 select +concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), +1000 + A.a + B.a*10 + C.a*100, +'filler' +from t0 A, t0 B, t0 C; +create table t2 (a char(8) character set utf8, b int); +insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +explain select * from t1, t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer +select * from t1, t2 where t1.a=t2.a; +a b filler a b +a-1010=A 1010 filler a-1010=A 1010 +a-1020=A 1020 filler a-1020=A 1020 +a-1030=A 1030 filler a-1030=A 1030 +drop table t1, t2; +create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); +insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; +insert into t1 values (11, 11, 11, 'filler'); +insert into t1 values (11, 11, 12, 'filler'); +insert into t1 values (11, 11, 13, 'filler'); +insert into t1 values (11, 22, 1234, 'filler'); +insert into t1 values (11, 33, 124, 'filler'); +insert into t1 values (11, 33, 125, 'filler'); +create table t2 (a int, b int); +insert into t2 values (11,33), (11,22), (11,11); +explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t2.a,test.t2.b 1 Using join buffer +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +11 22 1234 filler 11 22 +11 33 124 filler 11 33 +11 33 125 filler 11 33 +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +a b c filler a b +11 33 124 filler 11 33 +11 33 125 filler 11 33 +11 22 1234 filler 11 22 +11 11 11 filler 11 11 +11 11 12 filler 11 11 +11 11 13 filler 11 11 +set join_cache_level=6; +drop table t1,t2; +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/handler.h maria-5.3-dsmrr-for-cpk-noc/sql/handler.h --- maria-5.3-dsmrr-for-cpk-clean/sql/handler.h 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/handler.h 2010-06-22 23:28:40.000000000 +0400 @@ -1168,9 +1168,9 @@ COST_VECT *cost); /* - The below two are not used (and not handled) in this milestone of this WL - entry because there seems to be no use for them at this stage of - implementation. + Indicates that all scanned ranges will be singlepoint (aka equality) ranges. + The ranges may not use the full key but all of them will use the same number + of key parts. */ #define HA_MRR_SINGLE_POINT 1 #define HA_MRR_FIXED_KEY 2 @@ -1752,9 +1752,10 @@ uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); virtual ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); virtual int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, + uint n_ranges, uint mode, HANDLER_BUFFER *buf); virtual int multi_range_read_next(char **range_info); virtual int read_range_first(const key_range *start_key, diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/multi_range_read.cc maria-5.3-dsmrr-for-cpk-noc/sql/multi_range_read.cc --- maria-5.3-dsmrr-for-cpk-clean/sql/multi_range_read.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/multi_range_read.cc 2010-06-22 23:28:40.000000000 +0400 @@ -1,4 +1,5 @@ #include "mysql_priv.h" +#include <my_bit.h> #include "sql_select.h" /**************************************************************************** @@ -136,10 +137,16 @@ */ ha_rows handler::multi_range_read_info(uint keyno, uint n_ranges, uint n_rows, - uint *bufsz, uint *flags, COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { - *bufsz= 0; /* Default implementation doesn't need a buffer */ + /* + Currently we expect this function to be called only in preparation of scan + with HA_MRR_SINGLE_POINT property. + */ + DBUG_ASSERT(*flags | HA_MRR_SINGLE_POINT); + *bufsz= 0; /* Default implementation doesn't need a buffer */ *flags |= HA_MRR_USE_DEFAULT_IMPL; cost->zero(); @@ -316,25 +323,39 @@ { use_default_impl= TRUE; const int retval= - h->handler::multi_range_read_init(seq_funcs, seq_init_param, - n_ranges, mode, buf); + h->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges, + mode, buf); DBUG_RETURN(retval); } - rowids_buf= buf->buffer; + mrr_buf= buf->buffer; is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION); if (is_mrr_assoc) status_var_increment(table->in_use->status_var.ha_multi_range_read_init_count); - rowids_buf_end= buf->buffer_end; + mrr_buf_end= buf->buffer_end; + + if ((doing_cpk_scan= check_cpk_scan(h->active_index, mode))) + { + /* It's a DS-MRR/CPK scan */ + cpk_tuple_length= 0; /* dummy value telling it needs to be inited */ + cpk_have_range= FALSE; + use_default_impl= FALSE; + h->mrr_iter= seq_funcs->init(seq_init_param, n_ranges, mode); + h->mrr_funcs= *seq_funcs; + dsmrr_fill_buffer_cpk(); + if (dsmrr_eof) + buf->end_of_used_area= mrr_buf_last; + DBUG_RETURN(0); /* nothing could go wrong while filling the buffer */ + } + + /* In regular DS-MRR, buffer stores {rowid, range_id} pairs */ elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*); - rowids_buf_last= rowids_buf + - ((rowids_buf_end - rowids_buf)/ elem_size)* - elem_size; - rowids_buf_end= rowids_buf_last; + mrr_buf_last= mrr_buf + ((mrr_buf_end - mrr_buf)/ elem_size)* elem_size; + mrr_buf_end= mrr_buf_last; - /* + /* There can be two cases: - This is the first call since index_init(), h2==NULL Need to setup h2 then. @@ -406,8 +427,8 @@ goto error; } - if (h2->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges, - mode, buf) || + if (h2->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges, + mode, buf) || dsmrr_fill_buffer()) { goto error; @@ -417,7 +438,7 @@ adjust *buf to indicate that the remaining buffer space will not be used. */ if (dsmrr_eof) - buf->end_of_used_area= rowids_buf_last; + buf->end_of_used_area= mrr_buf_last; /* h->inited == INDEX may occur when 'range checked for each record' is @@ -473,6 +494,9 @@ rowid and return. The function assumes that rowids buffer is empty when it is invoked. + + dsmrr_eof is set to indicate whether we've exhausted the list of ranges we're + scanning. @param h Table handler @@ -487,8 +511,8 @@ int res; DBUG_ENTER("DsMrr_impl::dsmrr_fill_buffer"); - rowids_buf_cur= rowids_buf; - while ((rowids_buf_cur < rowids_buf_end) && + mrr_buf_cur= mrr_buf; + while ((mrr_buf_cur < mrr_buf_end) && !(res= h2->handler::multi_range_read_next(&range_info))) { KEY_MULTI_RANGE *curr_range= &h2->handler::mrr_cur_range; @@ -498,13 +522,13 @@ /* Put rowid, or {rowid, range_id} pair into the buffer */ h2->position(table->record[0]); - memcpy(rowids_buf_cur, h2->ref, h2->ref_length); - rowids_buf_cur += h2->ref_length; + memcpy(mrr_buf_cur, h2->ref, h2->ref_length); + mrr_buf_cur += h2->ref_length; if (is_mrr_assoc) { - memcpy(rowids_buf_cur, &range_info, sizeof(void*)); - rowids_buf_cur += sizeof(void*); + memcpy(mrr_buf_cur, &range_info, sizeof(void*)); + mrr_buf_cur += sizeof(void*); } } @@ -514,16 +538,224 @@ /* Sort the buffer contents by rowid */ uint elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*); - uint n_rowids= (rowids_buf_cur - rowids_buf) / elem_size; + uint n_rowids= (mrr_buf_cur - mrr_buf) / elem_size; - my_qsort2(rowids_buf, n_rowids, elem_size, (qsort2_cmp)rowid_cmp, + my_qsort2(mrr_buf, n_rowids, elem_size, (qsort2_cmp)rowid_cmp, (void*)h); - rowids_buf_last= rowids_buf_cur; - rowids_buf_cur= rowids_buf; + mrr_buf_last= mrr_buf_cur; + mrr_buf_cur= mrr_buf; DBUG_RETURN(0); } +/* + my_qsort2-compatible function to compare key tuples +*/ + +int DsMrr_impl::key_tuple_cmp(void* arg, uchar* key1, uchar* key2) +{ + DsMrr_impl *dsmrr= (DsMrr_impl*)arg; + TABLE *table= dsmrr->h->table; + + KEY_PART_INFO *part= table->key_info[table->s->primary_key].key_part; + uchar *key1_end= key1 + dsmrr->cpk_tuple_length; + + while (key1 < key1_end) + { + Field* f = part->field; + int len = part->store_length; + int res = f->cmp(key1, key2); + if (res) + return res; + key1 += len; + key2 += len; + part++; + } + return 0; +} + + +/* + DS-MRR/CPK: Fill the buffer with (lookup_tuple, range_id) pairs and sort + + SYNOPSIS + DsMrr_impl::dsmrr_fill_buffer_cpk() + + DESCRIPTION + DS-MRR/CPK: Fill the buffer with (lookup_tuple, range_id) pairs and sort + + dsmrr_eof is set to indicate whether we've exhausted the list of ranges + we're scanning. +*/ + +void DsMrr_impl::dsmrr_fill_buffer_cpk() +{ + int res; + KEY_MULTI_RANGE cur_range; + DBUG_ENTER("DsMrr_impl::dsmrr_fill_buffer_cpk"); + + mrr_buf_cur= mrr_buf; + while ((mrr_buf_cur < mrr_buf_end) && + !(res= h->mrr_funcs.next(h->mrr_iter, &cur_range))) + { + DBUG_ASSERT(cur_range.range_flag & EQ_RANGE); + DBUG_ASSERT(!cpk_tuple_length || + cpk_tuple_length == cur_range.start_key.length); + if (!cpk_tuple_length) + { + cpk_tuple_length= cur_range.start_key.length; + cpk_is_unique_scan= test(table->key_info[h->active_index].key_parts == + my_count_bits(cur_range.start_key.keypart_map)); + uint elem_size= cpk_tuple_length + (int)is_mrr_assoc * sizeof(void*); + mrr_buf_last= mrr_buf + ((mrr_buf_end - mrr_buf)/elem_size) * elem_size; + mrr_buf_end= mrr_buf_last; + } + + /* Put key, or {key, range_id} pair into the buffer */ + memcpy(mrr_buf_cur, cur_range.start_key.key, cpk_tuple_length); + mrr_buf_cur += cpk_tuple_length; + + if (is_mrr_assoc) + { + memcpy(mrr_buf_cur, &cur_range.ptr, sizeof(void*)); + mrr_buf_cur += sizeof(void*); + } + } + + dsmrr_eof= test(res); + + /* Sort the buffer contents by rowid */ + uint elem_size= cpk_tuple_length + (int)is_mrr_assoc * sizeof(void*); + uint n_rowids= (mrr_buf_cur - mrr_buf) / elem_size; + + my_qsort2(mrr_buf, n_rowids, elem_size, + (qsort2_cmp)DsMrr_impl::key_tuple_cmp, (void*)this); + mrr_buf_last= mrr_buf_cur; + mrr_buf_cur= mrr_buf; + DBUG_VOID_RETURN; +} + + +/* + DS-MRR/CPK: multi_range_read_next() function + + DESCRIPTION + DsMrr_impl::dsmrr_next_cpk() + range_info OUT identifier of range that the returned record belongs to + + DESCRIPTION + DS-MRR/CPK: multi_range_read_next() function. + This is similar to DsMrr_impl::dsmrr_next(), the differences are that + - we get records with index_read(), not with rnd_pos() + - we may get multiple records for one key (=element of the buffer) + - unlike dsmrr_fill_buffer(), dsmrr_fill_buffer_cpk() never fails. + + RETURN + 0 OK, next record was successfully read + HA_ERR_END_OF_FILE End of records + Other Some other error +*/ + +int DsMrr_impl::dsmrr_next_cpk(char **range_info) +{ + int res; + + while (cpk_have_range) + { + + if (h->mrr_funcs.skip_record && + h->mrr_funcs.skip_record(h->mrr_iter, cpk_saved_range_info, NULL)) + { + cpk_have_range= FALSE; + break; + } + + res= h->index_next_same(table->record[0], mrr_buf_cur, cpk_tuple_length); + + if (h->mrr_funcs.skip_index_tuple && + h->mrr_funcs.skip_index_tuple(h->mrr_iter, cpk_saved_range_info)) + continue; + + if (res != HA_ERR_END_OF_FILE) + { + if (is_mrr_assoc) + memcpy(range_info, &cpk_saved_range_info, sizeof(void*)); + return res; + } + + /* No more records in this range. Exit this loop and go get another range */ + cpk_have_range= FALSE; + } + + do + { + /* First, make sure we have a range at start of the buffer */ + if (mrr_buf_cur == mrr_buf_last) + { + if (dsmrr_eof) + { + res= HA_ERR_END_OF_FILE; + goto end; + } + dsmrr_fill_buffer_cpk(); + } + if (mrr_buf_cur == mrr_buf_last) + { + res= HA_ERR_END_OF_FILE; + goto end; + } + + /* Ok, got the range. Try making a lookup. */ + uchar *lookup_tuple= mrr_buf_cur; + mrr_buf_cur += cpk_tuple_length; + if (is_mrr_assoc) + { + memcpy(&cpk_saved_range_info, mrr_buf_cur, sizeof(void*)); + mrr_buf_cur += sizeof(void*) * test(is_mrr_assoc); + } + + if (h->mrr_funcs.skip_record && + h->mrr_funcs.skip_record(h->mrr_iter, cpk_saved_range_info, NULL)) + continue; + + res= h->index_read(table->record[0], lookup_tuple, cpk_tuple_length, + HA_READ_KEY_EXACT); + + /* + Check pushed index condition. Performance-wise, it does not make any + sense to put this call here (the above call has already accessed the full + record). That's the best I could do, though, because: + - ha_innobase doesn't support IndexConditionPushdown on clustered PK + - MRR interface doesn't allow the storage engine to refuse a pushed index + condition. + Having this call here is not fully harmless: EXPLAIN shows "pushed index + condition", which is technically true but doesn't bring the benefits that + one might expect. + */ + if (h->mrr_funcs.skip_index_tuple && + h->mrr_funcs.skip_index_tuple(h->mrr_iter, cpk_saved_range_info)) + continue; + + if (res && res != HA_ERR_END_OF_FILE) + goto end; + + if (!res) + { + memcpy(range_info, &cpk_saved_range_info, sizeof(void*)); + /* + Attempt reading more rows from this range only if there actually can + be multiple matches: + */ + cpk_have_range= !cpk_is_unique_scan; + break; + } + } while (true); + +end: + return res; +} + + /** DS-MRR implementation: multi_range_read_next() function */ @@ -536,10 +768,13 @@ if (use_default_impl) return h->handler::multi_range_read_next(range_info); + + if (doing_cpk_scan) + return dsmrr_next_cpk(range_info); do { - if (rowids_buf_cur == rowids_buf_last) + if (mrr_buf_cur == mrr_buf_last) { if (dsmrr_eof) { @@ -552,17 +787,17 @@ } /* return eof if there are no rowids in the buffer after re-fill attempt */ - if (rowids_buf_cur == rowids_buf_last) + if (mrr_buf_cur == mrr_buf_last) { res= HA_ERR_END_OF_FILE; goto end; } - rowid= rowids_buf_cur; + rowid= mrr_buf_cur; if (is_mrr_assoc) - memcpy(&cur_range_info, rowids_buf_cur + h->ref_length, sizeof(uchar**)); + memcpy(&cur_range_info, mrr_buf_cur + h->ref_length, sizeof(uchar**)); - rowids_buf_cur += h->ref_length + sizeof(void*) * test(is_mrr_assoc); + mrr_buf_cur += h->ref_length + sizeof(void*) * test(is_mrr_assoc); if (h2->mrr_funcs.skip_record && h2->mrr_funcs.skip_record(h2->mrr_iter, (char *) cur_range_info, rowid)) continue; @@ -582,7 +817,8 @@ /** DS-MRR implementation: multi_range_read_info() function */ -ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, +ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, + uint key_parts, uint *bufsz, uint *flags, COST_VECT *cost) { ha_rows res; @@ -590,8 +826,8 @@ uint def_bufsz= *bufsz; /* Get cost/flags/mem_usage of default MRR implementation */ - res= h->handler::multi_range_read_info(keyno, n_ranges, rows, &def_bufsz, - &def_flags, cost); + res= h->handler::multi_range_read_info(keyno, n_ranges, rows, key_parts, + &def_bufsz, &def_flags, cost); DBUG_ASSERT(!res); if ((*flags & HA_MRR_USE_DEFAULT_IMPL) || @@ -683,7 +919,33 @@ return FALSE; } -/** + +/* + Check if key/flags allow DS-MRR/CPK strategy to be used + + SYNOPSIS + DsMrr_impl::check_cpk_scan() + keyno Index that will be used + mrr_flags + + DESCRIPTION + Check if key/flags allow DS-MRR/CPK strategy to be used. + + RETURN + TRUE DS-MRR/CPK should be used + FALSE Otherwise +*/ + +bool DsMrr_impl::check_cpk_scan(uint keyno, uint mrr_flags) +{ + return test((mrr_flags & HA_MRR_SINGLE_POINT) && + !(mrr_flags & HA_MRR_SORTED) && + keyno == table->s->primary_key && + h->primary_key_is_clustered()); +} + + +/* DS-MRR Internals: Choose between Default MRR implementation and DS-MRR Make the choice between using Default MRR implementation and DS-MRR. @@ -706,14 +968,18 @@ @retval FALSE DS-MRR implementation should be used */ + bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz, COST_VECT *cost) { COST_VECT dsmrr_cost; bool res; THD *thd= current_thd; + + doing_cpk_scan= check_cpk_scan(keyno, *flags); if (thd->variables.optimizer_use_mrr == 2 || *flags & HA_MRR_INDEX_ONLY || - (keyno == table->s->primary_key && h->primary_key_is_clustered()) || + (keyno == table->s->primary_key && h->primary_key_is_clustered() && + !doing_cpk_scan) || key_uses_partial_cols(table, keyno)) { /* Use the default implementation */ diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/multi_range_read.h maria-5.3-dsmrr-for-cpk-noc/sql/multi_range_read.h --- maria-5.3-dsmrr-for-cpk-clean/sql/multi_range_read.h 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/multi_range_read.h 2010-06-22 23:28:40.000000000 +0400 @@ -1,16 +1,76 @@ /* - This file contains declarations for - - Disk-Sweep MultiRangeRead (DS-MRR) implementation + This file contains declarations for Disk-Sweep MultiRangeRead (DS-MRR) + implementation */ /** - A Disk-Sweep MRR interface implementation + A Disk-Sweep implementation of MRR Interface (DS-MRR for short) - This implementation makes range (and, in the future, 'ref') scans to read - table rows in disk sweeps. - - Currently it is used by MyISAM and InnoDB. Potentially it can be used with - any table handler that has non-clustered indexes and on-disk rows. + This is a "plugin"(*) for storage engines that allows make index scans + read table rows in rowid order. For disk-based storage engines, this is + faster than reading table rows in whatever-SQL-layer-makes-calls-in order. + + (*) - only conceptually. No dynamic loading or binary compatibility of any + kind. + + General scheme of things: + + SQL Layer code + | | | + -v---v---v---- handler->multi_range_read_XXX() function calls + | | | + ____________________________________ + / DS-MRR module \ + | (scan indexes, order rowids, do | + | full record reads in rowid order) | + \____________________________________/ + | | | + -|---|---|----- handler->read_range_first()/read_range_next(), + | | | handler->index_read(), handler->rnd_pos() calls. + | | | + v v v + Storage engine internals + + Currently DS-MRR is used by MyISAM, InnoDB/XtraDB and Maria storage engines. + Potentially it can be used with any table handler that has disk-based data + storage and has better performance when reading data in rowid order. +*/ + + +/* + DS-MRR implementation for one table. Create/use one object of this class for + each ha_{myisam/innobase/etc} object. That object will be further referred to + as "the handler" + + There are actually three strategies + S1. Bypass DS-MRR, pass all calls to default implementation (i.e. to + MRR-to-non-MRR calls converter) + S2. Regular DS-MRR + S3. DS-MRR/CPK for doing scans on clustered primary keys. + + S1 is used for cases which DS-MRR is unable to handle for some reason. + + S2 is the actual DS-MRR. The basic algorithm is as follows: + 1. Scan the index (and only index, that is, with HA_EXTRA_KEYREAD on) and + fill the buffer with {rowid, range_id} pairs + 2. Sort the buffer by rowid + 3. for each {rowid, range_id} pair in the buffer + get record by rowid and return the {record, range_id} pair + 4. Repeat the above steps until we've exhausted the list of ranges we're + scanning. + + S3 is the variant of DS-MRR for use with clustered primary keys (or any + clustered index). The idea is that in clustered index it is sufficient to + access the index in index order, and we don't need an intermediate steps to + get rowid (like step #1 in S2). + + DS-MRR/CPK's basic algorithm is as follows: + 1. Collect a number of ranges (=lookup keys) + 2. Sort them so that they follow in index order. + 3. for each {lookup_key, range_id} pair in the buffer + get record(s) matching the lookup key and return {record, range_id} pairs + 4. Repeat the above steps until we've exhausted the list of ranges we're + scanning. */ class DsMrr_impl @@ -21,21 +81,38 @@ DsMrr_impl() : h2(NULL) {}; + void init(handler *h_arg, TABLE *table_arg) + { + h= h_arg; + table= table_arg; + } + int dsmrr_init(handler *h, RANGE_SEQ_IF *seq_funcs, void *seq_init_param, + uint n_ranges, uint mode, HANDLER_BUFFER *buf); + void dsmrr_close(); + int dsmrr_next(char **range_info); + + ha_rows dsmrr_info(uint keyno, uint n_ranges, uint keys, uint key_parts, + uint *bufsz, uint *flags, COST_VECT *cost); + + ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq, + void *seq_init_param, uint n_ranges, uint *bufsz, + uint *flags, COST_VECT *cost); +private: /* The "owner" handler object (the one that calls dsmrr_XXX functions. It is used to retrieve full table rows by calling rnd_pos(). */ handler *h; TABLE *table; /* Always equal to h->table */ -private: + /* Secondary handler object. It is used for scanning the index */ handler *h2; /* Buffer to store rowids, or (rowid, range_id) pairs */ - uchar *rowids_buf; - uchar *rowids_buf_cur; /* Current position when reading/writing */ - uchar *rowids_buf_last; /* When reading: end of used buffer space */ - uchar *rowids_buf_end; /* End of the buffer */ + uchar *mrr_buf; + uchar *mrr_buf_cur; /* Current position when reading/writing */ + uchar *mrr_buf_last; /* When reading: end of used buffer space */ + uchar *mrr_buf_end; /* End of the buffer */ bool dsmrr_eof; /* TRUE <=> We have reached EOF when reading index tuples */ @@ -43,28 +120,31 @@ bool is_mrr_assoc; bool use_default_impl; /* TRUE <=> shortcut all calls to default MRR impl */ -public: - void init(handler *h_arg, TABLE *table_arg) - { - h= h_arg; - table= table_arg; - } - int dsmrr_init(handler *h, RANGE_SEQ_IF *seq_funcs, void *seq_init_param, - uint n_ranges, uint mode, HANDLER_BUFFER *buf); - void dsmrr_close(); - int dsmrr_fill_buffer(); - int dsmrr_next(char **range_info); - ha_rows dsmrr_info(uint keyno, uint n_ranges, uint keys, uint *bufsz, - uint *flags, COST_VECT *cost); + bool doing_cpk_scan; /* TRUE <=> DS-MRR/CPK variant is used */ + + /** DS-MRR/CPK variables start */ + + /* Length of lookup tuple being used, in bytes */ + uint cpk_tuple_length; + /* + TRUE <=> We're scanning on a full primary key (and not on prefix), and so + can get max. one match for each key + */ + bool cpk_is_unique_scan; + /* TRUE<=> we're in a middle of enumerating records from a range */ + bool cpk_have_range; + /* Valid if cpk_have_range==TRUE: range_id of the range we're enumerating */ + char *cpk_saved_range_info; - ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq, - void *seq_init_param, uint n_ranges, uint *bufsz, - uint *flags, COST_VECT *cost); -private: bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz, COST_VECT *cost); bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags, uint *buffer_size, COST_VECT *cost); + bool check_cpk_scan(uint keyno, uint mrr_flags); + static int key_tuple_cmp(void* arg, uchar* key1, uchar* key2); + int dsmrr_fill_buffer(); + void dsmrr_fill_buffer_cpk(); + int dsmrr_next_cpk(char **range_info); }; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/opt_range.cc maria-5.3-dsmrr-for-cpk-noc/sql/opt_range.cc --- maria-5.3-dsmrr-for-cpk-clean/sql/opt_range.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/opt_range.cc 2010-06-22 23:28:40.000000000 +0400 @@ -8006,6 +8006,7 @@ quick->mrr_buf_size= thd->variables.mrr_buff_size; if (table->file->multi_range_read_info(quick->index, 1, (uint)records, + uint(-1), &quick->mrr_buf_size, &quick->mrr_flags, &cost)) goto err; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/sql_join_cache.cc maria-5.3-dsmrr-for-cpk-noc/sql/sql_join_cache.cc --- maria-5.3-dsmrr-for-cpk-clean/sql/sql_join_cache.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/sql_join_cache.cc 2010-06-22 23:28:40.000000000 +0400 @@ -2376,8 +2376,8 @@ */ if (!file->inited) file->ha_index_init(join_tab->ref.key, 1); - if ((error= file->multi_range_read_init(seq_funcs, (void*) this, ranges, - mrr_mode, &mrr_buff))) + if ((error= file->multi_range_read_init(seq_funcs, (void*) this, ranges, + mrr_mode, &mrr_buff))) rc= error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR; return rc; diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/sql/sql_select.cc maria-5.3-dsmrr-for-cpk-noc/sql/sql_select.cc --- maria-5.3-dsmrr-for-cpk-clean/sql/sql_select.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/sql/sql_select.cc 2010-06-22 19:06:54.000000000 +0400 @@ -7318,10 +7318,11 @@ case JT_EQ_REF: if (cache_level <= 4) return 0; - flags= HA_MRR_NO_NULL_ENDPOINTS; + flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; if (tab->table->covering_keys.is_set(tab->ref.key)) flags|= HA_MRR_INDEX_ONLY; rows= tab->table->file->multi_range_read_info(tab->ref.key, 10, 20, + tab->ref.key_parts, &bufsz, &flags, &cost); if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL) && (!(flags & HA_MRR_NO_ASSOCIATION) || cache_level > 6) && diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/maria/ha_maria.cc maria-5.3-dsmrr-for-cpk-noc/storage/maria/ha_maria.cc --- maria-5.3-dsmrr-for-cpk-clean/storage/maria/ha_maria.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/maria/ha_maria.cc 2010-06-22 23:28:40.000000000 +0400 @@ -3501,8 +3501,8 @@ ***************************************************************************/ int ha_maria::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, - HANDLER_BUFFER *buf) + uint n_ranges, uint mode, + HANDLER_BUFFER *buf) { return ds_mrr.dsmrr_init(this, seq, seq_init_param, n_ranges, mode, buf); } @@ -3528,11 +3528,11 @@ } ha_rows ha_maria::multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, - COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + return ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, flags, cost); } /* MyISAM MRR implementation ends */ diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/maria/ha_maria.h maria-5.3-dsmrr-for-cpk-noc/storage/maria/ha_maria.h --- maria-5.3-dsmrr-for-cpk-clean/storage/maria/ha_maria.h 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/maria/ha_maria.h 2010-06-22 23:28:40.000000000 +0400 @@ -181,7 +181,8 @@ uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); /* Index condition pushdown implementation */ Item *idx_cond_push(uint keyno, Item* idx_cond); diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/myisam/ha_myisam.cc maria-5.3-dsmrr-for-cpk-noc/storage/myisam/ha_myisam.cc --- maria-5.3-dsmrr-for-cpk-clean/storage/myisam/ha_myisam.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/myisam/ha_myisam.cc 2010-06-22 23:28:40.000000000 +0400 @@ -2244,11 +2244,11 @@ } ha_rows ha_myisam::multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, - COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + return ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, flags, cost); } /* MyISAM MRR implementation ends */ diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/myisam/ha_myisam.h maria-5.3-dsmrr-for-cpk-noc/storage/myisam/ha_myisam.h --- maria-5.3-dsmrr-for-cpk-clean/storage/myisam/ha_myisam.h 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/myisam/ha_myisam.h 2010-06-22 23:28:40.000000000 +0400 @@ -169,7 +169,8 @@ uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); /* Index condition pushdown implementation */ Item *idx_cond_push(uint keyno, Item* idx_cond); diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/xtradb/handler/ha_innodb.cc maria-5.3-dsmrr-for-cpk-noc/storage/xtradb/handler/ha_innodb.cc --- maria-5.3-dsmrr-for-cpk-clean/storage/xtradb/handler/ha_innodb.cc 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/xtradb/handler/ha_innodb.cc 2010-06-22 23:28:40.000000000 +0400 @@ -11025,7 +11025,8 @@ */ int ha_innobase::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, HANDLER_BUFFER *buf) + uint n_ranges, uint mode, + HANDLER_BUFFER *buf) { return ds_mrr.dsmrr_init(this, seq, seq_init_param, n_ranges, mode, buf); } @@ -11052,12 +11053,13 @@ return res; } -ha_rows ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, - uint keys, uint *bufsz, +ha_rows ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, uint keys, + uint key_parts, uint *bufsz, uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - ha_rows res= ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + ha_rows res= ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, + flags, cost); return res; } diff -urN --exclude='.*' maria-5.3-dsmrr-for-cpk-clean/storage/xtradb/handler/ha_innodb.h maria-5.3-dsmrr-for-cpk-noc/storage/xtradb/handler/ha_innodb.h --- maria-5.3-dsmrr-for-cpk-clean/storage/xtradb/handler/ha_innodb.h 2010-06-22 19:10:46.000000000 +0400 +++ maria-5.3-dsmrr-for-cpk-noc/storage/xtradb/handler/ha_innodb.h 2010-06-22 23:28:40.000000000 +0400 @@ -217,7 +217,8 @@ uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); DsMrr_impl ds_mrr; Item *idx_cond_push(uint keyno, Item* idx_cond); BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 69 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:36)=-=- Research and design thoughts. Worked 19 hours and estimate 0 hours remain (original estimate increased by 19 hours). -=-=(Knielsen - Mon, 07 Jun 2010, 12:11)=-=- High Level Description modified. --- /tmp/wklog.107.old.31097 2010-06-07 12:11:57.000000000 +0000 +++ /tmp/wklog.107.new.31097 2010-06-07 12:11:57.000000000 +0000 @@ -7,3 +7,6 @@ https://lists.launchpad.net/maria-developers/msg01998.html +Wiki page for the project: + + http://askmonty.org/wiki/ReplicationProject -=-=(Knielsen - Mon, 29 Mar 2010, 07:33)=-=- Research and design discussions: Galera, 2pc/XA, group commit, multi-engine transactions. Worked 14 hours and estimate 0 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html Wiki page for the project: http://askmonty.org/wiki/ReplicationProject HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 69 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:36)=-=- Research and design thoughts. Worked 19 hours and estimate 0 hours remain (original estimate increased by 19 hours). -=-=(Knielsen - Mon, 07 Jun 2010, 12:11)=-=- High Level Description modified. --- /tmp/wklog.107.old.31097 2010-06-07 12:11:57.000000000 +0000 +++ /tmp/wklog.107.new.31097 2010-06-07 12:11:57.000000000 +0000 @@ -7,3 +7,6 @@ https://lists.launchpad.net/maria-developers/msg01998.html +Wiki page for the project: + + http://askmonty.org/wiki/ReplicationProject -=-=(Knielsen - Mon, 29 Mar 2010, 07:33)=-=- Research and design discussions: Galera, 2pc/XA, group commit, multi-engine transactions. Worked 14 hours and estimate 0 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html Wiki page for the project: http://askmonty.org/wiki/ReplicationProject HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Replication API for stacked event generators (120)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Replication API for stacked event generators CREATION DATE..: Mon, 07 Jun 2010, 13:13 SUPERVISOR.....: Knielsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 120 (http://askmonty.org/worklog/?tid=120) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 2 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:35)=-=- Research and design thoughts. Worked 2 hours and estimate 0 hours remain (original estimate increased by 2 hours). DESCRIPTION: A part of the replication project, MWL#107. Events are produced by event Generators. Examples are - Generation of statement-based replication events - Generation of row-based events - Generation of PBXT engine-level replication events and maybe reading of events from relay log on slave may also be an example of generating events. Event generators can be stacked, and a generator may defer event generation to the next one down the stack. For example, the row-level replication event generator may defer DLL to the statement-level replication event generator. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Replication API for stacked event generators (120)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Replication API for stacked event generators CREATION DATE..: Mon, 07 Jun 2010, 13:13 SUPERVISOR.....: Knielsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 120 (http://askmonty.org/worklog/?tid=120) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 2 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:35)=-=- Research and design thoughts. Worked 2 hours and estimate 0 hours remain (original estimate increased by 2 hours). DESCRIPTION: A part of the replication project, MWL#107. Events are produced by event Generators. Examples are - Generation of statement-based replication events - Generation of row-based events - Generation of PBXT engine-level replication events and maybe reading of events from relay log on slave may also be an example of generating events. Event generators can be stacked, and a generator may defer event generation to the next one down the stack. For example, the row-level replication event generator may defer DLL to the statement-level replication event generator. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Code-Review PRIORITY.......: 60 WORKED HOURS...: 42 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:32)=-=- Final review. Assist with some problems applying the patch. Worked 1 hour and estimate 0 hours remain (original estimate increased by 1 hour). -=-=(Guest - Thu, 17 Jun 2010, 00:38)=-=- Dependency deleted: 39 no longer depends on 47 -=-=(Knielsen - Mon, 07 Jun 2010, 07:13)=-=- Help debug some test failures seen in Buildbot. Worked 6 hours and estimate 0 hours remain (original estimate increased by 6 hours). -=-=(Knielsen - Mon, 31 May 2010, 06:49)=-=- Help Alexi debug+fix some test problems in the patch. Worked 4 hours and estimate 0 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 25 May 2010, 08:29)=-=- Help debug strange problem in mysqlbinlog.test. Worked 1 hour and estimate 4 hours remain (original estimate unchanged). -=-=(Knielsen - Mon, 17 May 2010, 08:45)=-=- Merge with latest trunk and run Buildbot tests. Worked 1 hour and estimate 5 hours remain (original estimate unchanged). -=-=(Knielsen - Wed, 05 May 2010, 13:53)=-=- Review of fixes to first review done. No new issues found. Worked 2 hours and estimate 6 hours remain (original estimate unchanged). -=-=(Knielsen - Fri, 23 Apr 2010, 12:51)=-=- Status updated. --- /tmp/wklog.47.old.28747 2010-04-23 12:51:36.000000000 +0000 +++ /tmp/wklog.47.new.28747 2010-04-23 12:51:36.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress ------------------------------------------------------------ -=-=(View All Progress Notes, 35 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Code-Review PRIORITY.......: 60 WORKED HOURS...: 42 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:32)=-=- Final review. Assist with some problems applying the patch. Worked 1 hour and estimate 0 hours remain (original estimate increased by 1 hour). -=-=(Guest - Thu, 17 Jun 2010, 00:38)=-=- Dependency deleted: 39 no longer depends on 47 -=-=(Knielsen - Mon, 07 Jun 2010, 07:13)=-=- Help debug some test failures seen in Buildbot. Worked 6 hours and estimate 0 hours remain (original estimate increased by 6 hours). -=-=(Knielsen - Mon, 31 May 2010, 06:49)=-=- Help Alexi debug+fix some test problems in the patch. Worked 4 hours and estimate 0 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 25 May 2010, 08:29)=-=- Help debug strange problem in mysqlbinlog.test. Worked 1 hour and estimate 4 hours remain (original estimate unchanged). -=-=(Knielsen - Mon, 17 May 2010, 08:45)=-=- Merge with latest trunk and run Buildbot tests. Worked 1 hour and estimate 5 hours remain (original estimate unchanged). -=-=(Knielsen - Wed, 05 May 2010, 13:53)=-=- Review of fixes to first review done. No new issues found. Worked 2 hours and estimate 6 hours remain (original estimate unchanged). -=-=(Knielsen - Fri, 23 Apr 2010, 12:51)=-=- Status updated. --- /tmp/wklog.47.old.28747 2010-04-23 12:51:36.000000000 +0000 +++ /tmp/wklog.47.new.28747 2010-04-23 12:51:36.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress ------------------------------------------------------------ -=-=(View All Progress Notes, 35 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Code-Review PRIORITY.......: 60 WORKED HOURS...: 42 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:32)=-=- Final review. Assist with some problems applying the patch. Worked 1 hour and estimate 0 hours remain (original estimate increased by 1 hour). -=-=(Guest - Thu, 17 Jun 2010, 00:38)=-=- Dependency deleted: 39 no longer depends on 47 -=-=(Knielsen - Mon, 07 Jun 2010, 07:13)=-=- Help debug some test failures seen in Buildbot. Worked 6 hours and estimate 0 hours remain (original estimate increased by 6 hours). -=-=(Knielsen - Mon, 31 May 2010, 06:49)=-=- Help Alexi debug+fix some test problems in the patch. Worked 4 hours and estimate 0 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 25 May 2010, 08:29)=-=- Help debug strange problem in mysqlbinlog.test. Worked 1 hour and estimate 4 hours remain (original estimate unchanged). -=-=(Knielsen - Mon, 17 May 2010, 08:45)=-=- Merge with latest trunk and run Buildbot tests. Worked 1 hour and estimate 5 hours remain (original estimate unchanged). -=-=(Knielsen - Wed, 05 May 2010, 13:53)=-=- Review of fixes to first review done. No new issues found. Worked 2 hours and estimate 6 hours remain (original estimate unchanged). -=-=(Knielsen - Fri, 23 Apr 2010, 12:51)=-=- Status updated. --- /tmp/wklog.47.old.28747 2010-04-23 12:51:36.000000000 +0000 +++ /tmp/wklog.47.new.28747 2010-04-23 12:51:36.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress ------------------------------------------------------------ -=-=(View All Progress Notes, 35 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 21 Jun '10

21 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Code-Review PRIORITY.......: 60 WORKED HOURS...: 42 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Mon, 21 Jun 2010, 08:32)=-=- Final review. Assist with some problems applying the patch. Worked 1 hour and estimate 0 hours remain (original estimate increased by 1 hour). -=-=(Guest - Thu, 17 Jun 2010, 00:38)=-=- Dependency deleted: 39 no longer depends on 47 -=-=(Knielsen - Mon, 07 Jun 2010, 07:13)=-=- Help debug some test failures seen in Buildbot. Worked 6 hours and estimate 0 hours remain (original estimate increased by 6 hours). -=-=(Knielsen - Mon, 31 May 2010, 06:49)=-=- Help Alexi debug+fix some test problems in the patch. Worked 4 hours and estimate 0 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 25 May 2010, 08:29)=-=- Help debug strange problem in mysqlbinlog.test. Worked 1 hour and estimate 4 hours remain (original estimate unchanged). -=-=(Knielsen - Mon, 17 May 2010, 08:45)=-=- Merge with latest trunk and run Buildbot tests. Worked 1 hour and estimate 5 hours remain (original estimate unchanged). -=-=(Knielsen - Wed, 05 May 2010, 13:53)=-=- Review of fixes to first review done. No new issues found. Worked 2 hours and estimate 6 hours remain (original estimate unchanged). -=-=(Knielsen - Fri, 23 Apr 2010, 12:51)=-=- Status updated. --- /tmp/wklog.47.old.28747 2010-04-23 12:51:36.000000000 +0000 +++ /tmp/wklog.47.new.28747 2010-04-23 12:51:36.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress ------------------------------------------------------------ -=-=(View All Progress Notes, 35 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] MariaDB 5.2.1-beta Release Notes and Changelog
by Daniel Bartholomew 19 Jun '10

19 Jun '10

Sergei (and everyone else), The Release Notes and Changelog pages for the MariaDB 5.2.1-beta release are ready: http://askmonty.org/wiki/Manual:MariaDB_5.2.1_Release_Notes http://askmonty.org/wiki/Manual:MariaDB_5.2.1_Changelog Please let me know if the Release Notes should mention anything else or if there is anything on that page which should be changed. The Changelog should have the full list of commits from the 5.2.0-beta up through the commit with the 5.2.1-beta tag. The download page for this release is also ready to go, but I haven't activated it yet. I will activate it (i.e. link to it from the download page, and other wiki pages) once the mirrors have been seeded (later tonight or tomorrow). Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

1 1