August 2009 - developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to filter certain kinds of statements (41)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter certain kinds of statements CREATION DATE..: Mon, 10 Aug 2009, 15:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 41 (http://askmonty.org/worklog/?tid=41) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:47)=-=- High-Level Specification modified. --- /tmp/wklog.41.old.13282 2009-08-10 15:47:13.000000000 +0300 +++ /tmp/wklog.41.new.13282 2009-08-10 15:47:13.000000000 +0300 @@ -2,3 +2,10 @@ - If we decide to parse the statement, SQL-verb filtering will be trivial - If we decide not to parse the statement, we still can reliably distinguish the statement by matching the first characters against a set of patterns. + +If we chose the second, we'll have to perform certain normalization before +matching the patterns: + - Remove all comments from the command + - Remove all pre-space + - Compare the string case-insensitively + - etc -=-=(Psergey - Mon, 10 Aug 2009, 15:35)=-=- High-Level Specification modified. --- /tmp/wklog.41.old.12689 2009-08-10 15:35:04.000000000 +0300 +++ /tmp/wklog.41.new.12689 2009-08-10 15:35:04.000000000 +0300 @@ -1 +1,4 @@ - +The implementation will depend on design choices made in WL#40: +- If we decide to parse the statement, SQL-verb filtering will be trivial +- If we decide not to parse the statement, we still can reliably distinguish the +statement by matching the first characters against a set of patterns. -=-=(Psergey - Mon, 10 Aug 2009, 15:31)=-=- Dependency created: 39 now depends on 41 DESCRIPTION: Add a mysqlbinlog option to filter certain kinds of statements, i.e. (syntax subject to discussion): mysqlbinlog --exclude='alter table,drop table,alter database,...' HIGH-LEVEL SPECIFICATION: The implementation will depend on design choices made in WL#40: - If we decide to parse the statement, SQL-verb filtering will be trivial - If we decide not to parse the statement, we still can reliably distinguish the statement by matching the first characters against a set of patterns. If we chose the second, we'll have to perform certain normalization before matching the patterns: - Remove all comments from the command - Remove all pre-space - Compare the string case-insensitively - etc ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to change the used database (36)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to change the used database CREATION DATE..: Fri, 07 Aug 2009, 14:57 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 36 (http://askmonty.org/worklog/?tid=36) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:41)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.13035 2009-08-10 15:41:51.000000000 +0300 +++ /tmp/wklog.36.new.13035 2009-08-10 15:41:51.000000000 +0300 @@ -1,5 +1,7 @@ Context ------- +(See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global +overview) At the moment, the server has a replication slave option --replicate-rewrite-db="from->to" -=-=(Guest - Mon, 10 Aug 2009, 11:12)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.6580 2009-08-10 11:12:36.000000000 +0300 +++ /tmp/wklog.36.new.6580 2009-08-10 11:12:36.000000000 +0300 @@ -1,4 +1,3 @@ - Context ------- At the moment, the server has a replication slave option @@ -67,6 +66,6 @@ It will be possible to do the rewrites either on the slave ( --replicate-rewrite-db will work for all kinds of statements), or in -mysqlbinlog (adding a comment is easy and doesn't require use to parse the -statement). +mysqlbinlog (adding a comment is easy and doesn't require mysqlbinlog to +parse the statement). -=-=(Psergey - Sun, 09 Aug 2009, 23:53)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.13425 2009-08-09 23:53:54.000000000 +0300 +++ /tmp/wklog.36.new.13425 2009-08-09 23:53:54.000000000 +0300 @@ -1 +1,72 @@ +Context +------- +At the moment, the server has a replication slave option + + --replicate-rewrite-db="from->to" + +the option affects +- Table_map_log_event (all RBR events) +- Load_log_event (LOAD DATA) +- Query_log_event (SBR-based updates, with the usual assumption that the + statement refers to tables in current database, so that changing the current + database will make the statement to work on a table in a different database). + +What we could do +---------------- + +Option1: make mysqlbinlog accept --replicate-rewrite-db option +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Make mysqlbinlog accept --replicate-rewrite-db options and process them to the +same extent as replication slave would process --replicate-rewrite-db option. + + +Option2: Add database-agnostic RBR events and --strip-db option +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Right now RBR events require a databasename. It is not possible to have RBR +event stream that won't mention which database the events are for. When I +tried to use debugger and specify empty database name, attempt to apply the +binlog resulted in this error: + +090809 17:38:44 [ERROR] Slave SQL: Error 'Table '.tablename' doesn't exist' on +opening tables, + +We could do as follows: +- Make the server interpret empty database name in RBR event (i.e. in a + Table_map_log_event) as "use current database". Binlog slave thread + probably should not allow such events as it doesn't have a natural current + database. +- Add a mysqlbinlog --strip-db option that would + = not produce any "USE dbname" statements + = change databasename for all RBR events to be empty + +That way, mysqlbinlog output will be database-agnostic and apply to the +current database. +(this will have the usual limitations that we assume that all statements in +the binlog refer to the current database). + +Option3: Enhance database rewrite +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +If there is a need to support database change for statements that use +dbname.tablename notation and are replicated as statements (i.e. are DDL +statements and/or DML statements that are binlogged as statements), +then that could be supported as follows: + +- Make the server's parser recognize special form of comments + + /* !database-alias(oldname,newname) */ + + and save the mapping somewhere + +- Put the hooks in table open and name resolution code to use the saved + mapping. + + +Once we've done the above, it will be easy to perform a complete, +no-compromise or restrictions database name change in binary log. + +It will be possible to do the rewrites either on the slave ( +--replicate-rewrite-db will work for all kinds of statements), or in +mysqlbinlog (adding a comment is easy and doesn't require use to parse the +statement). + -=-=(Psergey - Sun, 09 Aug 2009, 12:27)=-=- Dependency created: 39 now depends on 36 -=-=(Psergey - Fri, 07 Aug 2009, 14:57)=-=- Title modified. --- /tmp/wklog.36.old.14687 2009-08-07 14:57:49.000000000 +0300 +++ /tmp/wklog.36.new.14687 2009-08-07 14:57:49.000000000 +0300 @@ -1 +1 @@ -Add a mysqlbinlog option to change the database +Add a mysqlbinlog option to change the used database DESCRIPTION: Sometimes there is a need to take a binary log and apply it to a database with a different name than the original name of the database on binlog producer. If one is using statement-based replication, he can achieve this by grepping out "USE dbname" statements out of the output of mysqlbinlog(*). With row-based replication this is no longer possible, as database name is encoded within the the BINLOG '....' statement. This task is about adding an option to mysqlbinlog that would allow to change the names of used databases in both RBR and SBR events. (*) this implies that all statements refer to tables in the current database, doesn't catch updates made inside stored functions and so forth, but still works for a practially-important subset of cases. HIGH-LEVEL SPECIFICATION: Context ------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has a replication slave option --replicate-rewrite-db="from->to" the option affects - Table_map_log_event (all RBR events) - Load_log_event (LOAD DATA) - Query_log_event (SBR-based updates, with the usual assumption that the statement refers to tables in current database, so that changing the current database will make the statement to work on a table in a different database). What we could do ---------------- Option1: make mysqlbinlog accept --replicate-rewrite-db option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Make mysqlbinlog accept --replicate-rewrite-db options and process them to the same extent as replication slave would process --replicate-rewrite-db option. Option2: Add database-agnostic RBR events and --strip-db option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Right now RBR events require a databasename. It is not possible to have RBR event stream that won't mention which database the events are for. When I tried to use debugger and specify empty database name, attempt to apply the binlog resulted in this error: 090809 17:38:44 [ERROR] Slave SQL: Error 'Table '.tablename' doesn't exist' on opening tables, We could do as follows: - Make the server interpret empty database name in RBR event (i.e. in a Table_map_log_event) as "use current database". Binlog slave thread probably should not allow such events as it doesn't have a natural current database. - Add a mysqlbinlog --strip-db option that would = not produce any "USE dbname" statements = change databasename for all RBR events to be empty That way, mysqlbinlog output will be database-agnostic and apply to the current database. (this will have the usual limitations that we assume that all statements in the binlog refer to the current database). Option3: Enhance database rewrite ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If there is a need to support database change for statements that use dbname.tablename notation and are replicated as statements (i.e. are DDL statements and/or DML statements that are binlogged as statements), then that could be supported as follows: - Make the server's parser recognize special form of comments /* !database-alias(oldname,newname) */ and save the mapping somewhere - Put the hooks in table open and name resolution code to use the saved mapping. Once we've done the above, it will be easy to perform a complete, no-compromise or restrictions database name change in binary log. It will be possible to do the rewrites either on the slave ( --replicate-rewrite-db will work for all kinds of statements), or in mysqlbinlog (adding a comment is easy and doesn't require mysqlbinlog to parse the statement). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to change the used database (36)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to change the used database CREATION DATE..: Fri, 07 Aug 2009, 14:57 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 36 (http://askmonty.org/worklog/?tid=36) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:41)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.13035 2009-08-10 15:41:51.000000000 +0300 +++ /tmp/wklog.36.new.13035 2009-08-10 15:41:51.000000000 +0300 @@ -1,5 +1,7 @@ Context ------- +(See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global +overview) At the moment, the server has a replication slave option --replicate-rewrite-db="from->to" -=-=(Guest - Mon, 10 Aug 2009, 11:12)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.6580 2009-08-10 11:12:36.000000000 +0300 +++ /tmp/wklog.36.new.6580 2009-08-10 11:12:36.000000000 +0300 @@ -1,4 +1,3 @@ - Context ------- At the moment, the server has a replication slave option @@ -67,6 +66,6 @@ It will be possible to do the rewrites either on the slave ( --replicate-rewrite-db will work for all kinds of statements), or in -mysqlbinlog (adding a comment is easy and doesn't require use to parse the -statement). +mysqlbinlog (adding a comment is easy and doesn't require mysqlbinlog to +parse the statement). -=-=(Psergey - Sun, 09 Aug 2009, 23:53)=-=- High-Level Specification modified. --- /tmp/wklog.36.old.13425 2009-08-09 23:53:54.000000000 +0300 +++ /tmp/wklog.36.new.13425 2009-08-09 23:53:54.000000000 +0300 @@ -1 +1,72 @@ +Context +------- +At the moment, the server has a replication slave option + + --replicate-rewrite-db="from->to" + +the option affects +- Table_map_log_event (all RBR events) +- Load_log_event (LOAD DATA) +- Query_log_event (SBR-based updates, with the usual assumption that the + statement refers to tables in current database, so that changing the current + database will make the statement to work on a table in a different database). + +What we could do +---------------- + +Option1: make mysqlbinlog accept --replicate-rewrite-db option +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Make mysqlbinlog accept --replicate-rewrite-db options and process them to the +same extent as replication slave would process --replicate-rewrite-db option. + + +Option2: Add database-agnostic RBR events and --strip-db option +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Right now RBR events require a databasename. It is not possible to have RBR +event stream that won't mention which database the events are for. When I +tried to use debugger and specify empty database name, attempt to apply the +binlog resulted in this error: + +090809 17:38:44 [ERROR] Slave SQL: Error 'Table '.tablename' doesn't exist' on +opening tables, + +We could do as follows: +- Make the server interpret empty database name in RBR event (i.e. in a + Table_map_log_event) as "use current database". Binlog slave thread + probably should not allow such events as it doesn't have a natural current + database. +- Add a mysqlbinlog --strip-db option that would + = not produce any "USE dbname" statements + = change databasename for all RBR events to be empty + +That way, mysqlbinlog output will be database-agnostic and apply to the +current database. +(this will have the usual limitations that we assume that all statements in +the binlog refer to the current database). + +Option3: Enhance database rewrite +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +If there is a need to support database change for statements that use +dbname.tablename notation and are replicated as statements (i.e. are DDL +statements and/or DML statements that are binlogged as statements), +then that could be supported as follows: + +- Make the server's parser recognize special form of comments + + /* !database-alias(oldname,newname) */ + + and save the mapping somewhere + +- Put the hooks in table open and name resolution code to use the saved + mapping. + + +Once we've done the above, it will be easy to perform a complete, +no-compromise or restrictions database name change in binary log. + +It will be possible to do the rewrites either on the slave ( +--replicate-rewrite-db will work for all kinds of statements), or in +mysqlbinlog (adding a comment is easy and doesn't require use to parse the +statement). + -=-=(Psergey - Sun, 09 Aug 2009, 12:27)=-=- Dependency created: 39 now depends on 36 -=-=(Psergey - Fri, 07 Aug 2009, 14:57)=-=- Title modified. --- /tmp/wklog.36.old.14687 2009-08-07 14:57:49.000000000 +0300 +++ /tmp/wklog.36.new.14687 2009-08-07 14:57:49.000000000 +0300 @@ -1 +1 @@ -Add a mysqlbinlog option to change the database +Add a mysqlbinlog option to change the used database DESCRIPTION: Sometimes there is a need to take a binary log and apply it to a database with a different name than the original name of the database on binlog producer. If one is using statement-based replication, he can achieve this by grepping out "USE dbname" statements out of the output of mysqlbinlog(*). With row-based replication this is no longer possible, as database name is encoded within the the BINLOG '....' statement. This task is about adding an option to mysqlbinlog that would allow to change the names of used databases in both RBR and SBR events. (*) this implies that all statements refer to tables in the current database, doesn't catch updates made inside stored functions and so forth, but still works for a practially-important subset of cases. HIGH-LEVEL SPECIFICATION: Context ------- (See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global overview) At the moment, the server has a replication slave option --replicate-rewrite-db="from->to" the option affects - Table_map_log_event (all RBR events) - Load_log_event (LOAD DATA) - Query_log_event (SBR-based updates, with the usual assumption that the statement refers to tables in current database, so that changing the current database will make the statement to work on a table in a different database). What we could do ---------------- Option1: make mysqlbinlog accept --replicate-rewrite-db option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Make mysqlbinlog accept --replicate-rewrite-db options and process them to the same extent as replication slave would process --replicate-rewrite-db option. Option2: Add database-agnostic RBR events and --strip-db option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Right now RBR events require a databasename. It is not possible to have RBR event stream that won't mention which database the events are for. When I tried to use debugger and specify empty database name, attempt to apply the binlog resulted in this error: 090809 17:38:44 [ERROR] Slave SQL: Error 'Table '.tablename' doesn't exist' on opening tables, We could do as follows: - Make the server interpret empty database name in RBR event (i.e. in a Table_map_log_event) as "use current database". Binlog slave thread probably should not allow such events as it doesn't have a natural current database. - Add a mysqlbinlog --strip-db option that would = not produce any "USE dbname" statements = change databasename for all RBR events to be empty That way, mysqlbinlog output will be database-agnostic and apply to the current database. (this will have the usual limitations that we assume that all statements in the binlog refer to the current database). Option3: Enhance database rewrite ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If there is a need to support database change for statements that use dbname.tablename notation and are replicated as statements (i.e. are DDL statements and/or DML statements that are binlogged as statements), then that could be supported as follows: - Make the server's parser recognize special form of comments /* !database-alias(oldname,newname) */ and save the mapping somewhere - Put the hooks in table open and name resolution code to use the saved mapping. Once we've done the above, it will be easy to perform a complete, no-compromise or restrictions database name change in binary log. It will be possible to do the rewrites either on the slave ( --replicate-rewrite-db will work for all kinds of statements), or in mysqlbinlog (adding a comment is easy and doesn't require mysqlbinlog to parse the statement). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

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

10 Aug '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-RawIdeaBin TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:41)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.12989 2009-08-10 15:41:23.000000000 +0300 +++ /tmp/wklog.40.new.12989 2009-08-10 15:41:23.000000000 +0300 @@ -1,6 +1,7 @@ - 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 -=-=(Guest - Mon, 10 Aug 2009, 14:52)=-=- Dependency created: 39 now depends on 40 -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High Level Description modified. --- /tmp/wklog.40.old.16985 2009-08-10 14:51:59.000000000 +0300 +++ /tmp/wklog.40.new.16985 2009-08-10 14:51:59.000000000 +0300 @@ -1,3 +1,4 @@ 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. +--replicate-[wild-]{do,ignore}-table options. + +This task is about adding similar functionality to mysqlbinlog. -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.16949 2009-08-10 14:51:33.000000000 +0300 +++ /tmp/wklog.40.new.16949 2009-08-10 14:51:33.000000000 +0300 @@ -1 +1,73 @@ +1. Context +---------- +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. 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. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

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

10 Aug '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-RawIdeaBin TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:41)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.12989 2009-08-10 15:41:23.000000000 +0300 +++ /tmp/wklog.40.new.12989 2009-08-10 15:41:23.000000000 +0300 @@ -1,6 +1,7 @@ - 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 -=-=(Guest - Mon, 10 Aug 2009, 14:52)=-=- Dependency created: 39 now depends on 40 -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High Level Description modified. --- /tmp/wklog.40.old.16985 2009-08-10 14:51:59.000000000 +0300 +++ /tmp/wklog.40.new.16985 2009-08-10 14:51:59.000000000 +0300 @@ -1,3 +1,4 @@ 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. +--replicate-[wild-]{do,ignore}-table options. + +This task is about adding similar functionality to mysqlbinlog. -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.16949 2009-08-10 14:51:33.000000000 +0300 +++ /tmp/wklog.40.new.16949 2009-08-10 14:51:33.000000000 +0300 @@ -1 +1,73 @@ +1. Context +---------- +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. 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. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to filter certain kinds of statements (41)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter certain kinds of statements CREATION DATE..: Mon, 10 Aug 2009, 15:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 41 (http://askmonty.org/worklog/?tid=41) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:35)=-=- High-Level Specification modified. --- /tmp/wklog.41.old.12689 2009-08-10 15:35:04.000000000 +0300 +++ /tmp/wklog.41.new.12689 2009-08-10 15:35:04.000000000 +0300 @@ -1 +1,4 @@ - +The implementation will depend on design choices made in WL#40: +- If we decide to parse the statement, SQL-verb filtering will be trivial +- If we decide not to parse the statement, we still can reliably distinguish the +statement by matching the first characters against a set of patterns. -=-=(Psergey - Mon, 10 Aug 2009, 15:31)=-=- Dependency created: 39 now depends on 41 DESCRIPTION: Add a mysqlbinlog option to filter certain kinds of statements, i.e. (syntax subject to discussion): mysqlbinlog --exclude='alter table,drop table,alter database,...' HIGH-LEVEL SPECIFICATION: The implementation will depend on design choices made in WL#40: - If we decide to parse the statement, SQL-verb filtering will be trivial - If we decide not to parse the statement, we still can reliably distinguish the statement by matching the first characters against a set of patterns. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Add a mysqlbinlog option to filter certain kinds of statements (41)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter certain kinds of statements CREATION DATE..: Mon, 10 Aug 2009, 15:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 41 (http://askmonty.org/worklog/?tid=41) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 10 Aug 2009, 15:35)=-=- High-Level Specification modified. --- /tmp/wklog.41.old.12689 2009-08-10 15:35:04.000000000 +0300 +++ /tmp/wklog.41.new.12689 2009-08-10 15:35:04.000000000 +0300 @@ -1 +1,4 @@ - +The implementation will depend on design choices made in WL#40: +- If we decide to parse the statement, SQL-verb filtering will be trivial +- If we decide not to parse the statement, we still can reliably distinguish the +statement by matching the first characters against a set of patterns. -=-=(Psergey - Mon, 10 Aug 2009, 15:31)=-=- Dependency created: 39 now depends on 41 DESCRIPTION: Add a mysqlbinlog option to filter certain kinds of statements, i.e. (syntax subject to discussion): mysqlbinlog --exclude='alter table,drop table,alter database,...' HIGH-LEVEL SPECIFICATION: The implementation will depend on design choices made in WL#40: - If we decide to parse the statement, SQL-verb filtering will be trivial - If we decide not to parse the statement, we still can reliably distinguish the statement by matching the first characters against a set of patterns. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Add a mysqlbinlog option to filter certain kinds of statements (41)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter certain kinds of statements CREATION DATE..: Mon, 10 Aug 2009, 15:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 41 (http://askmonty.org/worklog/?tid=41) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Add a mysqlbinlog option to filter certain kinds of statements, i.e. (syntax subject to discussion): mysqlbinlog --exclude='alter table,drop table,alter database,...' ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Add a mysqlbinlog option to filter certain kinds of statements (41)
by worklog-noreply＠askmonty.org 10 Aug '09

10 Aug '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a mysqlbinlog option to filter certain kinds of statements CREATION DATE..: Mon, 10 Aug 2009, 15:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 41 (http://askmonty.org/worklog/?tid=41) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Add a mysqlbinlog option to filter certain kinds of statements, i.e. (syntax subject to discussion): mysqlbinlog --exclude='alter table,drop table,alter database,...' 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 10 Aug '09

10 Aug '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-RawIdeaBin TASK ID........: 40 (http://askmonty.org/worklog/?tid=40) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High Level Description modified. --- /tmp/wklog.40.old.16985 2009-08-10 14:51:59.000000000 +0300 +++ /tmp/wklog.40.new.16985 2009-08-10 14:51:59.000000000 +0300 @@ -1,3 +1,4 @@ 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. +--replicate-[wild-]{do,ignore}-table options. + +This task is about adding similar functionality to mysqlbinlog. -=-=(Guest - Mon, 10 Aug 2009, 14:51)=-=- High-Level Specification modified. --- /tmp/wklog.40.old.16949 2009-08-10 14:51:33.000000000 +0300 +++ /tmp/wklog.40.new.16949 2009-08-10 14:51:33.000000000 +0300 @@ -1 +1,73 @@ +1. Context +---------- +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. 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 ---------- 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. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0