August 2009 - developers - lists.mariadb.org

[Maria-developers] Rev 2733: MWL#17: Table elimination in file:///home/psergey/dev/maria-5.1-table-elim-r11/
by Sergey Petrunya 18 Aug '09

18 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r11/ ------------------------------------------------------------ revno: 2733 revision-id: psergey(a)askmonty.org-20090818130358-akd84j4m2i91lw5a parent: psergey(a)askmonty.org-20090817160724-fmmrmwp8zorzn82q committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r11 timestamp: Tue 2009-08-18 16:03:58 +0300 message: MWL#17: Table elimination - Switch from trying to eliminate all tables at once (which didn't work) to the original approach of bottom-up elimination. === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-17 15:02:29 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-18 13:03:58 +0000 @@ -273,8 +273,9 @@ class Outer_join_module: public Module_dep { public: - Outer_join_module(TABLE_LIST *table_list_arg, uint n_children) : - table_list(table_list_arg), parent(NULL) + Outer_join_module(//TABLE_LIST *table_list_arg, + uint n_children) + // table_list(table_list_arg), parent(NULL) { type= Module_dep::MODULE_OUTER_JOIN; unknown_args= n_children; @@ -283,10 +284,10 @@ Outer join we're representing. This can be a join nest or one table that is outer join'ed. */ - TABLE_LIST *table_list; +// TABLE_LIST *table_list; /* Parent eliminable outer join, if any */ - Outer_join_module *parent; +// Outer_join_module *parent; }; @@ -334,10 +335,10 @@ static Table_value *get_table_value(Table_elimination *te, TABLE *table); static Field_value *get_field_value(Table_elimination *te, Field *field); static Outer_join_module *get_outer_join_dep(Table_elimination *te, - TABLE_LIST *outer_join, + //TABLE_LIST *outer_join, table_map deps_map); static -void run_elimination_wave(Table_elimination *te, Module_dep *bound_modules); +bool run_elimination_wave(Table_elimination *te, Module_dep *bound_modules); void eliminate_tables(JOIN *join); static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl); @@ -357,7 +358,7 @@ and_level INOUT AND-level (like in add_key_fields) cond Condition to process usable_tables Tables which fields we're interested in. That is, - Equality_dep represent "tbl.col=expr" and we'll + Equality_module represent "tbl.col=expr" and we'll produce them only if tbl is in usable_tables. DESCRIPTION This function is modeled after add_key_fields() @@ -747,11 +748,11 @@ static Outer_join_module *get_outer_join_dep(Table_elimination *te, - TABLE_LIST *outer_join, + // TABLE_LIST *outer_join, table_map deps_map) { Outer_join_module *oj_dep; - if (!(oj_dep= new Outer_join_module(outer_join, my_count_bits(deps_map)))) + if (!(oj_dep= new Outer_join_module(/*outer_join, */my_count_bits(deps_map)))) return NULL; te->n_outer_joins++; @@ -792,8 +793,9 @@ point to the newly created Outer_join_module. to set the pointer of its near */ - if (!table_dep->outer_join_dep) + //if (!table_dep->outer_join_dep) table_dep->outer_join_dep= oj_dep; + /* else { Outer_join_module *oj= table_dep->outer_join_dep; @@ -802,95 +804,13 @@ if (oj != oj_dep) oj->parent=oj_dep; } + */ } return oj_dep; } /* - Build functional dependency graph for elements of given join list - - SYNOPSIS - collect_funcdeps_for_join_list() - te Table elimination context. - join_list Join list to work on - build_eq_deps TRUE <=> build Equality_module elements for all - members of the join list, even if they cannot - be individually eliminated - tables_used_elsewhere Bitmap of tables that are referred to from - somewhere outside of this join list (e.g. - select list, HAVING, ON expressions of parent - joins, etc). - eliminable_tables INOUT Tables that can potentially be eliminated - (needed so we know for which tables to build - dependencies for) - eq_dep INOUT End of array of equality dependencies. - - DESCRIPTION - . -*/ - -static bool -collect_funcdeps_for_join_list(Table_elimination *te, - List<TABLE_LIST> *join_list, - bool build_eq_deps, - table_map tables_used_elsewhere, - table_map *eliminable_tables, - Equality_module **eq_dep) -{ - TABLE_LIST *tbl; - List_iterator<TABLE_LIST> it(*join_list); - table_map tables_used_on_left= 0; - - while ((tbl= it++)) - { - if (tbl->on_expr) - { - table_map outside_used_tables= tables_used_elsewhere | - tables_used_on_left; - bool eliminable; - table_map cur_map; - if (tbl->nested_join) - { - /* This is "... LEFT JOIN (join_nest) ON cond" */ - cur_map= tbl->nested_join->used_tables; - eliminable= !(cur_map & outside_used_tables); - if (eliminable) - *eliminable_tables |= cur_map; - if (collect_funcdeps_for_join_list(te, &tbl->nested_join->join_list, - eliminable || build_eq_deps, - outside_used_tables, - eliminable_tables, - eq_dep)) - return TRUE; - } - else - { - /* This is "... LEFT JOIN tbl ON cond" */ - cur_map= tbl->table->map; - eliminable= !(tbl->table->map & outside_used_tables); - *eliminable_tables |= cur_map; - } - - if (eliminable || build_eq_deps) - { - // build comp_cond from ON expression - uint and_level=0; - build_eq_deps_for_cond(te, eq_dep, &and_level, tbl->on_expr, - *eliminable_tables); - } - - if (eliminable && !get_outer_join_dep(te, tbl, cur_map)) - return TRUE; - - tables_used_on_left |= tbl->on_expr->used_tables(); - } - } - return FALSE; -} - - -/* This is used to analyze expressions in "tbl.col=expr" dependencies so that we can figure out which fields the expression depends on. */ @@ -950,13 +870,15 @@ { DBUG_ENTER("setup_equality_deps"); + if (!te->n_equality_deps) + DBUG_RETURN(TRUE); /* Count Field_value objects and assign each of them a unique bitmap_offset. */ uint offset= 0; for (Table_value **tbl_dep=te->table_deps; tbl_dep < te->table_deps + MAX_TABLES; - tbl_dep++) + tbl_dep++) // psergey-todo: TODO change to Table_map_iterator { if (*tbl_dep) { @@ -1046,6 +968,12 @@ See the OVERVIEW section at the top of this file. */ +static uint +eliminate_tables_for_list(Table_elimination *te, + List<TABLE_LIST> *join_list, + Item *on_expr, + table_map tables_in_list, + table_map tables_used_elsewhere); void eliminate_tables(JOIN *join) { @@ -1101,15 +1029,22 @@ thd->lex->current_select->between_count)*m + 1 + 10; if (!(te.equality_deps= new Equality_module[max_elems])) DBUG_VOID_RETURN; - Equality_module *eq_deps_end= te.equality_deps; - table_map eliminable_tables= 0; + //Equality_module *eq_deps_end= te.equality_deps; + //table_map eliminable_tables= 0; + /* if (collect_funcdeps_for_join_list(&te, join->join_list, FALSE, used_tables, &eliminable_tables, - &eq_deps_end)) + &eq_deps)) DBUG_VOID_RETURN; - te.n_equality_deps= eq_deps_end - te.equality_deps; + */ + eliminate_tables_for_list(&te, join->join_list, + NULL, + (table_map(1) << join->tables) - 1, + used_tables); + + /*te.n_equality_deps= eq_deps_end - te.equality_deps; Module_dep *bound_modules; //Value_dep *bound_values; @@ -1117,11 +1052,207 @@ DBUG_VOID_RETURN; run_elimination_wave(&te, bound_modules); + */ } DBUG_VOID_RETURN; } +//////////////////////////// + +/* + Perform table elimination in a given join list + + SYNOPSIS + eliminate_tables_for_list() + join The join + leaves_arr OUT Store here an array of leaf (base) tables that + are descendants of the join_list, and increment + the pointer to point right above the array. + join_list Join list to work on + its_outer_join TRUE <=> join_list is an inner side of an outer + join + FALSE <=> otherwise (this is top-level join list) + tables_in_list Bitmap of tables embedded in the join_list. + tables_used_elsewhere Bitmap of tables that are referred to from + somewhere outside of the join list (e.g. + select list, HAVING, etc). + + DESCRIPTION + Perform table elimination for a join list. + Try eliminating children nests first. + The "all tables in join nest can produce only one matching record + combination" property checking is modeled after constant table detection, + plus we reuse info attempts to eliminate child join nests. + + RETURN + Number of children left after elimination. 0 means everything was + eliminated. +*/ + +bool try_eliminating(Table_elimination *te, table_map tables_in_list, + Item* on_expr) +{ + uint and_level=0; + Equality_module* eq_dep= te->equality_deps; + build_eq_deps_for_cond(te, &eq_dep, &and_level, on_expr, + tables_in_list); + te->n_equality_deps= eq_dep - te->equality_deps; + Module_dep *bound_modules; + get_outer_join_dep(te, tables_in_list); + + if (!setup_equality_deps(te, &bound_modules) && + run_elimination_wave(te, bound_modules)) + return TRUE; // eliminated + return FALSE; +} + + +static uint +eliminate_tables_for_list(Table_elimination *te, List<TABLE_LIST> *join_list, + Item *on_expr, + table_map tables_in_list, + table_map tables_used_elsewhere) +{ + TABLE_LIST *tbl; + List_iterator<TABLE_LIST> it(*join_list); + table_map tables_used_on_left= 0; + bool not_eliminated= FALSE; + + while ((tbl= it++)) + { + if (tbl->on_expr) + { + table_map outside_used_tables= tables_used_elsewhere | + tables_used_on_left; + if (tbl->nested_join) + { + /* This is "... LEFT JOIN (join_nest) ON cond" */ + if (eliminate_tables_for_list(te, + &tbl->nested_join->join_list, + tbl->on_expr, + tbl->nested_join->used_tables, + outside_used_tables)) + { + mark_as_eliminated(te->join, tbl); + } + else + not_eliminated= TRUE; + + } + else + { + /* This is "... LEFT JOIN tbl ON cond" */ + if (!(tbl->table->map & outside_used_tables) && + try_eliminating(te, tbl->table->map, tbl->on_expr)) + { + mark_as_eliminated(te->join, tbl); + } + else + not_eliminated= TRUE; + } + tables_used_on_left |= tbl->on_expr->used_tables(); + } + else + { + DBUG_ASSERT(!tbl->nested_join); + } + } + + /* Try eliminating the nest we're called for */ + if (!not_eliminated && on_expr && !(tables_in_list & tables_used_elsewhere)) + return try_eliminating(te, tables_in_list & ~te->join->eliminated_tables, + on_expr); + + return FALSE; /* not eliminated */ +} + +#if 0 +/* + Build functional dependency graph for elements of given join list + + SYNOPSIS + collect_funcdeps_for_join_list() + te Table elimination context. + join_list Join list to work on + build_eq_deps TRUE <=> build Equality_module elements for all + members of the join list, even if they cannot + be individually eliminated + tables_used_elsewhere Bitmap of tables that are referred to from + somewhere outside of this join list (e.g. + select list, HAVING, ON expressions of parent + joins, etc). + eliminable_tables INOUT Tables that can potentially be eliminated + (needed so we know for which tables to build + dependencies for) + eq_dep INOUT End of array of equality dependencies. + + DESCRIPTION + . +*/ + +static bool +collect_funcdeps_for_join_list(Table_elimination *te, + List<TABLE_LIST> *join_list, + bool build_eq_deps, + table_map tables_used_elsewhere, + table_map *eliminable_tables, + Equality_module **eq_dep) +{ + TABLE_LIST *tbl; + List_iterator<TABLE_LIST> it(*join_list); + table_map tables_used_on_left= 0; + + while ((tbl= it++)) + { + if (tbl->on_expr) + { + table_map outside_used_tables= tables_used_elsewhere | + tables_used_on_left; + bool eliminable; + table_map cur_map; + if (tbl->nested_join) + { + /* This is "... LEFT JOIN (join_nest) ON cond" */ + cur_map= tbl->nested_join->used_tables; + eliminable= !(cur_map & outside_used_tables); + if (eliminable) + *eliminable_tables |= cur_map; + if (collect_funcdeps_for_join_list(te, &tbl->nested_join->join_list, + eliminable || build_eq_deps, + outside_used_tables, + eliminable_tables, + eq_dep)) + return TRUE; + } + else + { + /* This is "... LEFT JOIN tbl ON cond" */ + cur_map= tbl->table->map; + eliminable= !(tbl->table->map & outside_used_tables); + *eliminable_tables |= cur_map; + } + + if (eliminable || build_eq_deps) + { + // build comp_cond from ON expression + uint and_level=0; + build_eq_deps_for_cond(te, eq_dep, &and_level, tbl->on_expr, + *eliminable_tables); + } + + if (eliminable && !get_outer_join_dep(te, tbl, cur_map)) + return TRUE; + + tables_used_on_left |= tbl->on_expr->used_tables(); + } + } + return FALSE; +} +#endif + +//////////////////////////// + static void signal_from_field_to_exprs(Table_elimination* te, Field_value *field_dep, Module_dep **bound_modules) @@ -1142,7 +1273,7 @@ static -void run_elimination_wave(Table_elimination *te, Module_dep *bound_modules) +bool run_elimination_wave(Table_elimination *te, Module_dep *bound_modules) { Value_dep *bound_values= NULL; /* @@ -1187,13 +1318,13 @@ } case Module_dep::MODULE_OUTER_JOIN: { - Outer_join_module *outer_join_dep= (Outer_join_module*)bound_modules; - mark_as_eliminated(te->join, outer_join_dep->table_list); - if (!--te->n_outer_joins) + //Outer_join_module *outer_join_dep= (Outer_join_module*)bound_modules; + //mark_as_eliminated(te->join, outer_join_dep->table_list); + //if (!--te->n_outer_joins) { DBUG_PRINT("info", ("Table elimination eliminated everything" " it theoretically could")); - return; + return TRUE; } break; } @@ -1256,8 +1387,9 @@ signal_from_field_to_exprs(te, field_dep, &bound_modules); } } - for (Outer_join_module *outer_join_dep= table_dep->outer_join_dep; - outer_join_dep; outer_join_dep= outer_join_dep->parent) + //for ( + Outer_join_module *outer_join_dep= table_dep->outer_join_dep; + // outer_join_dep; outer_join_dep= outer_join_dep->parent) { if (outer_join_dep->unknown_args && !--outer_join_dep->unknown_args) @@ -1274,6 +1406,7 @@ } } } + return FALSE; }

