This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 Add a mysqlbinlog option to change the used database
Title
Task ID36
Queue
Version
Status
Priority
Copies to
Created byPsergey07 Aug 2009Done
Supervisor   
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level 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.
 Task Dependencies
Others waiting for Task 36Task 36 is waiting forGraph
 
 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. Note this bug is fixed in MySQL 5.1.37, which is not
merged into MariaDB at the time of writing, but planned to be merged before
release.

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).
 Low-Level Design
Content
-------
1. Adding rewrite-db option
2. Supporting rewrite-db option for RBR events
3. Supporting rewrite-db option for SBR events
   (Limited to affecting only USE statements)
4. Current status

1. Adding rewrite-db option
---------------------------

1.1. Syntax:
     --rewrite-db='db_from->db_to'

1.2. Add 'OPT_REWRITE_DB' to 'options_client' (in client_priv.h).

1.3. In mysqlbinlog.cc:

- Add { "rewrite-db", OPT_REWRITE_DB, ...} record to my_long_options:
- Add Rpl_filter object to mysqlbinlog.cc

      Rpl_filter* binlog_filter;

- Add corresponding switch case to get_one_option():

      case OPT_REWRITE_DB:
        <extract db-from and db-to strings>
        binlog_filter->add_db_rewrite(db_from, db_to);
        break;
     .
Note. To make Rpl_filter usable in a MYSQL_CLIENT context, few small
additional changes are required:

- In sql_list.cc/h, Sql_alloc::new(size_t) and Sql_alloc::new[](size_t)
  uses sql_alloc() which is THD dependent. These are to be modified
  as follows:

      #ifdef MYSQL_CLIENT
        extern MEM_ROOT sql_list_client_mem_root; // defined in sql_list.cc
      #endif

      class Sql_alloc
      { ...
        static void *operator new(size_t size) throw ()
        {
      #ifndef MYSQL_CLIENT
          return sql_alloc(size);
      #else
          return alloc_root(&sql_list_client_mem_root, size);
      #endif
        }
        static void *operator new[](size_t size) throw ()
        {
      #ifndef MYSQL_CLIENT
          return sql_alloc(size);
      #else
          return alloc_root(&sql_list_client_mem_root, size); 
      #endif
        }
      ...
      }

- In rpl_filter.cc:

      Rpl_filter::Rpl_filter() :
        ...
      {
      #ifdef MYSQL_CLIENT
        init_alloc_root(&sql_list_client_mem_root, ...);
      #endif
      ...
      }

     Rpl_filter::~Rpl_filter() 
     { ...
     #ifdef MYSQL_CLIENT
       free_root(&sql_list_client_mem_root, ...);
     #endif
     }

2. Supporting rewrite-db for RBR events
---------------------------------------

In binlog, each row operation event is preceded by Table map event(s) which maps
table id(s) to database and table names. So, it's enough to support rewriting
database name in a Table map.

2.1. Add rewrite_db() member to Table_map_log_event:

      int Table_map_log_event::rewrite_db(
        const char* new_db,
        size_t new_db_len,
        const Format_description_log_event* desc)
      {
        /* 1. In temp_buf member (possibly reallocating it) rewrite
              event length, db length, and db parts
           2. Change m_dblen and m_dbnam members
        */
      }

Comment. This function assumes that temp_buf member contains Table map
binlog representaion (temp_buf is used for creating corresponding
BINLOG statement).

2.2. In mysqlbinlog modify corresponding switch case in the
process_event() function:

      Exit_status process_event(
        PRINT_EVENT_INFO *print_event_info,
        Log_event *ev, ...)
      {
        ...
        switch (ev_type) {
        ...
        case TABLE_MAP_EVENT:
        {
          Table_map_log_event *map= ((Table_map_log_event *)ev);
          if (shall_skip_database(map->get_db_name()))
          { ...
          }
          // WL36
          size_t new_len= 0;
          const char* new_db= binlog_filter->get_rewrite_db(
                                map->get_db_name(), &new_len);
          if (new_len && map->rewrite_db(new_db, new_len,
                                glob_description_event))
          { error("Could not rewrite database name");
            goto err;
          }
        }
        case WRITE_ROWS_EVENT:
        case DELETE_ROWS_EVENT:
        case UPDATE_ROWS_EVENT:
         ...
        }
        ...
      }

