- developers - lists.mariadb.org

[Maria-developers] New (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] WL47: Second suggestion.
by Alexi1952 25 Nov '09

25 Nov '09

Having to start with WL47 which already contains the 'First suggestion', I'd like to discuss: Second suggestion ~~~~~~~~~~~~~~~~~ Add query text to the RBR events data part (together with adding query_len field to their header). Reason (compared with the First suggestion): - this makes binary log bit smaller (no need to store headers of new events). - almost all changes needed in this case are localized in the RBR-event classes (e.g. replication, being, roughly speaking, based on calling the apply_event() and other Log_event methods, shouldn't care about whether there is a query_text in the event or not). We can recognize whether an RBR-event in a binary log contain query text or not by looking at it header length in the Format description event (the post_header member): containing the additional query_len field, the header of the 'extended' event have a diferent size. Because we are able to recognize RBR-events format _only_ by the Format description event (at the beginning of the binary log), we can not allow to mix in one binary log both 'standard' and 'extended' formats (which is possible within the First suggestion). If possibility of such a mix is important then we should prefer the First suggestion. Any remarks ? Which way to go ?

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2772)
by knielsen＠knielsen-hq.org 25 Nov '09

25 Nov '09

#At lp:maria 2772 knielsen(a)knielsen-hq.org 2009-11-25 After-merge fixes following Merge of Mysql 5.1.41 into MariaDB: Fix path for SSL stuff when running testsuite from installed package. modified: mysql-test/lib/My/ConfigFactory.pm === modified file 'mysql-test/lib/My/ConfigFactory.pm' --- a/mysql-test/lib/My/ConfigFactory.pm 2009-11-16 20:49:51 +0000 +++ b/mysql-test/lib/My/ConfigFactory.pm 2009-11-25 13:00:28 +0000 @@ -142,8 +142,7 @@ sub fix_secure_file_priv { sub fix_std_data { my ($self, $config, $group_name, $group)= @_; - my $basedir= $self->get_basedir($group); - return "$basedir/mysql-test/std_data"; + return "$::opt_vardir/std_data"; } sub ssl_supported {

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2771)
by knielsen＠knielsen-hq.org 25 Nov '09

25 Nov '09

#At lp:maria 2771 knielsen(a)knielsen-hq.org 2009-11-25 After-merge fix for merge of MySQL 5.1.41 into MariaDB: Valgrind fixes. modified: mysql-test/mysql-test-run.pl sql/log_event.cc per-file messages: mysql-test/mysql-test-run.pl Fix Valgrind warnings: add more post-shutdown warning suppressions, and revert bad previous change. sql/log_event.cc Manually apply fix for Bug#48340 (basically missing initialisation of thd->lex->local_file in Load_log_event::do_apply_event()) === modified file 'mysql-test/mysql-test-run.pl' --- a/mysql-test/mysql-test-run.pl 2009-11-24 09:34:42 +0000 +++ b/mysql-test/mysql-test-run.pl 2009-11-25 11:06:01 +0000 @@ -223,7 +223,7 @@ my $opt_strace_client; our $opt_user = "root"; my $opt_valgrind= 0; -my $opt_valgrind_mysqld= 0; +our $opt_valgrind_mysqld= 0; my $opt_valgrind_mysqltest= 0; my @default_valgrind_args= ("--show-reachable=yes"); my @valgrind_args; @@ -3990,6 +3990,8 @@ sub extract_warning_lines ($) { qr/unknown variable 'loose-/, qr/Now setting lower_case_table_names to [02]/, qr/deprecated/, + qr/Slave SQL thread retried transaction/, + qw/Slave $additional info$/, ); my $match_count= 0; === modified file 'sql/log_event.cc' --- a/sql/log_event.cc 2009-11-16 20:49:51 +0000 +++ b/sql/log_event.cc 2009-11-25 11:06:01 +0000 @@ -4509,6 +4509,7 @@ int Load_log_event::do_apply_event(NET* as the present method does not call mysql_parse(). */ lex_start(thd); + thd->lex->local_file= local_fname; mysql_reset_thd_for_next_command(thd); if (!use_rli_only_for_errors)

1 0