1 0

[Maria-developers] Rev 2732: MWL#17: Table elimination in file:///home/psergey/dev/maria-5.1-table-elim-r10/
by Sergey Petrunya 17 Aug '09

17 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2732 revision-id: psergey(a)askmonty.org-20090817160724-fmmrmwp8zorzn82q parent: psergey(a)askmonty.org-20090817150229-jy461nqbmk8nzhha committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Mon 2009-08-17 19:07:24 +0300 message: MWL#17: Table elimination - More testcases === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2009-08-17 15:02:29 +0000 +++ b/mysql-test/r/table_elim.result 2009-08-17 16:07:24 +0000 @@ -218,4 +218,48 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +drop table t1, t2, t3; +# +# Check things that look like functional dependencies but really are not +# +create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key); +insert into t1 values ('foo'); +insert into t1 values ('bar'); +create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key); +insert into t2 values ('foo'); +insert into t2 values ('FOO'); +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +drop table t1,t2; +create table t1 (a int primary key); +insert into t1 values (1),(2); +create table t2 (a char(10) primary key); +insert into t2 values ('1'),('1.0'); +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +drop table t1, t2; +create table t1 (a char(10) primary key); +insert into t1 values ('foo'),('bar'); +create table t2 (a char(10), unique key(a(2))); +insert into t2 values ('foo'),('bar'); +explain select t1.* from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ref a a 3 test.t1.a 2 drop table t1, t2; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2009-08-17 15:02:29 +0000 +++ b/mysql-test/t/table_elim.test 2009-08-17 16:07:24 +0000 @@ -175,5 +175,46 @@ explain select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; +drop table t1, t2, t3; + +--echo # +--echo # Check things that look like functional dependencies but really are not +--echo # + +create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key); +insert into t1 values ('foo'); +insert into t1 values ('bar'); + +create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key); +insert into t2 values ('foo'); +insert into t2 values ('FOO'); + +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; + +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; +drop table t1,t2; + +create table t1 (a int primary key); +insert into t1 values (1),(2); +create table t2 (a char(10) primary key); +insert into t2 values ('1'),('1.0'); +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=1; +-- echo this must not use table elimination: +explain select t1.* from t1 left join t2 on t2.a=t1.a; + +drop table t1, t2; +# partial unique keys do not work at the moment, although they are able to +# provide one-match guarantees: +create table t1 (a char(10) primary key); +insert into t1 values ('foo'),('bar'); + +create table t2 (a char(10), unique key(a(2))); +insert into t2 values ('foo'),('bar'); + +explain select t1.* from t1 left join t2 on t2.a=t1.a; + drop table t1, t2;

