maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              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:

-=-=(Guest - Sun, 16 Aug 2009, 17:11)=-=-
High-Level Specification modified.
--- /tmp/wklog.36.old.27162     2009-08-16 17:11:12.000000000 +0300
+++ /tmp/wklog.36.new.27162     2009-08-16 17:11:12.000000000 +0300
@@ -13,6 +13,8 @@
   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).
 
+See also MySQL BUG#42941.
+
 What we could do
 ----------------
 

-=-=(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).

See also MySQL BUG#42941.

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)