Comment. Rpl_filter::get_rewrite_db(db_from, &len): if filter contains
a (db_from, db_to) pair, this function returns pointer to db_to and
sets len = db_to length; otherwise, it returns db_from and does not
change len value.

3. Supporting rewrite-db for SBR events
---------------------------------------

Limited to emiting USE <db_to> instead of USE <db_from>.

USE statements can be emited by mysqlbinlog as a result of processing the
following events (see process_event() function):

- Query_log_event
- Load_log_event
- Execute_load_query_log_event [ :public Query_log_event ]
- Create_file_log_event        [ :public Load_log_event ]

TODO. Needed to check this list carefully (not sure for Create_file_log_event)
      Notes.
      - In replication, only Query_log_event and Load_log_event uses
        rpl_filter->get_rewrite_db();
      - In mysqlbinlog (process_event), Execute_load_query_log_event
        and Create_file_log_event are processed in separate switch
        cases. And Load_log_event is processed in the default switch case. 

Conditions for emiting use-statement:
- LOG_EVENT_SUPPRESS_USE_F is OFF for the event
  (e.g. it is ON for 'create database' statement)
- event's db name differs from db_name in PRINT_EVENT_INFO
  (PRINT_EVENT_INFO keeps db name of the last issued USE statement;
  initially, this db name is empty).

3.1. In mysqlbinlog.cc

- Add the following function:

      void print_use_stmt(Log_event* event, PRINT_EVENT_INFO* pinfo)
      {
          if (event->flags & LOG_EVENT_SUPPRESS_USE_F)
            return;
          /*
            - For events listed above get db_from = event->db;
            - If db_from is the same as pinfo->db then return;
            - If there is rewrite-db rule db_from->db_to,
              set db = db_to. Else set db = db_from;
            - Print "use <db>" to mysqlbinlog output
            - Set pinfo->db = db_from
              (this suppresses emiting use-statements by corresponding
              log_event's print-function)
          */
      }

- In process_event() function add switch case for Load_log_event and
  add print_use_stmt() invocations where needed (according to the
  events list above), e.g.:

      Exit_status process_event(
        PRINT_EVENT_INFO *print_event_info,
        Log_event *ev, ...)
      {
        ...
        switch (ev_type) {
        case QUERY_EVENT:
          if (shall_skip_database(((Query_log_event*)ev)->db))
            goto end;
          if (opt_base64_output_mode == BASE64_OUTPUT_ALWAYS)
          {
            // Possibly in case of rewite-db rule for ev->db
            // a warning should be emited here (see note below)
            ... write_event_header_and_base64(ev, ...) ...
          }
          else
          {
            print_use_stmt((Query_log_event*)ev, print_event_info);
            ev->print(result_file, print_event_info);
            }
          break;
        ...
        case LOAD_EVENT:
            print_use_stmt((Load_log_event*)ev, print_event_info);
            break;
        default:
            ...
        }
        ...
      }

Note. write_event_header_and_base64() does not print use-statement. It
produces BINLOG statement using ev->temp_buf content (i.e. the binary
log representation of the event). We don't rewrite temp_buf here with
db_to name (as we do it for Table map event) - this implies the
limitation 3 mentioned above.
Question: Is supporting of rewite_db + --base64-output really needed
currently?

4. Current status
-----------------

The outlined design (implemented for mysql-5.1.37) is tested for
simple test-cases.

TODO. 1. Check list of events which can emit use-statement.
      2. Supporting of rewite_db + --base64-output ?

 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Bothorsen4903 Nov 2009
Total49 
 Hrs WorkedProgressCurrentOriginal
This Task4900
Total4900
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Guest - Fri, 18 Jun 2010, 15:20
    
Version updated.
--- /tmp/wklog.36.old.11335	2010-06-18 15:20:26.000000000 +0000
+++ /tmp/wklog.36.new.11335	2010-06-18 15:20:26.000000000 +0000
@@ -1 +1 @@
-Server-9.x
+Server-5.3