1 0

[Maria-developers] Rev 2731: MWL#17: Table elimination in file:///home/psergey/dev/maria-5.1-table-elim-r10/
by Sergey Petrunya 17 Aug '09

17 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2731 revision-id: psergey(a)askmonty.org-20090817150229-jy461nqbmk8nzhha parent: psergey(a)askmonty.org-20090816180159-z3lfkjpjfsm7zbp0 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Mon 2009-08-17 18:02:29 +0300 message: MWL#17: Table elimination - More dbug printouts - More testcases === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2009-06-29 13:51:15 +0000 +++ b/mysql-test/r/table_elim.result 2009-08-17 15:02:29 +0000 @@ -202,3 +202,20 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 drop table t1, t2; +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); +create table t2 like t1; +insert into t2 select * from t1; +create table t3 like t1; +insert into t3 select * from t1; +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +drop table t1, t2; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2009-06-29 13:51:15 +0000 +++ b/mysql-test/t/table_elim.test 2009-08-17 15:02:29 +0000 @@ -157,4 +157,23 @@ t2.pk3=t2.pk1; drop table t1, t2; +# +# Check that equality propagation is taken into account +# +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); + +create table t2 like t1; +insert into t2 select * from t1; + +create table t3 like t1; +insert into t3 select * from t1; + +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; + +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; + +drop table t1, t2; === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-16 18:01:59 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-17 15:02:29 +0000 @@ -136,7 +136,8 @@ /* - A value. + A value, something that can be bound or not bound. Also, values can be linked + in a list. */ class Value_dep : public Sql_alloc @@ -203,7 +204,7 @@ /* - A 'module' + A 'module'. Module has dependencies */ class Module_dep : public Sql_alloc @@ -227,7 +228,6 @@ }; - /* A "tbl.column= expr" equality dependency. tbl.column depends on fields used in expr. @@ -333,6 +333,9 @@ static Table_value *get_table_value(Table_elimination *te, TABLE *table); static Field_value *get_field_value(Table_elimination *te, Field *field); +static Outer_join_module *get_outer_join_dep(Table_elimination *te, + TABLE_LIST *outer_join, + table_map deps_map); static void run_elimination_wave(Table_elimination *te, Module_dep *bound_modules); void eliminate_tables(JOIN *join); @@ -1212,15 +1215,19 @@ - expressions that depend on us. */ Field_value *field_dep= (Field_value*)bound_values; + DBUG_PRINT("info", ("field %s.%s is now bound", + field_dep->field->table->alias, + field_dep->field->field_name)); + for (Key_module *key_dep= field_dep->table->keys; key_dep; key_dep= key_dep->next_table_key) { - DBUG_PRINT("info", ("key %s.%s is now bound", - key_dep->table->table->alias, - key_dep->table->table->key_info[key_dep->keyno].name)); if (field_dep->field->part_of_key.is_set(key_dep->keyno) && key_dep->unknown_args && !--key_dep->unknown_args) { + DBUG_PRINT("info", ("key %s.%s is now bound", + key_dep->table->table->alias, + key_dep->table->table->key_info[key_dep->keyno].name)); /* Mark as bound and add to the list */ key_dep->next= bound_modules; bound_modules= key_dep;

1 0

[Maria-developers] MWL#39 improving mysqlbinlog output and doing rename
by Michael Widenius 17 Aug '09

17 Aug '09

Hi! >>>>> "Kristian" == Kristian Nielsen <knielsen(a)knielsen-hq.org> writes: Kristian> Sergey Petrunya <psergey(a)askmonty.org> writes: >> I've worked through the list and filed >> >> MWL#39 Replication tasks >> >> and its subordinate tasks: >> >> MWL#36 Add a mysqlbinlog option to change the used database >> MWL#37 Add an option to mysqlbinlog to produce SQL script with fewer roundtrips >> MWL#38 Make mysqlbinlog not to output unneeded statements when using --database >> MWL#40 Add a mysqlbinlog option to filter updates to certain tables >> MWL#41 Add a mysqlbinlog option to filter certain kinds of statements Kristian> Thanks, Sergey! I've started to work my way through them and the many Kristian> associated issues to understand for this. Kristian> One idea that occured to me and that I would like to bounce off of you others Kristian> (while I still continue thinking more about it and also working on the Kristian> original proposals): Kristian> In some sense the root of the problem is the magic "BINLOG" statement, which Kristian> really is not very nice. It is much much harder than it should be to see from Kristian> the binlog what data is actually changed from row-based events. Kristian> If the BINLOG statement was using proper syntax showing the actual data Kristian> changes, then the original customer problem would likely be solved as they Kristian> could treat it the same way as statement-based replication events. This is Kristian> especially nice when one considers that even row-based replication uses lots Kristian> of statement-based events, not to mention mixed-mode replication. Kristian> But readable BINLOG statements would be very nice in any case, and solve a Kristian> much more general problem than these worklogs, in an arguably nicer way. Note that it would also be very nice to have in the binlog the exact original statement: - 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 would be to have an approximately 2 as big binlog. (Especially insert of big blob's would be a bit painful). Kristian> There are also a number of potential problems that need careful consideration Kristian> of course. I haven't yet fully thought things through, just wanted to throw up Kristian> the idea. Kristian> I went throught the code for RBR events, and came up with the following Kristian> possible syntax: Kristian> BINLOG Kristian> WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 Kristian> TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 Kristian> TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 Kristian> WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 Kristian> UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), Kristian> FROM ('a') TO ('b') FLAGS 0x0 Kristian> DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0; Kristian> This is basically a dump of what is stored in the events, and would be an Kristian> alternative to BINLOG 'gwWEShMBAA...'. Kristian> So what do people think? The above would be a much better option than using the current syntax. Kristian> The implementation of this is not necessarily all that much harder than the Kristian> suggested worklogs under MWL#39 I think. The server upon reading this would Kristian> just reconstruct the binary representation of the binlog and proceed as the Kristian> old BINLOG statement. The mysqlbinlog program would just print out the Kristian> fields. The main complications are the addition of syntax to the Bison grammer Kristian> plus the need to handle all the possible types. Kristian> If we choose the easiest option in all the MWL#39 subtasks that would probably Kristian> be somewhat easier. On the other hand this would be a much more generally Kristian> useful feature, and would make trivial a lot of the suggested modifications to Kristian> mysqlbinlog. Which of the original customer problems would the above solve ? For exampling, doing general rename of databases wouldn't be much easier to do with the the above syntax (as we still need to handle SBR). Kristian> I'm pretty sure this would be easier than some of the harder options in the Kristian> MWL#39 subtasks. Which one are you thinking about ? (I like the proposed syntax, but don't grasp why things would be simple when doing this ). Kristian> So I need to think a bit more about this to make up my mind if I like it. But Kristian> I think at least it is a very interesting idea. Kristian> Comments? Kristian> (Also, isn't it time to move the discussion to the public Kristian> maria-developers(a)lists.launchpad.net list?) Done. Regards, Monty

3 6

[Maria-developers] Updated (by Psergey): mysqlbinlog: remove undesired effects of format description binlog statement (50)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: mysqlbinlog: remove undesired effects of format description binlog statement CREATION DATE..: Mon, 17 Aug 2009, 14:12 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 50 (http://askmonty.org/worklog/?tid=50) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 17 Aug 2009, 14:13)=-=- High-Level Specification modified. --- /tmp/wklog.50.old.11389 2009-08-17 14:13:05.000000000 +0300 +++ /tmp/wklog.50.new.11389 2009-08-17 14:13:05.000000000 +0300 @@ -1 +1,14 @@ +First item +---------- +AFAIU what needs to be done is: +1. record a source server version (it is in the first binlog event). +2. don't emit a BINLOG statement if the recorded version number is 5.0.x or + below. + +and we'll get a 5.0-applicable binlog. + +Second item +----------- +One question that one needs to sort out before disabling server_id change is +why it was put there in the first place? Should it be always removed? DESCRIPTION: According to complaints in BUG#46640: 1. mysqlbinlog will emit a 5.0-incompatible "format description binlog statement" even when reading a binary log that was produced by 5.0. This will cause an error when one tires to apply mysqlbinlog output to a 5.0.x server. 2. When one applies "format description binlog statement" at the slave, it will change the slave's server_id when applied. This WL is to fix these issues. HIGH-LEVEL SPECIFICATION: First item ---------- AFAIU what needs to be done is: 1. record a source server version (it is in the first binlog event). 2. don't emit a BINLOG statement if the recorded version number is 5.0.x or below. and we'll get a 5.0-applicable binlog. Second item ----------- One question that one needs to sort out before disabling server_id change is why it was put there in the first place? Should it be always removed? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): mysqlbinlog: remove undesired effects of format description binlog statement (50)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: mysqlbinlog: remove undesired effects of format description binlog statement CREATION DATE..: Mon, 17 Aug 2009, 14:12 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 50 (http://askmonty.org/worklog/?tid=50) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 17 Aug 2009, 14:13)=-=- High-Level Specification modified. --- /tmp/wklog.50.old.11389 2009-08-17 14:13:05.000000000 +0300 +++ /tmp/wklog.50.new.11389 2009-08-17 14:13:05.000000000 +0300 @@ -1 +1,14 @@ +First item +---------- +AFAIU what needs to be done is: +1. record a source server version (it is in the first binlog event). +2. don't emit a BINLOG statement if the recorded version number is 5.0.x or + below. + +and we'll get a 5.0-applicable binlog. + +Second item +----------- +One question that one needs to sort out before disabling server_id change is +why it was put there in the first place? Should it be always removed? DESCRIPTION: According to complaints in BUG#46640: 1. mysqlbinlog will emit a 5.0-incompatible "format description binlog statement" even when reading a binary log that was produced by 5.0. This will cause an error when one tires to apply mysqlbinlog output to a 5.0.x server. 2. When one applies "format description binlog statement" at the slave, it will change the slave's server_id when applied. This WL is to fix these issues. HIGH-LEVEL SPECIFICATION: First item ---------- AFAIU what needs to be done is: 1. record a source server version (it is in the first binlog event). 2. don't emit a BINLOG statement if the recorded version number is 5.0.x or below. and we'll get a 5.0-applicable binlog. Second item ----------- One question that one needs to sort out before disabling server_id change is why it was put there in the first place? Should it be always removed? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): mysqlbinlog: remove undesired effects of format description binlog statement (50)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: mysqlbinlog: remove undesired effects of format description binlog statement CREATION DATE..: Mon, 17 Aug 2009, 14:12 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 50 (http://askmonty.org/worklog/?tid=50) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: According to complaints in BUG#46640: 1. mysqlbinlog will emit a 5.0-incompatible "format description binlog statement" even when reading a binary log that was produced by 5.0. This will cause an error when one tires to apply mysqlbinlog output to a 5.0.x server. 2. When one applies "format description binlog statement" at the slave, it will change the slave's server_id when applied. This WL is to fix these issues. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): mysqlbinlog: remove undesired effects of format description binlog statement (50)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: mysqlbinlog: remove undesired effects of format description binlog statement CREATION DATE..: Mon, 17 Aug 2009, 14:12 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 50 (http://askmonty.org/worklog/?tid=50) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: According to complaints in BUG#46640: 1. mysqlbinlog will emit a 5.0-incompatible "format description binlog statement" even when reading a binary log that was produced by 5.0. This will cause an error when one tires to apply mysqlbinlog output to a 5.0.x server. 2. When one applies "format description binlog statement" at the slave, it will change the slave's server_id when applied. This WL is to fix these issues. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to produce succint output (45)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to produce succint output CREATION DATE..: Sat, 15 Aug 2009, 23:40 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 45 (http://askmonty.org/worklog/?tid=45) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 17 Aug 2009, 13:58)=-=- High Level Description modified. --- /tmp/wklog.45.old.10768 2009-08-17 13:58:07.000000000 +0300 +++ /tmp/wklog.45.new.10768 2009-08-17 13:58:07.000000000 +0300 @@ -4,3 +4,5 @@ This will be different from --short-form option. That option causes mysqlbinlog not to print RBR events, i.e. the output is not supposed to be applied. +There is also option to have --short-form do what this WL requires, see MySQL +BUG#18337. -=-=(Guest - Mon, 17 Aug 2009, 11:17)=-=- Dependency created: 39 now depends on 45 -=-=(Guest - Mon, 17 Aug 2009, 11:17)=-=- Dependency deleted: 39 no longer depends on 45 -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 45 -=-=(Psergey - Sat, 15 Aug 2009, 23:40)=-=- Title modified. --- /tmp/wklog.45.old.17603 2009-08-15 23:40:38.000000000 +0300 +++ /tmp/wklog.45.new.17603 2009-08-15 23:40:38.000000000 +0300 @@ -1 +1 @@ -Add a mysqlbinlog option to produce siccint output +Add a mysqlbinlog option to produce succint output DESCRIPTION: Add a mysqlbinlog option to produce the most succinct output, without any comments or other statements that are not needed to apply binlog correctly. This will be different from --short-form option. That option causes mysqlbinlog not to print RBR events, i.e. the output is not supposed to be applied. There is also option to have --short-form do what this WL requires, see MySQL BUG#18337. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to produce succint output (45)
by worklog-noreply＠askmonty.org 17 Aug '09

17 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to produce succint output CREATION DATE..: Sat, 15 Aug 2009, 23:40 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 45 (http://askmonty.org/worklog/?tid=45) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 17 Aug 2009, 13:58)=-=- High Level Description modified. --- /tmp/wklog.45.old.10768 2009-08-17 13:58:07.000000000 +0300 +++ /tmp/wklog.45.new.10768 2009-08-17 13:58:07.000000000 +0300 @@ -4,3 +4,5 @@ This will be different from --short-form option. That option causes mysqlbinlog not to print RBR events, i.e. the output is not supposed to be applied. +There is also option to have --short-form do what this WL requires, see MySQL +BUG#18337. -=-=(Guest - Mon, 17 Aug 2009, 11:17)=-=- Dependency created: 39 now depends on 45 -=-=(Guest - Mon, 17 Aug 2009, 11:17)=-=- Dependency deleted: 39 no longer depends on 45 -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 45 -=-=(Psergey - Sat, 15 Aug 2009, 23:40)=-=- Title modified. --- /tmp/wklog.45.old.17603 2009-08-15 23:40:38.000000000 +0300 +++ /tmp/wklog.45.new.17603 2009-08-15 23:40:38.000000000 +0300 @@ -1 +1 @@ -Add a mysqlbinlog option to produce siccint output +Add a mysqlbinlog option to produce succint output DESCRIPTION: Add a mysqlbinlog option to produce the most succinct output, without any comments or other statements that are not needed to apply binlog correctly. This will be different from --short-form option. That option causes mysqlbinlog not to print RBR events, i.e. the output is not supposed to be applied. There is also option to have --short-form do what this WL requires, see MySQL BUG#18337. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0