[Maria-developers] Updated (by Guest): Add a mysqlbinlog option to filter updates to certain tables (40)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter updates to certain tables CREATION DATE..: Mon, 10 Aug 2009, 13:25 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-Sprint TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 32 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 48 PROGRESS NOTES: -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Status updated. --- /tmp/wklog.40.old.5760 2009-11-25 11:41:09.000000000 +0200 +++ /tmp/wklog.40.new.5760 2009-11-25 11:41:09.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Category updated. --- /tmp/wklog.40.old.5737 2009-11-25 11:41:03.000000000 +0200 +++ /tmp/wklog.40.new.5737 2009-11-25 11:41:03.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Bothorsen - Tue, 17 Nov 2009, 17:20)=-=- Alex is closer to a working patch now. Worked 14 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Bothorsen - Thu, 12 Nov 2009, 13:13)=-=- Work hours by Alexi and Bo + estimated time for the task. Worked 16 hours and estimate 46 hours remain (original estimate increased by 14 hours). -=-=(Alexi - Sun, 08 Nov 2009, 15:18)=-=- Low Level Design modified. --- /tmp/wklog.40.old.15787 2009-11-08 15:18:11.000000000 +0200 +++ /tmp/wklog.40.new.15787 2009-11-08 15:18:11.000000000 +0200 @@ -62,7 +62,7 @@ it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. -3. db_len = 0 means that this is the current db. +3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 @@ -75,6 +75,77 @@ + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +*********************************************************************** +HELP NEEDED +*********************************************************************** +The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: + +log_event.h +~~~~~~~~~~~ +#define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ + LOG_EVENT_HEADER_LEN + /* write_header */ \ + QUERY_HEADER_LEN + /* write_data */ \ + EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ + MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ + NAME_LEN + 1) + +which is used only for setting + + thd->variables.max_allowed_packet + mysql->net.max_packet_size + +Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply +(without making any other changes) be substituted in this definition by +QUERY_HEADER_LEN_EXT. + +Below I list all places where MAX_LOG_EVENT_HEADER is used: + +slave.cc +~~~~~~~~ +static int init_slave_thread(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all + slave threads, since a replication event can become this much larger + than the corresponding packet (query) sent from client to master. + */ + thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ + ... +} +pthread_handler_t handle_slave_io(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O + thread, since a replication event can become this much larger than + the corresponding packet (query) sent from client to master. + */ + mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; + ... +} + +sql_repl.cc +~~~~~~~~~~~ +void mysql_binlog_send(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become + this larger than the corresponding packet (query) sent + from client to master. + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} +bool mysql_show_binlog_events(...) +{ ... + /* + to account binlog event header size + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} + 3. Changes in log events ************************ @@ -84,7 +155,7 @@ This setting is done in Format description event constructor which creates the event for writing to binary log: - if (binlog_with_tables_info) + if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; @@ -99,12 +170,12 @@ following manner: switch (binlog_ver) { - case 4: /* MySQL 5.0 and higher */ + #ifndef MYSQL_CLIENT + case 4: /* MySQL 5.0 and higher */ ... -+ #else -+ <error> + break; + #endif + case 1: case 3: ... @@ -132,7 +203,7 @@ -------------------------------- [Creates the event for binlogging] -In case of binlog_with_tables_info = TRUE, set additionally query_len, +In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). @@ -140,7 +211,7 @@ ---------------- [Writes the event to binlog] -In case of binlog_with_tables_info = TRUE, write additional members +In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. -=-=(Alexi - Sun, 08 Nov 2009, 10:40)=-=- Low Level Design modified. --- /tmp/wklog.40.old.5055 2009-11-08 08:40:02.000000000 +0000 +++ /tmp/wklog.40.new.5055 2009-11-08 08:40:02.000000000 +0000 @@ -3,6 +3,7 @@ 1. Adding --binlog-with-tables-info option ****************************************** +GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events -=-=(Alexi - Thu, 05 Nov 2009, 12:37)=-=- Low Level Design modified. --- /tmp/wklog.40.old.11441 2009-11-05 12:37:16.000000000 +0200 +++ /tmp/wklog.40.new.11441 2009-11-05 12:37:16.000000000 +0200 @@ -1,9 +1,18 @@ OPTION: 2.5 Extend Query Events With Tables Info ================================================ -1. Query_log_event Binary Format -******************************** -Changes to be done: +1. Adding --binlog-with-tables-info option +****************************************** + +When set, Query events are to be written in the extended binary +format which contains tables_info. When not set, Query events +are to be written in usual format (without any changes). + +2. Query event extended binary format +************************************* + +When --binlog-with-tables-info is set, Query events are writen +to binary log in the following (extended) format. Query_log_event binary format --------------------------------- @@ -24,12 +33,12 @@ error_code 2 status_vars_len 2 + query_len 2 (see Note 1) -+ tables_info_len 2 (see Note 2) ++ tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 -+ db db_len (see Note 3) ++ db db_len (see Note 2) query query_len + tables_info @@ -37,7 +46,7 @@ --------------------------------- Name Size (bytes) --------------------------------- - db_len 1 (see Note 4) + db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len @@ -48,19 +57,99 @@ table_name table_name_len NOTES -1. Currently Query_log_event format doesn't include 'query_len' because +1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. -2. If tables_info is not included in the event (--binlog-with-tables-info - option), tables_info_len = 0. -3. The trailing zero is redundant since the length is already known. -4. In case of db = current db, db_len = 0 and db = empty, because - current db is already included in the current event format. +2. For 'db' (current db) the trailing zero is redundant since the length + is already known. +3. db_len = 0 means that this is the current db. + +When reading Query events from binary log, we can recognize its format +by its post-header length: in extended case the post-header includes 4 +additional bytes. + + #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) ++ #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) + ... + #define Q_STATUS_VARS_LEN_OFFSET 11 ++ #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 ++ #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +3. Changes in log events +************************ + +3.1. Format description event +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Changes needed here concern setting post-header length for Query events. +This setting is done in Format description event constructor which creates +the event for writing to binary log: + + if (binlog_with_tables_info) + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; + else + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; + +This change is to be done only for case binlog_ver = 4. + +NOTE. The refered above constructor is allowed to be invoked in a client +context for creating "artificial" Format description events in case of +MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems +(because of 'binlog_with_tables_info') and taking into account the +"MySQL < 5.0" restriction, we have to #ifdef out the above code in +following manner: + + switch (binlog_ver) { + case 4: /* MySQL 5.0 and higher */ ++ #ifndef MYSQL_CLIENT + ... ++ #else ++ <error> ++ #endif + case 1: + case 3: + ... + } + +3.2. Query event +~~~~~~~~~~~~~~~~ +Changes needed here include adding tables_info and tables_info_len +members (member for query length already exists) and modifying the +following function-members: + +Query_log_event(buf) constructor +-------------------------------- +[Parses binary format written to the 'buf'] + +Getting post-header length from the Format description event (passed +to the constructor as an argument), define whether buf contains an +extended or usual Query event and parse the buf contents accordingly. + +NOTE. Defining Query event format here should be done with taking into +account that this constructor can be called within a Query-derived +event with the event_type argument != QUERY_EVENT. + +Query_log_event(thd) constructor +-------------------------------- +[Creates the event for binlogging] + +In case of binlog_with_tables_info = TRUE, set additionally query_len, +tables_info_len, and tables_info members (the constructor is to have +an additional 'tables_info' argument). + +write() function +---------------- +[Writes the event to binlog] + +In case of binlog_with_tables_info = TRUE, write additional members +(query_len, tables_info_len, and tables_info) to binary log. Also +write corresponding whole event length to the common-header. + +<To be continued> -2. Where to get tables info from? +4. Where to get tables info from? ********************************* -2.1. Case study: CREATE TABLE -****************************** +4.1. Case study: CREATE TABLE +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] @@ -129,4 +218,4 @@ } } -To be continued +<To be continued> -=-=(Alexi - Wed, 04 Nov 2009, 10:21)=-=- Low Level Design modified. --- /tmp/wklog.40.old.6734 2009-11-04 10:21:20.000000000 +0200 +++ /tmp/wklog.40.new.6734 2009-11-04 10:21:20.000000000 +0200 @@ -21,9 +21,9 @@ slave_proxy_id 4 exec_time 4 db_len 1 -+ query_len 2 (see Note 1) error_code 2 status_vars_len 2 ++ query_len 2 (see Note 1) + tables_info_len 2 (see Note 2) --------------------------------- BODY: -=-=(Alexi - Tue, 03 Nov 2009, 11:19)=-=- Low Level Design modified. --- /tmp/wklog.40.old.7187 2009-11-03 11:19:22.000000000 +0200 +++ /tmp/wklog.40.new.7187 2009-11-03 11:19:22.000000000 +0200 @@ -1 +1,132 @@ +OPTION: 2.5 Extend Query Events With Tables Info +================================================ +1. Query_log_event Binary Format +******************************** +Changes to be done: + + Query_log_event binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + COMMON HEADER: + timestamp 4 + type 1 + server_id 4 + total_size 4 + master_position 4 + flags 2 + --------------------------------- + POST HEADER: + slave_proxy_id 4 + exec_time 4 + db_len 1 ++ query_len 2 (see Note 1) + error_code 2 + status_vars_len 2 ++ tables_info_len 2 (see Note 2) + --------------------------------- + BODY: + status_vars status_vars_len +- db db_len + 1 ++ db db_len (see Note 3) + query query_len ++ tables_info + + tables_info binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + db_len 1 (see Note 4) + db db_len + table_name_len 1 + table_name table_name_len + ... + db_len 1 + db db_len + table_name_len 1 + table_name table_name_len + +NOTES +1. Currently Query_log_event format doesn't include 'query_len' because + it considers the query to extent to the end of the event. +2. If tables_info is not included in the event (--binlog-with-tables-info + option), tables_info_len = 0. +3. The trailing zero is redundant since the length is already known. +4. In case of db = current db, db_len = 0 and db = empty, because + current db is already included in the current event format. + +2. Where to get tables info from? +********************************* + +2.1. Case study: CREATE TABLE +****************************** + +*** CREATE TABLE table [SELECT ...] + + bool mysql_create_table_no_lock( + THD *thd, + const char *db, + const char *table_name, ...) + { + ... + // ------------------------------------- + // WL40: To be included in tables_info: + // * db, table_name + // * thd->lex->query_tables (tables refered to in + // the select-part; empty if no select-part) + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + +*** CREATE TABLE table LIKE src-table + + bool mysql_create_like_table( + ... + TABLE_LIST *table, + TABLE_LIST *src_table, + ...) + { + ... + if (thd->current_stmt_binlog_row_based) + { // RBR: In this case we don't replicate temp tables + if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) + { + if (src_table->table->s->tmp_table) + { // CREATE normal-table LIKE temp-table: + + // Generate new query without LIKE-part + store_create_info(thd, table, &query, create_info, FALSE); + + // ------------------------------------- + // WL40: To include to tables_info: + // * table (src_table is not included) + // ------------------------------------- + write_bin_log(thd, TRUE, query.ptr(), query.length()); + } + else + { // CREATE normal-table LIKE normal-table + + // ------------------------------------- + // WL40: To include to log_tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + // CREATE temp-table LIKE ... + // This case is not replicated + } + else + { // SBR: + // ------------------------------------- + // WL40: To include to tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + +To be continued -=-=(Alexi - Mon, 02 Nov 2009, 11:34)=-=- Worked 2 hours on option 2.5 Worked 2 hours and estimate 48 hours remain (original estimate increased by 50 hours). ------------------------------------------------------------ -=-=(View All Progress Notes, 16 total)=-=- http://askmonty.org/worklog/index.pl?tid=40&nolimit=1 DESCRIPTION: Replication slave can be set to filter updates to certain tables with --replicate-[wild-]{do,ignore}-table options. This task is about adding similar functionality to mysqlbinlog. HIGH-LEVEL SPECIFICATION: 1. Context ---------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has these replication slave options: --replicate-do-table=db.tbl --replicate-ignore-table=db.tbl --replicate-wild-do-table=pattern.pattern --replicate-wild-ignore-table=pattern.pattern They affect both RBR and SBR events. SBR events are checked after the statement has been parsed, the server iterates over list of used tables and checks them againist --replicate instructions. What is interesting is that this scheme still allows to update the ignored table through a VIEW. 2. Table filtering in mysqlbinlog --------------------------------- Per-table filtering of RBR events is easy (as it is relatively easy to extract the name of the table that the event applies to). Per-table filtering of SBR events is hard, as generally it is not apparent which tables the statement refers to. This opens possible options: 2.1 Put the parser into mysqlbinlog ----------------------------------- Once we have a full parser in mysqlbinlog, we'll be able to check which tables are used by a statement, and will allow to show behaviour identical to those that one obtains when using --replicate-* slave options. (It is not clear how much effort is needed to put the parser into mysqlbinlog. Any guesses?) 2.2 Use dumb regexp match ------------------------- Use a really dumb approach. A query is considered to be modifying table X if it matches an expression CREATE TABLE $tablename DROP $tablename UPDATE ...$tablename ... SET // here '...' can't contain the word 'SET' DELETE ...$tablename ... WHERE // same as above ALTER TABLE $tablename .. etc (go get from the grammar) .. The advantage over doing the same in awk is that mysqlbinlog will also process RBR statements, and together with that will provide a working solution for those who are careful with their table names not mixing with string constants and such. (TODO: string constants are of particular concern as they come from [potentially hostile] users, unlike e.g. table aliases which come from [not hostile] developers. Remove also all string constants before attempting to do match?) 2.3 Have the master put annotations ----------------------------------- We could add a master option so that it injects into query a mark that tells which tables the query will affect, e.g. for the query UPDATE t1 LEFT JOIN db3.t2 ON ... WHERE ... the binlog will have /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... and further processing in mysqlbinlog will be trivial. 2.4 Implement server functionality to ignore certain tables ----------------------------------------------------------- We could add a general facility in the server to ignore certain tables: SET SESSION ignored_tables = "db1.t1,db2.t2"; This would work similar to --replicate-ignore-table, but in a general way not restricted to the slave SQL thread. It would then be trivial for mysqlbinlog to add such statements at the start of the output, or probably the user could just do it manually with no need for additional options for mysqlbinlog. It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. 2.5 Extend Query Events With Tables Info ---------------------------------------- We could extend query events structure with a tables info - a list of tables which the query refers to: <current query event structure> tables_info_len dbase_len dbase table_len table ... dbase_len dbase table_len table Note. In case of <dbase> = current data base, we can set dbase_len = 0 and dbase = empty because current query event structure already includes current data base name. Note. Possibly it is reasonable also to add a --binlog-with-tables-info option which defines whether tables info must be included to the query events. LOW-LEVEL DESIGN: OPTION: 2.5 Extend Query Events With Tables Info ================================================ 1. Adding --binlog-with-tables-info option ****************************************** GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events are to be written in usual format (without any changes). 2. Query event extended binary format ************************************* When --binlog-with-tables-info is set, Query events are writen to binary log in the following (extended) format. Query_log_event binary format --------------------------------- Name Size (bytes) --------------------------------- COMMON HEADER: timestamp 4 type 1 server_id 4 total_size 4 master_position 4 flags 2 --------------------------------- POST HEADER: slave_proxy_id 4 exec_time 4 db_len 1 error_code 2 status_vars_len 2 + query_len 2 (see Note 1) + tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 + db db_len (see Note 2) query query_len + tables_info tables_info binary format --------------------------------- Name Size (bytes) --------------------------------- db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len ... db_len 1 db db_len table_name_len 1 table_name table_name_len NOTES 1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. 3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 additional bytes. #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) + #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) ... #define Q_STATUS_VARS_LEN_OFFSET 11 + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 *********************************************************************** HELP NEEDED *********************************************************************** The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: log_event.h ~~~~~~~~~~~ #define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ LOG_EVENT_HEADER_LEN + /* write_header */ \ QUERY_HEADER_LEN + /* write_data */ \ EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ NAME_LEN + 1) which is used only for setting thd->variables.max_allowed_packet mysql->net.max_packet_size Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply (without making any other changes) be substituted in this definition by QUERY_HEADER_LEN_EXT. Below I list all places where MAX_LOG_EVENT_HEADER is used: slave.cc ~~~~~~~~ static int init_slave_thread(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all slave threads, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ ... } pthread_handler_t handle_slave_io(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O thread, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; ... } sql_repl.cc ~~~~~~~~~~~ void mysql_binlog_send(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become this larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } bool mysql_show_binlog_events(...) { ... /* to account binlog event header size */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } 3. Changes in log events ************************ 3.1. Format description event ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Changes needed here concern setting post-header length for Query events. This setting is done in Format description event constructor which creates the event for writing to binary log: if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; This change is to be done only for case binlog_ver = 4. NOTE. The refered above constructor is allowed to be invoked in a client context for creating "artificial" Format description events in case of MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems (because of 'binlog_with_tables_info') and taking into account the "MySQL < 5.0" restriction, we have to #ifdef out the above code in following manner: switch (binlog_ver) { + #ifndef MYSQL_CLIENT case 4: /* MySQL 5.0 and higher */ ... break; + #endif case 1: case 3: ... } 3.2. Query event ~~~~~~~~~~~~~~~~ Changes needed here include adding tables_info and tables_info_len members (member for query length already exists) and modifying the following function-members: Query_log_event(buf) constructor -------------------------------- [Parses binary format written to the 'buf'] Getting post-header length from the Format description event (passed to the constructor as an argument), define whether buf contains an extended or usual Query event and parse the buf contents accordingly. NOTE. Defining Query event format here should be done with taking into account that this constructor can be called within a Query-derived event with the event_type argument != QUERY_EVENT. Query_log_event(thd) constructor -------------------------------- [Creates the event for binlogging] In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). write() function ---------------- [Writes the event to binlog] In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. <To be continued> 4. Where to get tables info from? ********************************* 4.1. Case study: CREATE TABLE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] bool mysql_create_table_no_lock( THD *thd, const char *db, const char *table_name, ...) { ... // ------------------------------------- // WL40: To be included in tables_info: // * db, table_name // * thd->lex->query_tables (tables refered to in // the select-part; empty if no select-part) // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } *** CREATE TABLE table LIKE src-table bool mysql_create_like_table( ... TABLE_LIST *table, TABLE_LIST *src_table, ...) { ... if (thd->current_stmt_binlog_row_based) { // RBR: In this case we don't replicate temp tables if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) { if (src_table->table->s->tmp_table) { // CREATE normal-table LIKE temp-table: // Generate new query without LIKE-part store_create_info(thd, table, &query, create_info, FALSE); // ------------------------------------- // WL40: To include to tables_info: // * table (src_table is not included) // ------------------------------------- write_bin_log(thd, TRUE, query.ptr(), query.length()); } else { // CREATE normal-table LIKE normal-table // ------------------------------------- // WL40: To include to log_tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } // CREATE temp-table LIKE ... // This case is not replicated } else { // SBR: // ------------------------------------- // WL40: To include to tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } <To be continued> ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Add a mysqlbinlog option to filter updates to certain tables (40)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter updates to certain tables CREATION DATE..: Mon, 10 Aug 2009, 13:25 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-Sprint TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 32 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 48 PROGRESS NOTES: -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Status updated. --- /tmp/wklog.40.old.5760 2009-11-25 11:41:09.000000000 +0200 +++ /tmp/wklog.40.new.5760 2009-11-25 11:41:09.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Category updated. --- /tmp/wklog.40.old.5737 2009-11-25 11:41:03.000000000 +0200 +++ /tmp/wklog.40.new.5737 2009-11-25 11:41:03.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Bothorsen - Tue, 17 Nov 2009, 17:20)=-=- Alex is closer to a working patch now. Worked 14 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Bothorsen - Thu, 12 Nov 2009, 13:13)=-=- Work hours by Alexi and Bo + estimated time for the task. Worked 16 hours and estimate 46 hours remain (original estimate increased by 14 hours). -=-=(Alexi - Sun, 08 Nov 2009, 15:18)=-=- Low Level Design modified. --- /tmp/wklog.40.old.15787 2009-11-08 15:18:11.000000000 +0200 +++ /tmp/wklog.40.new.15787 2009-11-08 15:18:11.000000000 +0200 @@ -62,7 +62,7 @@ it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. -3. db_len = 0 means that this is the current db. +3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 @@ -75,6 +75,77 @@ + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +*********************************************************************** +HELP NEEDED +*********************************************************************** +The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: + +log_event.h +~~~~~~~~~~~ +#define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ + LOG_EVENT_HEADER_LEN + /* write_header */ \ + QUERY_HEADER_LEN + /* write_data */ \ + EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ + MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ + NAME_LEN + 1) + +which is used only for setting + + thd->variables.max_allowed_packet + mysql->net.max_packet_size + +Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply +(without making any other changes) be substituted in this definition by +QUERY_HEADER_LEN_EXT. + +Below I list all places where MAX_LOG_EVENT_HEADER is used: + +slave.cc +~~~~~~~~ +static int init_slave_thread(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all + slave threads, since a replication event can become this much larger + than the corresponding packet (query) sent from client to master. + */ + thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ + ... +} +pthread_handler_t handle_slave_io(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O + thread, since a replication event can become this much larger than + the corresponding packet (query) sent from client to master. + */ + mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; + ... +} + +sql_repl.cc +~~~~~~~~~~~ +void mysql_binlog_send(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become + this larger than the corresponding packet (query) sent + from client to master. + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} +bool mysql_show_binlog_events(...) +{ ... + /* + to account binlog event header size + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} + 3. Changes in log events ************************ @@ -84,7 +155,7 @@ This setting is done in Format description event constructor which creates the event for writing to binary log: - if (binlog_with_tables_info) + if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; @@ -99,12 +170,12 @@ following manner: switch (binlog_ver) { - case 4: /* MySQL 5.0 and higher */ + #ifndef MYSQL_CLIENT + case 4: /* MySQL 5.0 and higher */ ... -+ #else -+ <error> + break; + #endif + case 1: case 3: ... @@ -132,7 +203,7 @@ -------------------------------- [Creates the event for binlogging] -In case of binlog_with_tables_info = TRUE, set additionally query_len, +In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). @@ -140,7 +211,7 @@ ---------------- [Writes the event to binlog] -In case of binlog_with_tables_info = TRUE, write additional members +In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. -=-=(Alexi - Sun, 08 Nov 2009, 10:40)=-=- Low Level Design modified. --- /tmp/wklog.40.old.5055 2009-11-08 08:40:02.000000000 +0000 +++ /tmp/wklog.40.new.5055 2009-11-08 08:40:02.000000000 +0000 @@ -3,6 +3,7 @@ 1. Adding --binlog-with-tables-info option ****************************************** +GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events -=-=(Alexi - Thu, 05 Nov 2009, 12:37)=-=- Low Level Design modified. --- /tmp/wklog.40.old.11441 2009-11-05 12:37:16.000000000 +0200 +++ /tmp/wklog.40.new.11441 2009-11-05 12:37:16.000000000 +0200 @@ -1,9 +1,18 @@ OPTION: 2.5 Extend Query Events With Tables Info ================================================ -1. Query_log_event Binary Format -******************************** -Changes to be done: +1. Adding --binlog-with-tables-info option +****************************************** + +When set, Query events are to be written in the extended binary +format which contains tables_info. When not set, Query events +are to be written in usual format (without any changes). + +2. Query event extended binary format +************************************* + +When --binlog-with-tables-info is set, Query events are writen +to binary log in the following (extended) format. Query_log_event binary format --------------------------------- @@ -24,12 +33,12 @@ error_code 2 status_vars_len 2 + query_len 2 (see Note 1) -+ tables_info_len 2 (see Note 2) ++ tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 -+ db db_len (see Note 3) ++ db db_len (see Note 2) query query_len + tables_info @@ -37,7 +46,7 @@ --------------------------------- Name Size (bytes) --------------------------------- - db_len 1 (see Note 4) + db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len @@ -48,19 +57,99 @@ table_name table_name_len NOTES -1. Currently Query_log_event format doesn't include 'query_len' because +1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. -2. If tables_info is not included in the event (--binlog-with-tables-info - option), tables_info_len = 0. -3. The trailing zero is redundant since the length is already known. -4. In case of db = current db, db_len = 0 and db = empty, because - current db is already included in the current event format. +2. For 'db' (current db) the trailing zero is redundant since the length + is already known. +3. db_len = 0 means that this is the current db. + +When reading Query events from binary log, we can recognize its format +by its post-header length: in extended case the post-header includes 4 +additional bytes. + + #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) ++ #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) + ... + #define Q_STATUS_VARS_LEN_OFFSET 11 ++ #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 ++ #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +3. Changes in log events +************************ + +3.1. Format description event +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Changes needed here concern setting post-header length for Query events. +This setting is done in Format description event constructor which creates +the event for writing to binary log: + + if (binlog_with_tables_info) + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; + else + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; + +This change is to be done only for case binlog_ver = 4. + +NOTE. The refered above constructor is allowed to be invoked in a client +context for creating "artificial" Format description events in case of +MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems +(because of 'binlog_with_tables_info') and taking into account the +"MySQL < 5.0" restriction, we have to #ifdef out the above code in +following manner: + + switch (binlog_ver) { + case 4: /* MySQL 5.0 and higher */ ++ #ifndef MYSQL_CLIENT + ... ++ #else ++ <error> ++ #endif + case 1: + case 3: + ... + } + +3.2. Query event +~~~~~~~~~~~~~~~~ +Changes needed here include adding tables_info and tables_info_len +members (member for query length already exists) and modifying the +following function-members: + +Query_log_event(buf) constructor +-------------------------------- +[Parses binary format written to the 'buf'] + +Getting post-header length from the Format description event (passed +to the constructor as an argument), define whether buf contains an +extended or usual Query event and parse the buf contents accordingly. + +NOTE. Defining Query event format here should be done with taking into +account that this constructor can be called within a Query-derived +event with the event_type argument != QUERY_EVENT. + +Query_log_event(thd) constructor +-------------------------------- +[Creates the event for binlogging] + +In case of binlog_with_tables_info = TRUE, set additionally query_len, +tables_info_len, and tables_info members (the constructor is to have +an additional 'tables_info' argument). + +write() function +---------------- +[Writes the event to binlog] + +In case of binlog_with_tables_info = TRUE, write additional members +(query_len, tables_info_len, and tables_info) to binary log. Also +write corresponding whole event length to the common-header. + +<To be continued> -2. Where to get tables info from? +4. Where to get tables info from? ********************************* -2.1. Case study: CREATE TABLE -****************************** +4.1. Case study: CREATE TABLE +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] @@ -129,4 +218,4 @@ } } -To be continued +<To be continued> -=-=(Alexi - Wed, 04 Nov 2009, 10:21)=-=- Low Level Design modified. --- /tmp/wklog.40.old.6734 2009-11-04 10:21:20.000000000 +0200 +++ /tmp/wklog.40.new.6734 2009-11-04 10:21:20.000000000 +0200 @@ -21,9 +21,9 @@ slave_proxy_id 4 exec_time 4 db_len 1 -+ query_len 2 (see Note 1) error_code 2 status_vars_len 2 ++ query_len 2 (see Note 1) + tables_info_len 2 (see Note 2) --------------------------------- BODY: -=-=(Alexi - Tue, 03 Nov 2009, 11:19)=-=- Low Level Design modified. --- /tmp/wklog.40.old.7187 2009-11-03 11:19:22.000000000 +0200 +++ /tmp/wklog.40.new.7187 2009-11-03 11:19:22.000000000 +0200 @@ -1 +1,132 @@ +OPTION: 2.5 Extend Query Events With Tables Info +================================================ +1. Query_log_event Binary Format +******************************** +Changes to be done: + + Query_log_event binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + COMMON HEADER: + timestamp 4 + type 1 + server_id 4 + total_size 4 + master_position 4 + flags 2 + --------------------------------- + POST HEADER: + slave_proxy_id 4 + exec_time 4 + db_len 1 ++ query_len 2 (see Note 1) + error_code 2 + status_vars_len 2 ++ tables_info_len 2 (see Note 2) + --------------------------------- + BODY: + status_vars status_vars_len +- db db_len + 1 ++ db db_len (see Note 3) + query query_len ++ tables_info + + tables_info binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + db_len 1 (see Note 4) + db db_len + table_name_len 1 + table_name table_name_len + ... + db_len 1 + db db_len + table_name_len 1 + table_name table_name_len + +NOTES +1. Currently Query_log_event format doesn't include 'query_len' because + it considers the query to extent to the end of the event. +2. If tables_info is not included in the event (--binlog-with-tables-info + option), tables_info_len = 0. +3. The trailing zero is redundant since the length is already known. +4. In case of db = current db, db_len = 0 and db = empty, because + current db is already included in the current event format. + +2. Where to get tables info from? +********************************* + +2.1. Case study: CREATE TABLE +****************************** + +*** CREATE TABLE table [SELECT ...] + + bool mysql_create_table_no_lock( + THD *thd, + const char *db, + const char *table_name, ...) + { + ... + // ------------------------------------- + // WL40: To be included in tables_info: + // * db, table_name + // * thd->lex->query_tables (tables refered to in + // the select-part; empty if no select-part) + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + +*** CREATE TABLE table LIKE src-table + + bool mysql_create_like_table( + ... + TABLE_LIST *table, + TABLE_LIST *src_table, + ...) + { + ... + if (thd->current_stmt_binlog_row_based) + { // RBR: In this case we don't replicate temp tables + if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) + { + if (src_table->table->s->tmp_table) + { // CREATE normal-table LIKE temp-table: + + // Generate new query without LIKE-part + store_create_info(thd, table, &query, create_info, FALSE); + + // ------------------------------------- + // WL40: To include to tables_info: + // * table (src_table is not included) + // ------------------------------------- + write_bin_log(thd, TRUE, query.ptr(), query.length()); + } + else + { // CREATE normal-table LIKE normal-table + + // ------------------------------------- + // WL40: To include to log_tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + // CREATE temp-table LIKE ... + // This case is not replicated + } + else + { // SBR: + // ------------------------------------- + // WL40: To include to tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + +To be continued -=-=(Alexi - Mon, 02 Nov 2009, 11:34)=-=- Worked 2 hours on option 2.5 Worked 2 hours and estimate 48 hours remain (original estimate increased by 50 hours). ------------------------------------------------------------ -=-=(View All Progress Notes, 16 total)=-=- http://askmonty.org/worklog/index.pl?tid=40&nolimit=1 DESCRIPTION: Replication slave can be set to filter updates to certain tables with --replicate-[wild-]{do,ignore}-table options. This task is about adding similar functionality to mysqlbinlog. HIGH-LEVEL SPECIFICATION: 1. Context ---------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has these replication slave options: --replicate-do-table=db.tbl --replicate-ignore-table=db.tbl --replicate-wild-do-table=pattern.pattern --replicate-wild-ignore-table=pattern.pattern They affect both RBR and SBR events. SBR events are checked after the statement has been parsed, the server iterates over list of used tables and checks them againist --replicate instructions. What is interesting is that this scheme still allows to update the ignored table through a VIEW. 2. Table filtering in mysqlbinlog --------------------------------- Per-table filtering of RBR events is easy (as it is relatively easy to extract the name of the table that the event applies to). Per-table filtering of SBR events is hard, as generally it is not apparent which tables the statement refers to. This opens possible options: 2.1 Put the parser into mysqlbinlog ----------------------------------- Once we have a full parser in mysqlbinlog, we'll be able to check which tables are used by a statement, and will allow to show behaviour identical to those that one obtains when using --replicate-* slave options. (It is not clear how much effort is needed to put the parser into mysqlbinlog. Any guesses?) 2.2 Use dumb regexp match ------------------------- Use a really dumb approach. A query is considered to be modifying table X if it matches an expression CREATE TABLE $tablename DROP $tablename UPDATE ...$tablename ... SET // here '...' can't contain the word 'SET' DELETE ...$tablename ... WHERE // same as above ALTER TABLE $tablename .. etc (go get from the grammar) .. The advantage over doing the same in awk is that mysqlbinlog will also process RBR statements, and together with that will provide a working solution for those who are careful with their table names not mixing with string constants and such. (TODO: string constants are of particular concern as they come from [potentially hostile] users, unlike e.g. table aliases which come from [not hostile] developers. Remove also all string constants before attempting to do match?) 2.3 Have the master put annotations ----------------------------------- We could add a master option so that it injects into query a mark that tells which tables the query will affect, e.g. for the query UPDATE t1 LEFT JOIN db3.t2 ON ... WHERE ... the binlog will have /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... and further processing in mysqlbinlog will be trivial. 2.4 Implement server functionality to ignore certain tables ----------------------------------------------------------- We could add a general facility in the server to ignore certain tables: SET SESSION ignored_tables = "db1.t1,db2.t2"; This would work similar to --replicate-ignore-table, but in a general way not restricted to the slave SQL thread. It would then be trivial for mysqlbinlog to add such statements at the start of the output, or probably the user could just do it manually with no need for additional options for mysqlbinlog. It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. 2.5 Extend Query Events With Tables Info ---------------------------------------- We could extend query events structure with a tables info - a list of tables which the query refers to: <current query event structure> tables_info_len dbase_len dbase table_len table ... dbase_len dbase table_len table Note. In case of <dbase> = current data base, we can set dbase_len = 0 and dbase = empty because current query event structure already includes current data base name. Note. Possibly it is reasonable also to add a --binlog-with-tables-info option which defines whether tables info must be included to the query events. LOW-LEVEL DESIGN: OPTION: 2.5 Extend Query Events With Tables Info ================================================ 1. Adding --binlog-with-tables-info option ****************************************** GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events are to be written in usual format (without any changes). 2. Query event extended binary format ************************************* When --binlog-with-tables-info is set, Query events are writen to binary log in the following (extended) format. Query_log_event binary format --------------------------------- Name Size (bytes) --------------------------------- COMMON HEADER: timestamp 4 type 1 server_id 4 total_size 4 master_position 4 flags 2 --------------------------------- POST HEADER: slave_proxy_id 4 exec_time 4 db_len 1 error_code 2 status_vars_len 2 + query_len 2 (see Note 1) + tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 + db db_len (see Note 2) query query_len + tables_info tables_info binary format --------------------------------- Name Size (bytes) --------------------------------- db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len ... db_len 1 db db_len table_name_len 1 table_name table_name_len NOTES 1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. 3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 additional bytes. #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) + #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) ... #define Q_STATUS_VARS_LEN_OFFSET 11 + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 *********************************************************************** HELP NEEDED *********************************************************************** The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: log_event.h ~~~~~~~~~~~ #define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ LOG_EVENT_HEADER_LEN + /* write_header */ \ QUERY_HEADER_LEN + /* write_data */ \ EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ NAME_LEN + 1) which is used only for setting thd->variables.max_allowed_packet mysql->net.max_packet_size Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply (without making any other changes) be substituted in this definition by QUERY_HEADER_LEN_EXT. Below I list all places where MAX_LOG_EVENT_HEADER is used: slave.cc ~~~~~~~~ static int init_slave_thread(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all slave threads, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ ... } pthread_handler_t handle_slave_io(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O thread, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; ... } sql_repl.cc ~~~~~~~~~~~ void mysql_binlog_send(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become this larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } bool mysql_show_binlog_events(...) { ... /* to account binlog event header size */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } 3. Changes in log events ************************ 3.1. Format description event ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Changes needed here concern setting post-header length for Query events. This setting is done in Format description event constructor which creates the event for writing to binary log: if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; This change is to be done only for case binlog_ver = 4. NOTE. The refered above constructor is allowed to be invoked in a client context for creating "artificial" Format description events in case of MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems (because of 'binlog_with_tables_info') and taking into account the "MySQL < 5.0" restriction, we have to #ifdef out the above code in following manner: switch (binlog_ver) { + #ifndef MYSQL_CLIENT case 4: /* MySQL 5.0 and higher */ ... break; + #endif case 1: case 3: ... } 3.2. Query event ~~~~~~~~~~~~~~~~ Changes needed here include adding tables_info and tables_info_len members (member for query length already exists) and modifying the following function-members: Query_log_event(buf) constructor -------------------------------- [Parses binary format written to the 'buf'] Getting post-header length from the Format description event (passed to the constructor as an argument), define whether buf contains an extended or usual Query event and parse the buf contents accordingly. NOTE. Defining Query event format here should be done with taking into account that this constructor can be called within a Query-derived event with the event_type argument != QUERY_EVENT. Query_log_event(thd) constructor -------------------------------- [Creates the event for binlogging] In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). write() function ---------------- [Writes the event to binlog] In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. <To be continued> 4. Where to get tables info from? ********************************* 4.1. Case study: CREATE TABLE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] bool mysql_create_table_no_lock( THD *thd, const char *db, const char *table_name, ...) { ... // ------------------------------------- // WL40: To be included in tables_info: // * db, table_name // * thd->lex->query_tables (tables refered to in // the select-part; empty if no select-part) // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } *** CREATE TABLE table LIKE src-table bool mysql_create_like_table( ... TABLE_LIST *table, TABLE_LIST *src_table, ...) { ... if (thd->current_stmt_binlog_row_based) { // RBR: In this case we don't replicate temp tables if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) { if (src_table->table->s->tmp_table) { // CREATE normal-table LIKE temp-table: // Generate new query without LIKE-part store_create_info(thd, table, &query, create_info, FALSE); // ------------------------------------- // WL40: To include to tables_info: // * table (src_table is not included) // ------------------------------------- write_bin_log(thd, TRUE, query.ptr(), query.length()); } else { // CREATE normal-table LIKE normal-table // ------------------------------------- // WL40: To include to log_tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } // CREATE temp-table LIKE ... // This case is not replicated } else { // SBR: // ------------------------------------- // WL40: To include to tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } <To be continued> ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Add a mysqlbinlog option to filter updates to certain tables (40)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter updates to certain tables CREATION DATE..: Mon, 10 Aug 2009, 13:25 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-Sprint TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 32 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 48 PROGRESS NOTES: -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Category updated. --- /tmp/wklog.40.old.5737 2009-11-25 11:41:03.000000000 +0200 +++ /tmp/wklog.40.new.5737 2009-11-25 11:41:03.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Bothorsen - Tue, 17 Nov 2009, 17:20)=-=- Alex is closer to a working patch now. Worked 14 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Bothorsen - Thu, 12 Nov 2009, 13:13)=-=- Work hours by Alexi and Bo + estimated time for the task. Worked 16 hours and estimate 46 hours remain (original estimate increased by 14 hours). -=-=(Alexi - Sun, 08 Nov 2009, 15:18)=-=- Low Level Design modified. --- /tmp/wklog.40.old.15787 2009-11-08 15:18:11.000000000 +0200 +++ /tmp/wklog.40.new.15787 2009-11-08 15:18:11.000000000 +0200 @@ -62,7 +62,7 @@ it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. -3. db_len = 0 means that this is the current db. +3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 @@ -75,6 +75,77 @@ + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +*********************************************************************** +HELP NEEDED +*********************************************************************** +The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: + +log_event.h +~~~~~~~~~~~ +#define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ + LOG_EVENT_HEADER_LEN + /* write_header */ \ + QUERY_HEADER_LEN + /* write_data */ \ + EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ + MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ + NAME_LEN + 1) + +which is used only for setting + + thd->variables.max_allowed_packet + mysql->net.max_packet_size + +Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply +(without making any other changes) be substituted in this definition by +QUERY_HEADER_LEN_EXT. + +Below I list all places where MAX_LOG_EVENT_HEADER is used: + +slave.cc +~~~~~~~~ +static int init_slave_thread(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all + slave threads, since a replication event can become this much larger + than the corresponding packet (query) sent from client to master. + */ + thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ + ... +} +pthread_handler_t handle_slave_io(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O + thread, since a replication event can become this much larger than + the corresponding packet (query) sent from client to master. + */ + mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; + ... +} + +sql_repl.cc +~~~~~~~~~~~ +void mysql_binlog_send(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become + this larger than the corresponding packet (query) sent + from client to master. + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} +bool mysql_show_binlog_events(...) +{ ... + /* + to account binlog event header size + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} + 3. Changes in log events ************************ @@ -84,7 +155,7 @@ This setting is done in Format description event constructor which creates the event for writing to binary log: - if (binlog_with_tables_info) + if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; @@ -99,12 +170,12 @@ following manner: switch (binlog_ver) { - case 4: /* MySQL 5.0 and higher */ + #ifndef MYSQL_CLIENT + case 4: /* MySQL 5.0 and higher */ ... -+ #else -+ <error> + break; + #endif + case 1: case 3: ... @@ -132,7 +203,7 @@ -------------------------------- [Creates the event for binlogging] -In case of binlog_with_tables_info = TRUE, set additionally query_len, +In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). @@ -140,7 +211,7 @@ ---------------- [Writes the event to binlog] -In case of binlog_with_tables_info = TRUE, write additional members +In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. -=-=(Alexi - Sun, 08 Nov 2009, 10:40)=-=- Low Level Design modified. --- /tmp/wklog.40.old.5055 2009-11-08 08:40:02.000000000 +0000 +++ /tmp/wklog.40.new.5055 2009-11-08 08:40:02.000000000 +0000 @@ -3,6 +3,7 @@ 1. Adding --binlog-with-tables-info option ****************************************** +GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events -=-=(Alexi - Thu, 05 Nov 2009, 12:37)=-=- Low Level Design modified. --- /tmp/wklog.40.old.11441 2009-11-05 12:37:16.000000000 +0200 +++ /tmp/wklog.40.new.11441 2009-11-05 12:37:16.000000000 +0200 @@ -1,9 +1,18 @@ OPTION: 2.5 Extend Query Events With Tables Info ================================================ -1. Query_log_event Binary Format -******************************** -Changes to be done: +1. Adding --binlog-with-tables-info option +****************************************** + +When set, Query events are to be written in the extended binary +format which contains tables_info. When not set, Query events +are to be written in usual format (without any changes). + +2. Query event extended binary format +************************************* + +When --binlog-with-tables-info is set, Query events are writen +to binary log in the following (extended) format. Query_log_event binary format --------------------------------- @@ -24,12 +33,12 @@ error_code 2 status_vars_len 2 + query_len 2 (see Note 1) -+ tables_info_len 2 (see Note 2) ++ tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 -+ db db_len (see Note 3) ++ db db_len (see Note 2) query query_len + tables_info @@ -37,7 +46,7 @@ --------------------------------- Name Size (bytes) --------------------------------- - db_len 1 (see Note 4) + db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len @@ -48,19 +57,99 @@ table_name table_name_len NOTES -1. Currently Query_log_event format doesn't include 'query_len' because +1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. -2. If tables_info is not included in the event (--binlog-with-tables-info - option), tables_info_len = 0. -3. The trailing zero is redundant since the length is already known. -4. In case of db = current db, db_len = 0 and db = empty, because - current db is already included in the current event format. +2. For 'db' (current db) the trailing zero is redundant since the length + is already known. +3. db_len = 0 means that this is the current db. + +When reading Query events from binary log, we can recognize its format +by its post-header length: in extended case the post-header includes 4 +additional bytes. + + #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) ++ #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) + ... + #define Q_STATUS_VARS_LEN_OFFSET 11 ++ #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 ++ #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +3. Changes in log events +************************ + +3.1. Format description event +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Changes needed here concern setting post-header length for Query events. +This setting is done in Format description event constructor which creates +the event for writing to binary log: + + if (binlog_with_tables_info) + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; + else + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; + +This change is to be done only for case binlog_ver = 4. + +NOTE. The refered above constructor is allowed to be invoked in a client +context for creating "artificial" Format description events in case of +MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems +(because of 'binlog_with_tables_info') and taking into account the +"MySQL < 5.0" restriction, we have to #ifdef out the above code in +following manner: + + switch (binlog_ver) { + case 4: /* MySQL 5.0 and higher */ ++ #ifndef MYSQL_CLIENT + ... ++ #else ++ <error> ++ #endif + case 1: + case 3: + ... + } + +3.2. Query event +~~~~~~~~~~~~~~~~ +Changes needed here include adding tables_info and tables_info_len +members (member for query length already exists) and modifying the +following function-members: + +Query_log_event(buf) constructor +-------------------------------- +[Parses binary format written to the 'buf'] + +Getting post-header length from the Format description event (passed +to the constructor as an argument), define whether buf contains an +extended or usual Query event and parse the buf contents accordingly. + +NOTE. Defining Query event format here should be done with taking into +account that this constructor can be called within a Query-derived +event with the event_type argument != QUERY_EVENT. + +Query_log_event(thd) constructor +-------------------------------- +[Creates the event for binlogging] + +In case of binlog_with_tables_info = TRUE, set additionally query_len, +tables_info_len, and tables_info members (the constructor is to have +an additional 'tables_info' argument). + +write() function +---------------- +[Writes the event to binlog] + +In case of binlog_with_tables_info = TRUE, write additional members +(query_len, tables_info_len, and tables_info) to binary log. Also +write corresponding whole event length to the common-header. + +<To be continued> -2. Where to get tables info from? +4. Where to get tables info from? ********************************* -2.1. Case study: CREATE TABLE -****************************** +4.1. Case study: CREATE TABLE +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] @@ -129,4 +218,4 @@ } } -To be continued +<To be continued> -=-=(Alexi - Wed, 04 Nov 2009, 10:21)=-=- Low Level Design modified. --- /tmp/wklog.40.old.6734 2009-11-04 10:21:20.000000000 +0200 +++ /tmp/wklog.40.new.6734 2009-11-04 10:21:20.000000000 +0200 @@ -21,9 +21,9 @@ slave_proxy_id 4 exec_time 4 db_len 1 -+ query_len 2 (see Note 1) error_code 2 status_vars_len 2 ++ query_len 2 (see Note 1) + tables_info_len 2 (see Note 2) --------------------------------- BODY: -=-=(Alexi - Tue, 03 Nov 2009, 11:19)=-=- Low Level Design modified. --- /tmp/wklog.40.old.7187 2009-11-03 11:19:22.000000000 +0200 +++ /tmp/wklog.40.new.7187 2009-11-03 11:19:22.000000000 +0200 @@ -1 +1,132 @@ +OPTION: 2.5 Extend Query Events With Tables Info +================================================ +1. Query_log_event Binary Format +******************************** +Changes to be done: + + Query_log_event binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + COMMON HEADER: + timestamp 4 + type 1 + server_id 4 + total_size 4 + master_position 4 + flags 2 + --------------------------------- + POST HEADER: + slave_proxy_id 4 + exec_time 4 + db_len 1 ++ query_len 2 (see Note 1) + error_code 2 + status_vars_len 2 ++ tables_info_len 2 (see Note 2) + --------------------------------- + BODY: + status_vars status_vars_len +- db db_len + 1 ++ db db_len (see Note 3) + query query_len ++ tables_info + + tables_info binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + db_len 1 (see Note 4) + db db_len + table_name_len 1 + table_name table_name_len + ... + db_len 1 + db db_len + table_name_len 1 + table_name table_name_len + +NOTES +1. Currently Query_log_event format doesn't include 'query_len' because + it considers the query to extent to the end of the event. +2. If tables_info is not included in the event (--binlog-with-tables-info + option), tables_info_len = 0. +3. The trailing zero is redundant since the length is already known. +4. In case of db = current db, db_len = 0 and db = empty, because + current db is already included in the current event format. + +2. Where to get tables info from? +********************************* + +2.1. Case study: CREATE TABLE +****************************** + +*** CREATE TABLE table [SELECT ...] + + bool mysql_create_table_no_lock( + THD *thd, + const char *db, + const char *table_name, ...) + { + ... + // ------------------------------------- + // WL40: To be included in tables_info: + // * db, table_name + // * thd->lex->query_tables (tables refered to in + // the select-part; empty if no select-part) + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + +*** CREATE TABLE table LIKE src-table + + bool mysql_create_like_table( + ... + TABLE_LIST *table, + TABLE_LIST *src_table, + ...) + { + ... + if (thd->current_stmt_binlog_row_based) + { // RBR: In this case we don't replicate temp tables + if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) + { + if (src_table->table->s->tmp_table) + { // CREATE normal-table LIKE temp-table: + + // Generate new query without LIKE-part + store_create_info(thd, table, &query, create_info, FALSE); + + // ------------------------------------- + // WL40: To include to tables_info: + // * table (src_table is not included) + // ------------------------------------- + write_bin_log(thd, TRUE, query.ptr(), query.length()); + } + else + { // CREATE normal-table LIKE normal-table + + // ------------------------------------- + // WL40: To include to log_tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + // CREATE temp-table LIKE ... + // This case is not replicated + } + else + { // SBR: + // ------------------------------------- + // WL40: To include to tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + +To be continued -=-=(Alexi - Mon, 02 Nov 2009, 11:34)=-=- Worked 2 hours on option 2.5 Worked 2 hours and estimate 48 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Mon, 02 Nov 2009, 11:20)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.4848 2009-11-02 11:20:24.000000000 +0200 +++ /tmp/wklog.40.new.4848 2009-11-02 11:20:24.000000000 +0200 @@ -90,3 +90,25 @@ It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. +2.5 Extend Query Events With Tables Info +---------------------------------------- + +We could extend query events structure with a tables info - a list of tables +which the query refers to: + + <current query event structure> + tables_info_len + dbase_len dbase + table_len table + ... + dbase_len dbase + table_len table + +Note. In case of <dbase> = current data base, we can set dbase_len = 0 + and dbase = empty because current query event structure already + includes current data base name. + +Note. Possibly it is reasonable also to add a --binlog-with-tables-info + option which defines whether tables info must be included to the + query events. + ------------------------------------------------------------ -=-=(View All Progress Notes, 15 total)=-=- http://askmonty.org/worklog/index.pl?tid=40&nolimit=1 DESCRIPTION: Replication slave can be set to filter updates to certain tables with --replicate-[wild-]{do,ignore}-table options. This task is about adding similar functionality to mysqlbinlog. HIGH-LEVEL SPECIFICATION: 1. Context ---------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has these replication slave options: --replicate-do-table=db.tbl --replicate-ignore-table=db.tbl --replicate-wild-do-table=pattern.pattern --replicate-wild-ignore-table=pattern.pattern They affect both RBR and SBR events. SBR events are checked after the statement has been parsed, the server iterates over list of used tables and checks them againist --replicate instructions. What is interesting is that this scheme still allows to update the ignored table through a VIEW. 2. Table filtering in mysqlbinlog --------------------------------- Per-table filtering of RBR events is easy (as it is relatively easy to extract the name of the table that the event applies to). Per-table filtering of SBR events is hard, as generally it is not apparent which tables the statement refers to. This opens possible options: 2.1 Put the parser into mysqlbinlog ----------------------------------- Once we have a full parser in mysqlbinlog, we'll be able to check which tables are used by a statement, and will allow to show behaviour identical to those that one obtains when using --replicate-* slave options. (It is not clear how much effort is needed to put the parser into mysqlbinlog. Any guesses?) 2.2 Use dumb regexp match ------------------------- Use a really dumb approach. A query is considered to be modifying table X if it matches an expression CREATE TABLE $tablename DROP $tablename UPDATE ...$tablename ... SET // here '...' can't contain the word 'SET' DELETE ...$tablename ... WHERE // same as above ALTER TABLE $tablename .. etc (go get from the grammar) .. The advantage over doing the same in awk is that mysqlbinlog will also process RBR statements, and together with that will provide a working solution for those who are careful with their table names not mixing with string constants and such. (TODO: string constants are of particular concern as they come from [potentially hostile] users, unlike e.g. table aliases which come from [not hostile] developers. Remove also all string constants before attempting to do match?) 2.3 Have the master put annotations ----------------------------------- We could add a master option so that it injects into query a mark that tells which tables the query will affect, e.g. for the query UPDATE t1 LEFT JOIN db3.t2 ON ... WHERE ... the binlog will have /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... and further processing in mysqlbinlog will be trivial. 2.4 Implement server functionality to ignore certain tables ----------------------------------------------------------- We could add a general facility in the server to ignore certain tables: SET SESSION ignored_tables = "db1.t1,db2.t2"; This would work similar to --replicate-ignore-table, but in a general way not restricted to the slave SQL thread. It would then be trivial for mysqlbinlog to add such statements at the start of the output, or probably the user could just do it manually with no need for additional options for mysqlbinlog. It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. 2.5 Extend Query Events With Tables Info ---------------------------------------- We could extend query events structure with a tables info - a list of tables which the query refers to: <current query event structure> tables_info_len dbase_len dbase table_len table ... dbase_len dbase table_len table Note. In case of <dbase> = current data base, we can set dbase_len = 0 and dbase = empty because current query event structure already includes current data base name. Note. Possibly it is reasonable also to add a --binlog-with-tables-info option which defines whether tables info must be included to the query events. LOW-LEVEL DESIGN: OPTION: 2.5 Extend Query Events With Tables Info ================================================ 1. Adding --binlog-with-tables-info option ****************************************** GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events are to be written in usual format (without any changes). 2. Query event extended binary format ************************************* When --binlog-with-tables-info is set, Query events are writen to binary log in the following (extended) format. Query_log_event binary format --------------------------------- Name Size (bytes) --------------------------------- COMMON HEADER: timestamp 4 type 1 server_id 4 total_size 4 master_position 4 flags 2 --------------------------------- POST HEADER: slave_proxy_id 4 exec_time 4 db_len 1 error_code 2 status_vars_len 2 + query_len 2 (see Note 1) + tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 + db db_len (see Note 2) query query_len + tables_info tables_info binary format --------------------------------- Name Size (bytes) --------------------------------- db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len ... db_len 1 db db_len table_name_len 1 table_name table_name_len NOTES 1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. 3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 additional bytes. #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) + #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) ... #define Q_STATUS_VARS_LEN_OFFSET 11 + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 *********************************************************************** HELP NEEDED *********************************************************************** The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: log_event.h ~~~~~~~~~~~ #define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ LOG_EVENT_HEADER_LEN + /* write_header */ \ QUERY_HEADER_LEN + /* write_data */ \ EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ NAME_LEN + 1) which is used only for setting thd->variables.max_allowed_packet mysql->net.max_packet_size Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply (without making any other changes) be substituted in this definition by QUERY_HEADER_LEN_EXT. Below I list all places where MAX_LOG_EVENT_HEADER is used: slave.cc ~~~~~~~~ static int init_slave_thread(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all slave threads, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ ... } pthread_handler_t handle_slave_io(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O thread, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; ... } sql_repl.cc ~~~~~~~~~~~ void mysql_binlog_send(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become this larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } bool mysql_show_binlog_events(...) { ... /* to account binlog event header size */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } 3. Changes in log events ************************ 3.1. Format description event ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Changes needed here concern setting post-header length for Query events. This setting is done in Format description event constructor which creates the event for writing to binary log: if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; This change is to be done only for case binlog_ver = 4. NOTE. The refered above constructor is allowed to be invoked in a client context for creating "artificial" Format description events in case of MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems (because of 'binlog_with_tables_info') and taking into account the "MySQL < 5.0" restriction, we have to #ifdef out the above code in following manner: switch (binlog_ver) { + #ifndef MYSQL_CLIENT case 4: /* MySQL 5.0 and higher */ ... break; + #endif case 1: case 3: ... } 3.2. Query event ~~~~~~~~~~~~~~~~ Changes needed here include adding tables_info and tables_info_len members (member for query length already exists) and modifying the following function-members: Query_log_event(buf) constructor -------------------------------- [Parses binary format written to the 'buf'] Getting post-header length from the Format description event (passed to the constructor as an argument), define whether buf contains an extended or usual Query event and parse the buf contents accordingly. NOTE. Defining Query event format here should be done with taking into account that this constructor can be called within a Query-derived event with the event_type argument != QUERY_EVENT. Query_log_event(thd) constructor -------------------------------- [Creates the event for binlogging] In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). write() function ---------------- [Writes the event to binlog] In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. <To be continued> 4. Where to get tables info from? ********************************* 4.1. Case study: CREATE TABLE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] bool mysql_create_table_no_lock( THD *thd, const char *db, const char *table_name, ...) { ... // ------------------------------------- // WL40: To be included in tables_info: // * db, table_name // * thd->lex->query_tables (tables refered to in // the select-part; empty if no select-part) // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } *** CREATE TABLE table LIKE src-table bool mysql_create_like_table( ... TABLE_LIST *table, TABLE_LIST *src_table, ...) { ... if (thd->current_stmt_binlog_row_based) { // RBR: In this case we don't replicate temp tables if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) { if (src_table->table->s->tmp_table) { // CREATE normal-table LIKE temp-table: // Generate new query without LIKE-part store_create_info(thd, table, &query, create_info, FALSE); // ------------------------------------- // WL40: To include to tables_info: // * table (src_table is not included) // ------------------------------------- write_bin_log(thd, TRUE, query.ptr(), query.length()); } else { // CREATE normal-table LIKE normal-table // ------------------------------------- // WL40: To include to log_tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } // CREATE temp-table LIKE ... // This case is not replicated } else { // SBR: // ------------------------------------- // WL40: To include to tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } <To be continued> ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Add a mysqlbinlog option to filter updates to certain tables (40)
by worklog-noreply＠askmonty.org 25 Nov '09