(Guest - Thu, 17 Jun 2010, 00:39
    
Dependency deleted: WL#39 no longer depends on WL#36

(Guest - Sat, 07 Nov 2009, 22:43
    
Category updated.
--- /tmp/wklog.36.old.9112	2009-11-07 22:43:50.000000000 +0200
+++ /tmp/wklog.36.new.9112	2009-11-07 22:43:50.000000000 +0200
@@ -1 +1 @@
-Server-RawIdeaBin
+Server-Sprint

(Guest - Sat, 07 Nov 2009, 22:43
    
Status updated.
--- /tmp/wklog.36.old.9112	2009-11-07 22:43:50.000000000 +0200
+++ /tmp/wklog.36.new.9112	2009-11-07 22:43:50.000000000 +0200
@@ -1 +1 @@
-Un-Assigned
+Complete

(Bothorsen - Tue, 03 Nov 2009, 13:49
    
More cleanup work done by Alexi, Bo and Sergey.
Worked 4 hours and estimate 0 hours remain (original estimate increased by 4 hours).

(Bothorsen - Tue, 03 Nov 2009, 13:49
    
Sergey and Bo has been working on getting the patch ready, and Alexi has fixed some issues with the
patch.
Worked 15 hours and estimate 0 hours remain (original estimate increased by 15 hours).

(Bothorsen - Tue, 03 Nov 2009, 13:47
    
Alexi has implemented a patch for this item.
Worked 30 hours and estimate 0 hours remain (original estimate increased by 30 hours).

(Guest - Tue, 15 Sep 2009, 18:04
    
Low Level Design modified.
--- /tmp/wklog.36.old.19322	2009-09-15 18:04:49.000000000 +0300
+++ /tmp/wklog.36.new.19322	2009-09-15 18:04:49.000000000 +0300
@@ -191,7 +191,7 @@
 
 - In process_event() function add switch case for Load_log_event and
    add print_use_stmt() invocations where needed (according to the
-   events lis above), e.g.:
+  events list above), e.g.:
 
       Exit_status process_event(
         PRINT_EVENT_INFO *print_event_info,

(Guest - Tue, 15 Sep 2009, 15:53
    
Low Level Design modified.
--- /tmp/wklog.36.old.13421	2009-09-15 15:53:31.000000000 +0300
+++ /tmp/wklog.36.new.13421	2009-09-15 15:53:31.000000000 +0300
@@ -150,10 +150,17 @@
 following events (see process_event() function):
 
 - Query_log_event
-- Execute_load_query_log_event
-- Create_file_log_event
-
-TODO. Needed to check this list requires carefully !!!
+- Load_log_event
+- Execute_load_query_log_event [ :public Query_log_event ]
+- Create_file_log_event        [ :public Load_log_event ]
+
+TODO. Needed to check this list carefully (not sure for Create_file_log_event)
+      Notes.
+      - In replication, only Query_log_event and Load_log_event uses
+        rpl_filter->get_rewrite_db();
+      - In mysqlbinlog (process_event), Execute_load_query_log_event
+        and Create_file_log_event are processed in separate switch
+        cases. And Load_log_event is processed in the default switch case. 
 
 Conditions for emiting use-statement:
 - LOG_EVENT_SUPPRESS_USE_F is OFF for the event
@@ -182,8 +189,9 @@
           */
       }
 
-- In process_event() function add print_use_stmt() invocations where
-  needed (according to the events lis above), e.g.:
+- In process_event() function add switch case for Load_log_event and
+   add print_use_stmt() invocations where needed (according to the
+   events lis above), e.g.:
 
       Exit_status process_event(
         PRINT_EVENT_INFO *print_event_info,
@@ -207,6 +215,11 @@
             }
           break;
         ...
+        case LOAD_EVENT:
+            print_use_stmt((Load_log_event*)ev, print_event_info);
+            break;
+        default:
+            ...
         }
         ...
       }

(Guest - Tue, 15 Sep 2009, 12:12
    
Low Level Design modified.
--- /tmp/wklog.36.old.3961	2009-09-15 12:12:26.000000000 +0300
+++ /tmp/wklog.36.new.3961	2009-09-15 12:12:26.000000000 +0300
@@ -144,6 +144,8 @@
 3. Supporting rewrite-db for SBR events
 ---------------------------------------
 
+Limited to emiting USE <db_to> instead of USE <db_from>.
+
 USE statements can be emited by mysqlbinlog as a result of processing the
 following events (see process_event() function):
 
-- View All Progress Notes (20 total) --


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB