- developers - lists.mariadb.org

[Maria-developers] Rev 2728: - Better comments in file:///home/psergey/dev/maria-5.1-table-elim-r10/
by Sergey Petrunya 16 Aug '09

16 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2728 revision-id: psergey(a)askmonty.org-20090816124331-gd53m2alc0jb3ws4 parent: psergey(a)askmonty.org-20090816121708-v42h3mehvoy4c7yu committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Sun 2009-08-16 15:43:31 +0300 message: - Better comments - Add OOM error checking === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-16 12:17:08 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-16 12:43:31 +0000 @@ -184,18 +184,11 @@ unknown_args= n_children; } /* - Outer join we're representing. This can be a join nest or a one table that + Outer join we're representing. This can be a join nest or one table that is outer join'ed. */ TABLE_LIST *table_list; - /* - Tables within this outer join (and its descendants) that are not yet known - to be functionally dependent. - */ - table_map missing_tables; //psergey-todo: remove - /* All tables within this outer join and its descendants */ - table_map all_tables; //psergey-todo: remove /* Parent eliminable outer join, if any */ Outer_join_module *parent; }; @@ -229,11 +222,11 @@ }; static -void build_eq_deps_for_cond(Table_elimination *te, Equality_module **fdeps, +bool build_eq_deps_for_cond(Table_elimination *te, Equality_module **fdeps, uint *and_level, Item *cond, table_map usable_tables); static -void add_eq_dep(Table_elimination *te, Equality_module **eq_dep, +bool add_eq_dep(Table_elimination *te, Equality_module **eq_dep, uint and_level, Item_func *cond, Item *left, Item *right, table_map usable_tables); @@ -270,7 +263,7 @@ */ static -void build_eq_deps_for_cond(Table_elimination *te, Equality_module **fdeps, +bool build_eq_deps_for_cond(Table_elimination *te, Equality_module **fdeps, uint *and_level, Item *cond, table_map usable_tables) { @@ -285,7 +278,8 @@ Item *item; while ((item=li++)) { - build_eq_deps_for_cond(te, fdeps, and_level, item, usable_tables); + if (build_eq_deps_for_cond(te, fdeps, and_level, item, usable_tables)) + return TRUE; } /* TODO: inject here a "if we have {t.col=const AND t.col=smth_else}, then @@ -297,47 +291,52 @@ else { (*and_level)++; - build_eq_deps_for_cond(te, fdeps, and_level, li++, usable_tables); + if (build_eq_deps_for_cond(te, fdeps, and_level, li++, usable_tables)) + return TRUE; Item *item; while ((item=li++)) { Equality_module *start_key_fields= *fdeps; (*and_level)++; - build_eq_deps_for_cond(te, fdeps, and_level, item, usable_tables); + if (build_eq_deps_for_cond(te, fdeps, and_level, item, usable_tables)) + return TRUE; *fdeps= merge_func_deps(org_key_fields, start_key_fields, *fdeps, ++(*and_level)); } } - return; + return FALSE; } if (cond->type() != Item::FUNC_ITEM) - return; + return FALSE; Item_func *cond_func= (Item_func*) cond; Item **args= cond_func->arguments(); - Item *fld; switch (cond_func->functype()) { case Item_func::IN_FUNC: { if (cond_func->argument_count() == 2) { - add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], - usable_tables); - add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], - usable_tables); + if (add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables) || + add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], + usable_tables)) + return TRUE; } } case Item_func::BETWEEN: { + Item *fld; if (!((Item_func_between*)cond)->negated && + (fld= args[0]->real_item())->type() == Item::FIELD_ITEM && args[1]->eq(args[2], ((Item_field*)fld)->field->binary())) { - add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], - usable_tables); - add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], - usable_tables); + if (add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables) || + add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], + usable_tables)) + return TRUE; } break; } @@ -353,10 +352,9 @@ case Item_func::ISNULL_FUNC: { Item *tmp=new Item_null; - if (unlikely(!tmp)) // Should never be true - return; - add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], - usable_tables); + if (!tmp || add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables)) + return TRUE; break; } case Item_func::MULT_EQUAL_FUNC: @@ -374,8 +372,9 @@ */ while ((item= it++)) { - add_eq_dep(te, fdeps, *and_level, cond_func, item, const_item, - usable_tables); + if (add_eq_dep(te, fdeps, *and_level, cond_func, item, const_item, + usable_tables)) + return TRUE; } } else @@ -395,8 +394,9 @@ { if (!field->eq(item2->field)) { - add_eq_dep(te, fdeps, *and_level, cond_func, item, item2, - usable_tables); + if (add_eq_dep(te, fdeps, *and_level, cond_func, item, item2, + usable_tables)) + return TRUE; } } it.rewind(); @@ -407,6 +407,7 @@ default: break; } + return FALSE; } @@ -536,7 +537,7 @@ */ static -void add_eq_dep(Table_elimination *te, Equality_module **eq_dep, +bool add_eq_dep(Table_elimination *te, Equality_module **eq_dep, uint and_level, Item_func *cond, Item *left, Item *right, table_map usable_tables) { @@ -550,7 +551,7 @@ if (right->result_type() != STRING_RESULT) { if (field->cmp_type() != right->result_type()) - return; + return FALSE; } else { @@ -560,17 +561,19 @@ */ if (field->cmp_type() == STRING_RESULT && ((Field_str*)field)->charset() != cond->compare_collation()) - return; + return FALSE; } } /* Store possible eq field */ (*eq_dep)->type= Module_dep::MODULE_EXPRESSION; //psergey-todo; - (*eq_dep)->field= get_field_value(te, field); + if (!((*eq_dep)->field= get_field_value(te, field))) + return TRUE; (*eq_dep)->expression= right; (*eq_dep)->level= and_level; (*eq_dep)++; } + return FALSE; }