25 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter updates to certain tables CREATION DATE..: Mon, 10 Aug 2009, 13:25 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-Sprint TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 32 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 48 PROGRESS NOTES: -=-=(Guest - Wed, 25 Nov 2009, 11:41)=-=- Category updated. --- /tmp/wklog.40.old.5737 2009-11-25 11:41:03.000000000 +0200 +++ /tmp/wklog.40.new.5737 2009-11-25 11:41:03.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Bothorsen - Tue, 17 Nov 2009, 17:20)=-=- Alex is closer to a working patch now. Worked 14 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Bothorsen - Thu, 12 Nov 2009, 13:13)=-=- Work hours by Alexi and Bo + estimated time for the task. Worked 16 hours and estimate 46 hours remain (original estimate increased by 14 hours). -=-=(Alexi - Sun, 08 Nov 2009, 15:18)=-=- Low Level Design modified. --- /tmp/wklog.40.old.15787 2009-11-08 15:18:11.000000000 +0200 +++ /tmp/wklog.40.new.15787 2009-11-08 15:18:11.000000000 +0200 @@ -62,7 +62,7 @@ it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. -3. db_len = 0 means that this is the current db. +3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 @@ -75,6 +75,77 @@ + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +*********************************************************************** +HELP NEEDED +*********************************************************************** +The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: + +log_event.h +~~~~~~~~~~~ +#define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ + LOG_EVENT_HEADER_LEN + /* write_header */ \ + QUERY_HEADER_LEN + /* write_data */ \ + EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ + MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ + NAME_LEN + 1) + +which is used only for setting + + thd->variables.max_allowed_packet + mysql->net.max_packet_size + +Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply +(without making any other changes) be substituted in this definition by +QUERY_HEADER_LEN_EXT. + +Below I list all places where MAX_LOG_EVENT_HEADER is used: + +slave.cc +~~~~~~~~ +static int init_slave_thread(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all + slave threads, since a replication event can become this much larger + than the corresponding packet (query) sent from client to master. + */ + thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ + ... +} +pthread_handler_t handle_slave_io(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O + thread, since a replication event can become this much larger than + the corresponding packet (query) sent from client to master. + */ + mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; + ... +} + +sql_repl.cc +~~~~~~~~~~~ +void mysql_binlog_send(...) +{ ... + /* + Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become + this larger than the corresponding packet (query) sent + from client to master. + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} +bool mysql_show_binlog_events(...) +{ ... + /* + to account binlog event header size + */ + thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; + ... +} + 3. Changes in log events ************************ @@ -84,7 +155,7 @@ This setting is done in Format description event constructor which creates the event for writing to binary log: - if (binlog_with_tables_info) + if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; @@ -99,12 +170,12 @@ following manner: switch (binlog_ver) { - case 4: /* MySQL 5.0 and higher */ + #ifndef MYSQL_CLIENT + case 4: /* MySQL 5.0 and higher */ ... -+ #else -+ <error> + break; + #endif + case 1: case 3: ... @@ -132,7 +203,7 @@ -------------------------------- [Creates the event for binlogging] -In case of binlog_with_tables_info = TRUE, set additionally query_len, +In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). @@ -140,7 +211,7 @@ ---------------- [Writes the event to binlog] -In case of binlog_with_tables_info = TRUE, write additional members +In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. -=-=(Alexi - Sun, 08 Nov 2009, 10:40)=-=- Low Level Design modified. --- /tmp/wklog.40.old.5055 2009-11-08 08:40:02.000000000 +0000 +++ /tmp/wklog.40.new.5055 2009-11-08 08:40:02.000000000 +0000 @@ -3,6 +3,7 @@ 1. Adding --binlog-with-tables-info option ****************************************** +GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events -=-=(Alexi - Thu, 05 Nov 2009, 12:37)=-=- Low Level Design modified. --- /tmp/wklog.40.old.11441 2009-11-05 12:37:16.000000000 +0200 +++ /tmp/wklog.40.new.11441 2009-11-05 12:37:16.000000000 +0200 @@ -1,9 +1,18 @@ OPTION: 2.5 Extend Query Events With Tables Info ================================================ -1. Query_log_event Binary Format -******************************** -Changes to be done: +1. Adding --binlog-with-tables-info option +****************************************** + +When set, Query events are to be written in the extended binary +format which contains tables_info. When not set, Query events +are to be written in usual format (without any changes). + +2. Query event extended binary format +************************************* + +When --binlog-with-tables-info is set, Query events are writen +to binary log in the following (extended) format. Query_log_event binary format --------------------------------- @@ -24,12 +33,12 @@ error_code 2 status_vars_len 2 + query_len 2 (see Note 1) -+ tables_info_len 2 (see Note 2) ++ tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 -+ db db_len (see Note 3) ++ db db_len (see Note 2) query query_len + tables_info @@ -37,7 +46,7 @@ --------------------------------- Name Size (bytes) --------------------------------- - db_len 1 (see Note 4) + db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len @@ -48,19 +57,99 @@ table_name table_name_len NOTES -1. Currently Query_log_event format doesn't include 'query_len' because +1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. -2. If tables_info is not included in the event (--binlog-with-tables-info - option), tables_info_len = 0. -3. The trailing zero is redundant since the length is already known. -4. In case of db = current db, db_len = 0 and db = empty, because - current db is already included in the current event format. +2. For 'db' (current db) the trailing zero is redundant since the length + is already known. +3. db_len = 0 means that this is the current db. + +When reading Query events from binary log, we can recognize its format +by its post-header length: in extended case the post-header includes 4 +additional bytes. + + #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) ++ #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) + ... + #define Q_STATUS_VARS_LEN_OFFSET 11 ++ #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 ++ #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 + +3. Changes in log events +************************ + +3.1. Format description event +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Changes needed here concern setting post-header length for Query events. +This setting is done in Format description event constructor which creates +the event for writing to binary log: + + if (binlog_with_tables_info) + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; + else + post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; + +This change is to be done only for case binlog_ver = 4. + +NOTE. The refered above constructor is allowed to be invoked in a client +context for creating "artificial" Format description events in case of +MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems +(because of 'binlog_with_tables_info') and taking into account the +"MySQL < 5.0" restriction, we have to #ifdef out the above code in +following manner: + + switch (binlog_ver) { + case 4: /* MySQL 5.0 and higher */ ++ #ifndef MYSQL_CLIENT + ... ++ #else ++ <error> ++ #endif + case 1: + case 3: + ... + } + +3.2. Query event +~~~~~~~~~~~~~~~~ +Changes needed here include adding tables_info and tables_info_len +members (member for query length already exists) and modifying the +following function-members: + +Query_log_event(buf) constructor +-------------------------------- +[Parses binary format written to the 'buf'] + +Getting post-header length from the Format description event (passed +to the constructor as an argument), define whether buf contains an +extended or usual Query event and parse the buf contents accordingly. + +NOTE. Defining Query event format here should be done with taking into +account that this constructor can be called within a Query-derived +event with the event_type argument != QUERY_EVENT. + +Query_log_event(thd) constructor +-------------------------------- +[Creates the event for binlogging] + +In case of binlog_with_tables_info = TRUE, set additionally query_len, +tables_info_len, and tables_info members (the constructor is to have +an additional 'tables_info' argument). + +write() function +---------------- +[Writes the event to binlog] + +In case of binlog_with_tables_info = TRUE, write additional members +(query_len, tables_info_len, and tables_info) to binary log. Also +write corresponding whole event length to the common-header. + +<To be continued> -2. Where to get tables info from? +4. Where to get tables info from? ********************************* -2.1. Case study: CREATE TABLE -****************************** +4.1. Case study: CREATE TABLE +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] @@ -129,4 +218,4 @@ } } -To be continued +<To be continued> -=-=(Alexi - Wed, 04 Nov 2009, 10:21)=-=- Low Level Design modified. --- /tmp/wklog.40.old.6734 2009-11-04 10:21:20.000000000 +0200 +++ /tmp/wklog.40.new.6734 2009-11-04 10:21:20.000000000 +0200 @@ -21,9 +21,9 @@ slave_proxy_id 4 exec_time 4 db_len 1 -+ query_len 2 (see Note 1) error_code 2 status_vars_len 2 ++ query_len 2 (see Note 1) + tables_info_len 2 (see Note 2) --------------------------------- BODY: -=-=(Alexi - Tue, 03 Nov 2009, 11:19)=-=- Low Level Design modified. --- /tmp/wklog.40.old.7187 2009-11-03 11:19:22.000000000 +0200 +++ /tmp/wklog.40.new.7187 2009-11-03 11:19:22.000000000 +0200 @@ -1 +1,132 @@ +OPTION: 2.5 Extend Query Events With Tables Info +================================================ +1. Query_log_event Binary Format +******************************** +Changes to be done: + + Query_log_event binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + COMMON HEADER: + timestamp 4 + type 1 + server_id 4 + total_size 4 + master_position 4 + flags 2 + --------------------------------- + POST HEADER: + slave_proxy_id 4 + exec_time 4 + db_len 1 ++ query_len 2 (see Note 1) + error_code 2 + status_vars_len 2 ++ tables_info_len 2 (see Note 2) + --------------------------------- + BODY: + status_vars status_vars_len +- db db_len + 1 ++ db db_len (see Note 3) + query query_len ++ tables_info + + tables_info binary format + --------------------------------- + Name Size (bytes) + --------------------------------- + db_len 1 (see Note 4) + db db_len + table_name_len 1 + table_name table_name_len + ... + db_len 1 + db db_len + table_name_len 1 + table_name table_name_len + +NOTES +1. Currently Query_log_event format doesn't include 'query_len' because + it considers the query to extent to the end of the event. +2. If tables_info is not included in the event (--binlog-with-tables-info + option), tables_info_len = 0. +3. The trailing zero is redundant since the length is already known. +4. In case of db = current db, db_len = 0 and db = empty, because + current db is already included in the current event format. + +2. Where to get tables info from? +********************************* + +2.1. Case study: CREATE TABLE +****************************** + +*** CREATE TABLE table [SELECT ...] + + bool mysql_create_table_no_lock( + THD *thd, + const char *db, + const char *table_name, ...) + { + ... + // ------------------------------------- + // WL40: To be included in tables_info: + // * db, table_name + // * thd->lex->query_tables (tables refered to in + // the select-part; empty if no select-part) + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + +*** CREATE TABLE table LIKE src-table + + bool mysql_create_like_table( + ... + TABLE_LIST *table, + TABLE_LIST *src_table, + ...) + { + ... + if (thd->current_stmt_binlog_row_based) + { // RBR: In this case we don't replicate temp tables + if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) + { + if (src_table->table->s->tmp_table) + { // CREATE normal-table LIKE temp-table: + + // Generate new query without LIKE-part + store_create_info(thd, table, &query, create_info, FALSE); + + // ------------------------------------- + // WL40: To include to tables_info: + // * table (src_table is not included) + // ------------------------------------- + write_bin_log(thd, TRUE, query.ptr(), query.length()); + } + else + { // CREATE normal-table LIKE normal-table + + // ------------------------------------- + // WL40: To include to log_tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + // CREATE temp-table LIKE ... + // This case is not replicated + } + else + { // SBR: + // ------------------------------------- + // WL40: To include to tables_info: + // * table + // * src_table + // ------------------------------------- + write_bin_log(thd, TRUE, thd->query, thd->query_length); + } + } + +To be continued -=-=(Alexi - Mon, 02 Nov 2009, 11:34)=-=- Worked 2 hours on option 2.5 Worked 2 hours and estimate 48 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Mon, 02 Nov 2009, 11:20)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.4848 2009-11-02 11:20:24.000000000 +0200 +++ /tmp/wklog.40.new.4848 2009-11-02 11:20:24.000000000 +0200 @@ -90,3 +90,25 @@ It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. +2.5 Extend Query Events With Tables Info +---------------------------------------- + +We could extend query events structure with a tables info - a list of tables +which the query refers to: + + <current query event structure> + tables_info_len + dbase_len dbase + table_len table + ... + dbase_len dbase + table_len table + +Note. In case of <dbase> = current data base, we can set dbase_len = 0 + and dbase = empty because current query event structure already + includes current data base name. + +Note. Possibly it is reasonable also to add a --binlog-with-tables-info + option which defines whether tables info must be included to the + query events. + ------------------------------------------------------------ -=-=(View All Progress Notes, 15 total)=-=- http://askmonty.org/worklog/index.pl?tid=40&nolimit=1 DESCRIPTION: Replication slave can be set to filter updates to certain tables with --replicate-[wild-]{do,ignore}-table options. This task is about adding similar functionality to mysqlbinlog. HIGH-LEVEL SPECIFICATION: 1. Context ---------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has these replication slave options: --replicate-do-table=db.tbl --replicate-ignore-table=db.tbl --replicate-wild-do-table=pattern.pattern --replicate-wild-ignore-table=pattern.pattern They affect both RBR and SBR events. SBR events are checked after the statement has been parsed, the server iterates over list of used tables and checks them againist --replicate instructions. What is interesting is that this scheme still allows to update the ignored table through a VIEW. 2. Table filtering in mysqlbinlog --------------------------------- Per-table filtering of RBR events is easy (as it is relatively easy to extract the name of the table that the event applies to). Per-table filtering of SBR events is hard, as generally it is not apparent which tables the statement refers to. This opens possible options: 2.1 Put the parser into mysqlbinlog ----------------------------------- Once we have a full parser in mysqlbinlog, we'll be able to check which tables are used by a statement, and will allow to show behaviour identical to those that one obtains when using --replicate-* slave options. (It is not clear how much effort is needed to put the parser into mysqlbinlog. Any guesses?) 2.2 Use dumb regexp match ------------------------- Use a really dumb approach. A query is considered to be modifying table X if it matches an expression CREATE TABLE $tablename DROP $tablename UPDATE ...$tablename ... SET // here '...' can't contain the word 'SET' DELETE ...$tablename ... WHERE // same as above ALTER TABLE $tablename .. etc (go get from the grammar) .. The advantage over doing the same in awk is that mysqlbinlog will also process RBR statements, and together with that will provide a working solution for those who are careful with their table names not mixing with string constants and such. (TODO: string constants are of particular concern as they come from [potentially hostile] users, unlike e.g. table aliases which come from [not hostile] developers. Remove also all string constants before attempting to do match?) 2.3 Have the master put annotations ----------------------------------- We could add a master option so that it injects into query a mark that tells which tables the query will affect, e.g. for the query UPDATE t1 LEFT JOIN db3.t2 ON ... WHERE ... the binlog will have /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... and further processing in mysqlbinlog will be trivial. 2.4 Implement server functionality to ignore certain tables ----------------------------------------------------------- We could add a general facility in the server to ignore certain tables: SET SESSION ignored_tables = "db1.t1,db2.t2"; This would work similar to --replicate-ignore-table, but in a general way not restricted to the slave SQL thread. It would then be trivial for mysqlbinlog to add such statements at the start of the output, or probably the user could just do it manually with no need for additional options for mysqlbinlog. It might be useful to integrate this with the code that already handles --replicate-ignore-db and similar slave options. 2.5 Extend Query Events With Tables Info ---------------------------------------- We could extend query events structure with a tables info - a list of tables which the query refers to: <current query event structure> tables_info_len dbase_len dbase table_len table ... dbase_len dbase table_len table Note. In case of <dbase> = current data base, we can set dbase_len = 0 and dbase = empty because current query event structure already includes current data base name. Note. Possibly it is reasonable also to add a --binlog-with-tables-info option which defines whether tables info must be included to the query events. LOW-LEVEL DESIGN: OPTION: 2.5 Extend Query Events With Tables Info ================================================ 1. Adding --binlog-with-tables-info option ****************************************** GLOBAL, read-only option. When set, Query events are to be written in the extended binary format which contains tables_info. When not set, Query events are to be written in usual format (without any changes). 2. Query event extended binary format ************************************* When --binlog-with-tables-info is set, Query events are writen to binary log in the following (extended) format. Query_log_event binary format --------------------------------- Name Size (bytes) --------------------------------- COMMON HEADER: timestamp 4 type 1 server_id 4 total_size 4 master_position 4 flags 2 --------------------------------- POST HEADER: slave_proxy_id 4 exec_time 4 db_len 1 error_code 2 status_vars_len 2 + query_len 2 (see Note 1) + tables_info_len 2 --------------------------------- BODY: status_vars status_vars_len - db db_len + 1 + db db_len (see Note 2) query query_len + tables_info tables_info binary format --------------------------------- Name Size (bytes) --------------------------------- db_len 1 (see Note 3) db db_len table_name_len 1 table_name table_name_len ... db_len 1 db db_len table_name_len 1 table_name table_name_len NOTES 1. In usual format, Query_log_event doesn't include 'query_len' because it considers the query to extent to the end of the event. 2. For 'db' (current db) the trailing zero is redundant since the length is already known. 3. In tables_info, db_len = 0 means that this is the current db. When reading Query events from binary log, we can recognize its format by its post-header length: in extended case the post-header includes 4 additional bytes. #define QUERY_HEADER_LEN (QUERY_HEADER_MINIMAL_LEN + 4) + #define QUERY_HEADER_LEN_EXT (QUERY_HEADER_LEN + 4) ... #define Q_STATUS_VARS_LEN_OFFSET 11 + #define Q_QUERY_LEN_OFFSET Q_STATUS_VARS_LEN_OFFSET + 2 + #define Q_QUERY_TABLES_INFO_LEN_OFFSET Q_QUERY_LEN_OFFSET + 2 *********************************************************************** HELP NEEDED *********************************************************************** The QUERY_HEADER_LEN is used in the definition of MAX_LOG_EVENT_HEADER: log_event.h ~~~~~~~~~~~ #define MAX_LOG_EVENT_HEADER ( /* in order of Query_log_event::write */ \ LOG_EVENT_HEADER_LEN + /* write_header */ \ QUERY_HEADER_LEN + /* write_data */ \ EXECUTE_LOAD_QUERY_EXTRA_HEADER_LEN + /*write_post_header_for_derived */ \ MAX_SIZE_LOG_EVENT_STATUS + /* status */ \ NAME_LEN + 1) which is used only for setting thd->variables.max_allowed_packet mysql->net.max_packet_size Looks like (but I am not quite sure) that QUERY_HEADER_LEN can simply (without making any other changes) be substituted in this definition by QUERY_HEADER_LEN_EXT. Below I list all places where MAX_LOG_EVENT_HEADER is used: slave.cc ~~~~~~~~ static int init_slave_thread(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_allowed_packet on all slave threads, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet= global_system_variables.max_allowed_packet + MAX_LOG_EVENT_HEADER; /* note, incr over the global not session var */ ... } pthread_handler_t handle_slave_io(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN to the max_packet_size on the I/O thread, since a replication event can become this much larger than the corresponding packet (query) sent from client to master. */ mysql->net.max_packet_size= thd->net.max_packet_size+= MAX_LOG_EVENT_HEADER; ... } sql_repl.cc ~~~~~~~~~~~ void mysql_binlog_send(...) { ... /* Adding MAX_LOG_EVENT_HEADER_LEN, since a binlog event can become this larger than the corresponding packet (query) sent from client to master. */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } bool mysql_show_binlog_events(...) { ... /* to account binlog event header size */ thd->variables.max_allowed_packet+= MAX_LOG_EVENT_HEADER; ... } 3. Changes in log events ************************ 3.1. Format description event ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Changes needed here concern setting post-header length for Query events. This setting is done in Format description event constructor which creates the event for writing to binary log: if (opt_binlog_with_tables_info) post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN_EXT; else post_header_len[QUERY_EVENT - 1] = QUERY_HEADER_LEN; This change is to be done only for case binlog_ver = 4. NOTE. The refered above constructor is allowed to be invoked in a client context for creating "artificial" Format description events in case of MySQL < 5.0 (e.g. see mysqlbinlog code). To avoid compilation problems (because of 'binlog_with_tables_info') and taking into account the "MySQL < 5.0" restriction, we have to #ifdef out the above code in following manner: switch (binlog_ver) { + #ifndef MYSQL_CLIENT case 4: /* MySQL 5.0 and higher */ ... break; + #endif case 1: case 3: ... } 3.2. Query event ~~~~~~~~~~~~~~~~ Changes needed here include adding tables_info and tables_info_len members (member for query length already exists) and modifying the following function-members: Query_log_event(buf) constructor -------------------------------- [Parses binary format written to the 'buf'] Getting post-header length from the Format description event (passed to the constructor as an argument), define whether buf contains an extended or usual Query event and parse the buf contents accordingly. NOTE. Defining Query event format here should be done with taking into account that this constructor can be called within a Query-derived event with the event_type argument != QUERY_EVENT. Query_log_event(thd) constructor -------------------------------- [Creates the event for binlogging] In case of opt_binlog_with_tables_info = TRUE, set additionally query_len, tables_info_len, and tables_info members (the constructor is to have an additional 'tables_info' argument). write() function ---------------- [Writes the event to binlog] In case of opt_binlog_with_tables_info = TRUE, write additional members (query_len, tables_info_len, and tables_info) to binary log. Also write corresponding whole event length to the common-header. <To be continued> 4. Where to get tables info from? ********************************* 4.1. Case study: CREATE TABLE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** CREATE TABLE table [SELECT ...] bool mysql_create_table_no_lock( THD *thd, const char *db, const char *table_name, ...) { ... // ------------------------------------- // WL40: To be included in tables_info: // * db, table_name // * thd->lex->query_tables (tables refered to in // the select-part; empty if no select-part) // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } *** CREATE TABLE table LIKE src-table bool mysql_create_like_table( ... TABLE_LIST *table, TABLE_LIST *src_table, ...) { ... if (thd->current_stmt_binlog_row_based) { // RBR: In this case we don't replicate temp tables if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE)) { if (src_table->table->s->tmp_table) { // CREATE normal-table LIKE temp-table: // Generate new query without LIKE-part store_create_info(thd, table, &query, create_info, FALSE); // ------------------------------------- // WL40: To include to tables_info: // * table (src_table is not included) // ------------------------------------- write_bin_log(thd, TRUE, query.ptr(), query.length()); } else { // CREATE normal-table LIKE normal-table // ------------------------------------- // WL40: To include to log_tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } // CREATE temp-table LIKE ... // This case is not replicated } else { // SBR: // ------------------------------------- // WL40: To include to tables_info: // * table // * src_table // ------------------------------------- write_bin_log(thd, TRUE, thd->query, thd->query_length); } } <To be continued> ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2771)
by knielsen＠knielsen-hq.org 25 Nov '09

25 Nov '09

#At lp:maria 2771 knielsen(a)knielsen-hq.org 2009-11-25 After-merge fix for merge of MySQL 5.1.41 into MariaDB. Manually apply fix for Bug#48340 (basically missing initialisation of thd->lex->local_file in Load_log_event::do_apply_event()) modified: sql/log_event.cc === modified file 'sql/log_event.cc' --- a/sql/log_event.cc 2009-11-16 20:49:51 +0000 +++ b/sql/log_event.cc 2009-11-25 09:15:05 +0000 @@ -4509,6 +4509,7 @@ int Load_log_event::do_apply_event(NET* as the present method does not call mysql_parse(). */ lex_start(thd); + thd->lex->local_file= local_fname; mysql_reset_thd_for_next_command(thd); if (!use_rli_only_for_errors)

1 0