1 0

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

16 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2727 revision-id: psergey(a)askmonty.org-20090816121708-v42h3mehvoy4c7yu parent: psergey(a)askmonty.org-20090816091549-da84w3nlmx8prmvm committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Sun 2009-08-16 15:17:08 +0300 message: MWL#17: Table elimination - Address review feedback: change expression analyzer used to be a copy-paste of ref analyzer. ref analyzer, besides doing ref analysis, also collected info about keys which had sargable predicates. We didn't need that part here. === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-16 09:15:49 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-16 12:17:08 +0000 @@ -233,11 +233,10 @@ uint *and_level, Item *cond, table_map usable_tables); static -void add_eq_dep(Table_elimination *te, - Equality_module **eq_dep, uint and_level, - Item_func *cond, Field *field, - bool eq_func, Item **value, - uint num_values, table_map usable_tables); +void add_eq_dep(Table_elimination *te, Equality_module **eq_dep, + uint and_level, + Item_func *cond, Item *left, Item *right, + table_map usable_tables); static Equality_module *merge_func_deps(Equality_module *start, Equality_module *new_fields, Equality_module *end, uint and_level); @@ -314,87 +313,54 @@ if (cond->type() != Item::FUNC_ITEM) return; + Item_func *cond_func= (Item_func*) cond; - switch (cond_func->select_optimize()) { - case Item_func::OPTIMIZE_NONE: - break; - case Item_func::OPTIMIZE_KEY: - { - Item **values; - // BETWEEN, IN, NE - if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM && - !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) - { - values= cond_func->arguments()+1; - if (cond_func->functype() == Item_func::NE_FUNC && - cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM && - !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT)) - values--; - DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC || - cond_func->argument_count() != 2); - add_eq_dep(te, fdeps, *and_level, cond_func, - ((Item_field*)(cond_func->key_item()->real_item()))->field, - 0, values, - cond_func->argument_count()-1, - usable_tables); - } - if (cond_func->functype() == Item_func::BETWEEN) - { - values= cond_func->arguments(); - for (uint i= 1 ; i < cond_func->argument_count() ; i++) - { - Item_field *field_item; - if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM - && - !(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT)) - { - field_item= (Item_field *) (cond_func->arguments()[i]->real_item()); - add_eq_dep(te, fdeps, *and_level, cond_func, - field_item->field, 0, values, 1, usable_tables); - } - } - } - break; - } - case Item_func::OPTIMIZE_OP: - { - bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC || - cond_func->functype() == Item_func::EQUAL_FUNC); + Item **args= cond_func->arguments(); + Item *fld; - if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM && - !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT)) - { - add_eq_dep(te, fdeps, *and_level, cond_func, - ((Item_field*)(cond_func->arguments()[0])->real_item())->field, - equal_func, - cond_func->arguments()+1, 1, usable_tables); - } - if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM && - cond_func->functype() != Item_func::LIKE_FUNC && - !(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT)) - { - add_eq_dep(te, fdeps, *and_level, cond_func, - ((Item_field*)(cond_func->arguments()[1])->real_item())->field, - equal_func, - cond_func->arguments(),1,usable_tables); - } - break; - } - case Item_func::OPTIMIZE_NULL: - /* column_name IS [NOT] NULL */ - if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM && - !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) - { - Item *tmp=new Item_null; - if (unlikely(!tmp)) // Should never be true - return; - add_eq_dep(te, fdeps, *and_level, cond_func, - ((Item_field*)(cond_func->arguments()[0])->real_item())->field, - cond_func->functype() == Item_func::ISNULL_FUNC, - &tmp, 1, usable_tables); - } - break; - case Item_func::OPTIMIZE_EQUAL: + switch (cond_func->functype()) { + case Item_func::IN_FUNC: + { + if (cond_func->argument_count() == 2) + { + add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables); + add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], + usable_tables); + } + } + case Item_func::BETWEEN: + { + if (!((Item_func_between*)cond)->negated && + args[1]->eq(args[2], ((Item_field*)fld)->field->binary())) + { + add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables); + add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], + usable_tables); + } + break; + } + case Item_func::EQ_FUNC: + case Item_func::EQUAL_FUNC: + { + add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables); + add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0], + usable_tables); + break; + } + case Item_func::ISNULL_FUNC: + { + Item *tmp=new Item_null; + if (unlikely(!tmp)) // Should never be true + return; + add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1], + usable_tables); + break; + } + case Item_func::MULT_EQUAL_FUNC: + { Item_equal *item_equal= (Item_equal *) cond; Item *const_item= item_equal->get_const(); Item_equal_iterator it(*item_equal); @@ -408,8 +374,8 @@ */ while ((item= it++)) { - add_eq_dep(te, fdeps, *and_level, cond_func, item->field, - TRUE, &const_item, 1, usable_tables); + add_eq_dep(te, fdeps, *and_level, cond_func, item, const_item, + usable_tables); } } else @@ -424,12 +390,13 @@ while ((item= fi++)) { Field *field= item->field; - while ((item= it++)) + Item_field *item2; + while ((item2= it++)) { - if (!field->eq(item->field)) + if (!field->eq(item2->field)) { - add_eq_dep(te, fdeps, *and_level, cond_func, field, - TRUE, (Item **) &item, 1, usable_tables); + add_eq_dep(te, fdeps, *and_level, cond_func, item, item2, + usable_tables); } } it.rewind(); @@ -437,6 +404,9 @@ } break; } + default: + break; + } } @@ -567,75 +537,40 @@ static void add_eq_dep(Table_elimination *te, Equality_module **eq_dep, - uint and_level, Item_func *cond, Field *field, - bool eq_func, Item **value, uint num_values, - table_map usable_tables) + uint and_level, Item_func *cond, + Item *left, Item *right, table_map usable_tables) { - if (!(field->table->map & usable_tables)) - return; - - for (uint i=0; i<num_values; i++) - { - if ((value[i])->used_tables() & RAND_TABLE_BIT) - return; - } - - /* - Save the following cases: - Field op constant - Field LIKE constant where constant doesn't start with a wildcard - Field = field2 where field2 is in a different table - Field op formula - Field IS NULL - Field IS NOT NULL - Field BETWEEN ... - Field IN ... - */ - - /* - We can't always use indexes when comparing a string index to a - number. cmp_type() is checked to allow compare of dates to numbers. - eq_func is NEVER true when num_values > 1 - */ - if (!eq_func) - { - /* - Additional optimization: if we're processing "t.key BETWEEN c1 AND c1" - then proceed as if we were processing "t.key = c1". - */ - if ((cond->functype() != Item_func::BETWEEN) || - ((Item_func_between*) cond)->negated || - !value[0]->eq(value[1], field->binary())) - return; - eq_func= TRUE; - } - - if (field->result_type() == STRING_RESULT) - { - if ((*value)->result_type() != STRING_RESULT) - { - if (field->cmp_type() != (*value)->result_type()) - return; - } - else - { - /* - We can't use indexes if the effective collation - of the operation differ from the field collation. - */ - if (field->cmp_type() == STRING_RESULT && - ((Field_str*)field)->charset() != cond->compare_collation()) - return; - } - } - - DBUG_ASSERT(eq_func); - /* Store possible eq field */ - (*eq_dep)->type= Module_dep::MODULE_EXPRESSION; //psergey-todo; - (*eq_dep)->field= get_field_value(te, field); - (*eq_dep)->expression= *value; - (*eq_dep)->level= and_level; - (*eq_dep)++; + if ((left->used_tables() & usable_tables) && + !(right->used_tables() & RAND_TABLE_BIT) && + left->real_item()->type() == Item::FIELD_ITEM) + { + Field *field= ((Item_field*)left->real_item())->field; + if (field->result_type() == STRING_RESULT) + { + if (right->result_type() != STRING_RESULT) + { + if (field->cmp_type() != right->result_type()) + return; + } + else + { + /* + We can't use indexes if the effective collation + of the operation differ from the field collation. + */ + if (field->cmp_type() == STRING_RESULT && + ((Field_str*)field)->charset() != cond->compare_collation()) + return; + } + } + + /* Store possible eq field */ + (*eq_dep)->type= Module_dep::MODULE_EXPRESSION; //psergey-todo; + (*eq_dep)->field= get_field_value(te, field); + (*eq_dep)->expression= right; + (*eq_dep)->level= and_level; + (*eq_dep)++; + } } @@ -1150,6 +1085,12 @@ { 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; + } break; } case Module_dep::MODULE_MULTI_EQUALITY:

1 0

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

16 Aug '09

At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2726 revision-id: psergey(a)askmonty.org-20090816091549-da84w3nlmx8prmvm parent: psergey(a)askmonty.org-20090816072524-w9fu2hy23pjwlr8z committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Sun 2009-08-16 12:15:49 +0300 message: MWL#17: Table elimination - code cleanup === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-16 07:25:24 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-16 09:15:49 +0000 @@ -125,7 +125,7 @@ make elements that depend on them bound, too. */ Module_dep *next; - uint unknown_args; /* TRUE<=> The entity is considered bound */ + uint unknown_args; Module_dep() : next(NULL), unknown_args(0) {} }; @@ -249,11 +249,9 @@ void eliminate_tables(JOIN *join); static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl); -#if 0 #ifndef DBUG_OFF static void dbug_print_deps(Table_elimination *te); #endif -#endif /*******************************************************************************************/ /* @@ -854,7 +852,7 @@ /* - This is used to analyse expressions in "tbl.col=expr" dependencies so + This is used to analyze expressions in "tbl.col=expr" dependencies so that we can figure out which fields the expression depends on. */ @@ -965,7 +963,7 @@ } *bound_deps_list= bound_dep; - //DBUG_EXECUTE("test", dbug_print_deps(te); ); + DBUG_EXECUTE("test", dbug_print_deps(te); ); DBUG_RETURN(FALSE); } @@ -1089,7 +1087,6 @@ void signal_from_field_to_exprs(Table_elimination* te, Field_value *field_dep, Module_dep **bound_modules) { - /* Now, expressions */ for (uint i=0; i < te->n_equality_deps; i++) { if (bitmap_is_set(&te->expr_deps, field_dep->bitmap_offset + i) && @@ -1213,7 +1210,6 @@ 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->missing_tables &= ~table_dep->table->map)) if (outer_join_dep->unknown_args && !--outer_join_dep->unknown_args) { @@ -1268,7 +1264,6 @@ } -#if 0 #ifndef DBUG_OFF static void dbug_print_deps(Table_elimination *te) @@ -1324,7 +1319,6 @@ } #endif -#endif /** @} (end of group Table_Elimination) */

1 0

[Maria-developers] Updated (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 16 Aug 2009, 12:06)=-=- High-Level Specification modified. --- /tmp/wklog.49.old.14928 2009-08-16 12:06:27.000000000 +0300 +++ /tmp/wklog.49.new.14928 2009-08-16 12:06:27.000000000 +0300 @@ -1 +1,12 @@ +Some notes: + +The only required changes are on the slave. The slave can see bounds between +statements (they are delimited by Query_event and Table_map_event entries), +Table_map_event lists all tables that are going to be updated => it is possible +to make a decision whether we should skip the statement, and if yes, skip all +RBR events that belong to the statement. + +Possible syntax for options +--replicate-wild-ignore-stmt-with-table=%.tmptbl% +--replicate-ignore-stmt-with-table=tmptbl -=-=(Psergey - Sun, 16 Aug 2009, 12:02)=-=- High Level Description modified. --- /tmp/wklog.49.old.14739 2009-08-16 12:02:05.000000000 +0300 +++ /tmp/wklog.49.new.14739 2009-08-16 12:02:05.000000000 +0300 @@ -6,8 +6,8 @@ This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. -This WL entry is about making processing of RBR events to work the same as SBR -events did. +This WL entry is about adding an option to make processing of RBR events to work +the same as SBR events did. DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about adding an option to make processing of RBR events to work the same as SBR events did. HIGH-LEVEL SPECIFICATION: Some notes: The only required changes are on the slave. The slave can see bounds between statements (they are delimited by Query_event and Table_map_event entries), Table_map_event lists all tables that are going to be updated => it is possible to make a decision whether we should skip the statement, and if yes, skip all RBR events that belong to the statement. Possible syntax for options --replicate-wild-ignore-stmt-with-table=%.tmptbl% --replicate-ignore-stmt-with-table=tmptbl ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 16 Aug 2009, 12:06)=-=- High-Level Specification modified. --- /tmp/wklog.49.old.14928 2009-08-16 12:06:27.000000000 +0300 +++ /tmp/wklog.49.new.14928 2009-08-16 12:06:27.000000000 +0300 @@ -1 +1,12 @@ +Some notes: + +The only required changes are on the slave. The slave can see bounds between +statements (they are delimited by Query_event and Table_map_event entries), +Table_map_event lists all tables that are going to be updated => it is possible +to make a decision whether we should skip the statement, and if yes, skip all +RBR events that belong to the statement. + +Possible syntax for options +--replicate-wild-ignore-stmt-with-table=%.tmptbl% +--replicate-ignore-stmt-with-table=tmptbl -=-=(Psergey - Sun, 16 Aug 2009, 12:02)=-=- High Level Description modified. --- /tmp/wklog.49.old.14739 2009-08-16 12:02:05.000000000 +0300 +++ /tmp/wklog.49.new.14739 2009-08-16 12:02:05.000000000 +0300 @@ -6,8 +6,8 @@ This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. -This WL entry is about making processing of RBR events to work the same as SBR -events did. +This WL entry is about adding an option to make processing of RBR events to work +the same as SBR events did. DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about adding an option to make processing of RBR events to work the same as SBR events did. HIGH-LEVEL SPECIFICATION: Some notes: The only required changes are on the slave. The slave can see bounds between statements (they are delimited by Query_event and Table_map_event entries), Table_map_event lists all tables that are going to be updated => it is possible to make a decision whether we should skip the statement, and if yes, skip all RBR events that belong to the statement. Possible syntax for options --replicate-wild-ignore-stmt-with-table=%.tmptbl% --replicate-ignore-stmt-with-table=tmptbl ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 16 Aug 2009, 12:02)=-=- High Level Description modified. --- /tmp/wklog.49.old.14739 2009-08-16 12:02:05.000000000 +0300 +++ /tmp/wklog.49.new.14739 2009-08-16 12:02:05.000000000 +0300 @@ -6,8 +6,8 @@ This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. -This WL entry is about making processing of RBR events to work the same as SBR -events did. +This WL entry is about adding an option to make processing of RBR events to work +the same as SBR events did. DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about adding an option to make processing of RBR events to work the same as SBR events did. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 16 Aug 2009, 12:02)=-=- High Level Description modified. --- /tmp/wklog.49.old.14739 2009-08-16 12:02:05.000000000 +0300 +++ /tmp/wklog.49.new.14739 2009-08-16 12:02:05.000000000 +0300 @@ -6,8 +6,8 @@ This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. -This WL entry is about making processing of RBR events to work the same as SBR -events did. +This WL entry is about adding an option to make processing of RBR events to work +the same as SBR events did. DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about adding an option to make processing of RBR events to work the same as SBR events did. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about making processing of RBR events to work the same as SBR events did. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Make --replicate-(do, ignore)-(db, table) behaviour for RBR identical to that of SBR (49)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make --replicate-(do,ignore)-(db,table) behaviour for RBR identical to that of SBR CREATION DATE..: Sun, 16 Aug 2009, 12:01 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 49 (http://askmonty.org/worklog/?tid=49) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: At the moment semantics of --replicate-(do,ignore)-(db,table) rules is different for RBR and SBR: http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html This can be inconvenient, and also the semantics gets really complicated when --binlog_format=mixed is used. This WL entry is about making processing of RBR events to work the same as SBR events did. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Change BINLOG statement syntax to be human-readable (46)
by worklog-noreply＠askmonty.org 16 Aug '09

16 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Change BINLOG statement syntax to be human-readable CREATION DATE..: Sat, 15 Aug 2009, 23:42 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 46 (http://askmonty.org/worklog/?tid=46) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 16 Aug 2009, 11:30)=-=- High-Level Specification modified. --- /tmp/wklog.46.old.13453 2009-08-16 11:30:06.000000000 +0300 +++ /tmp/wklog.46.new.13453 2009-08-16 11:30:06.000000000 +0300 @@ -26,3 +26,7 @@ (due to locking, table open/close, etc). (TODO: is it really slower? we haven't checked). +* When SBR replication is used and the statements refer to the current database + (a common scenario), one can use awk to filter out updates made in certain + databases. The proposed syntax doesn't allow to perform equivalent filtering? + -=-=(Psergey - Sun, 16 Aug 2009, 11:13)=-=- High Level Description modified. --- /tmp/wklog.46.old.12747 2009-08-16 11:13:54.000000000 +0300 +++ /tmp/wklog.46.new.12747 2009-08-16 11:13:54.000000000 +0300 @@ -6,4 +6,4 @@ This WL task is about making BINLOG statements to be human-readable (either as an option or by default -The approach of this WL is to some extent an alternative to WL#38, WL#40, WL41. +The approach of this WL is to some extent an alternative to WL#38, WL#40, WL#41. -=-=(Psergey - Sun, 16 Aug 2009, 11:13)=-=- High Level Description modified. --- /tmp/wklog.46.old.12717 2009-08-16 11:13:40.000000000 +0300 +++ /tmp/wklog.46.new.12717 2009-08-16 11:13:40.000000000 +0300 @@ -5,3 +5,5 @@ This WL task is about making BINLOG statements to be human-readable (either as an option or by default + +The approach of this WL is to some extent an alternative to WL#38, WL#40, WL41. -=-=(Psergey - Sun, 16 Aug 2009, 11:07)=-=- Dependency created: 39 now depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 11:07)=-=- Dependency deleted: 48 no longer depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 10:59)=-=- Dependency created: 48 now depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 10:59)=-=- Dependency deleted: 39 no longer depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 46 -=-=(Psergey - Sat, 15 Aug 2009, 23:43)=-=- High-Level Specification modified. --- /tmp/wklog.46.old.17742 2009-08-15 23:43:09.000000000 +0300 +++ /tmp/wklog.46.new.17742 2009-08-15 23:43:09.000000000 +0300 @@ -1 +1,28 @@ +Suggestion 1 +------------ +Original syntax suggestion by Kristian: + + BINLOG + WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 + TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 + TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 + WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 + UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), + FROM ('a') TO ('b') FLAGS 0x0 + DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0; + + This is basically a dump of what is stored in the events, and would be an + alternative to BINLOG 'gwWEShMBAA...'. + +Feedback and other suggestions +------------------------------ +* What is the need for WITH TIMESTAMP part? Can't one use a separate + SET TIMESTAMP statement? + +* mysqlbinlog --base64-output=DECODE-ROWS --verbose already produces something + that's close to readable SQL. Can we make it to be regular parseable SQL? + + This will be syntax that's familiar to our parser and to the users + - A stream of SQL statements will be slower to run than BINLOG statements + (due to locking, table open/close, etc). (TODO: is it really slower? we + haven't checked). DESCRIPTION: One of great things about mysqlbinlog was that its output was human-readable SQL, so it was possible to edit it manually or with help of scripts. With RBR events and BINLOG 'DpiGShMBAAAALQAAADcBAA...' statements this is no longer the case. This WL task is about making BINLOG statements to be human-readable (either as an option or by default The approach of this WL is to some extent an alternative to WL#38, WL#40, WL#41. HIGH-LEVEL SPECIFICATION: Suggestion 1 ------------ Original syntax suggestion by Kristian: BINLOG WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), FROM ('a') TO ('b') FLAGS 0x0 DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0; This is basically a dump of what is stored in the events, and would be an alternative to BINLOG 'gwWEShMBAA...'. Feedback and other suggestions ------------------------------ * What is the need for WITH TIMESTAMP part? Can't one use a separate SET TIMESTAMP statement? * mysqlbinlog --base64-output=DECODE-ROWS --verbose already produces something that's close to readable SQL. Can we make it to be regular parseable SQL? + This will be syntax that's familiar to our parser and to the users - A stream of SQL statements will be slower to run than BINLOG statements (due to locking, table open/close, etc). (TODO: is it really slower? we haven't checked). * When SBR replication is used and the statements refer to the current database (a common scenario), one can use awk to filter out updates made in certain databases. The proposed syntax doesn't allow to perform equivalent filtering? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0