WL#6403: Add idempotent mode to mysqlbinlog

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

The idea is to provide an idempotent mode of operation for mysql server.
In this mode the server will ignore the errors while applying row based events in 
a binlog file.

This mode is useful when a  DBA wants to replay binlogs using mysqlbinlog, to a 
server which may not contain, deliberately all data, so suppressing duplicate-key 
and no-key-found errors can be particularly useful.

The following changes will be needed.

1. A *SESSION ONLY* system variable which will be dynamic and can be set using a 
SET command.
   a. Proposed variable name: RBR_EXEC_MODE= STRICT|IDEMPOTENT (default: STRICT)
      The existing variable for slave thread SLAVE_EXEC_MODE will remain the
      way it is.
   b. Both will set the execution modes to IDEMPOTENT|STRICT for the 
corresponding threads(slave or user thread).
   c. This variable *cannot* be set from the command-line

2. Execution of the binlog events will be done based on the execution mode. Not 
much change will be required as this will reuse the code path that is currently
being used for the slave execution mode.

1. Adding a commandline argument '--idempotent' will be implemented which will
print, in the start of the output, a SET variable query:
This will instruct the server to execute the following events in idempotent
2. The above option will also print  "SET SESSION RBR_EXEC_MODE= STRICT;" at the 
end of the output to unset this variable to allow users to concatenate the 
output of idempotent and normal modes of mysqlbinlog in a single output file

One can use thSET SESSION RBR_EXEC_MODE= IDEMPOTENT;is feature as follows:
1. Start a mysql server normally, or use an existing server with some data
already  loaded. This server may or may not contain all the data require to
replay a given binlog

2. To replay a binlog file use  mysqlbinlog and mysql client as follows:
rohit@rohit-laptop$ ./mysqlbinlog --idempotent 
/path/to/binlog/file/to/replay|mysql -uroot -h... etc.


rohit@rohit-laptop:$ ./mysqlbinlog --idempotent /path/to/binlog/file/to/replay -
rohit@rohit-laptop:$ ./mysql -uroot -h...< out.sql
The following changes will be done.


1. Modify the members shared between slave_exec_mode and rbr_exec_mode i.e. 
%slave% => %rbr% for the good of the people maintaining the code.

2 Add a new rbr-exec-mode session variable

=== modified file 'sql/sys_vars.cc'
--- sql/sys_vars.cc     2012-08-31 21:54:56 +0000
+++ sql/sys_vars.cc     2012-09-06 12:16:32 +0000
@@ -730,6 +741,21 @@ static Sys_var_enum Sys_binlog_format(
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(binlog_format_check),
+static const char *rbr_exec_mode_names[]=
+       {"STRICT", "IDEMPOTENT", 0};
+static Sys_var_enum rbr_exec_mode(
+       "rbr_exec_mode",
+       "Modes for how row events should be executed. Legal values "
+       "are STRICT (default) and IDEMPOTENT. In IDEMPOTENT mode, "
+       "the server will not throw errors for operations that are idempotent. "
+       "In STRICT mode, server will throw errors for the operations that "
+       "cause a conflict.",
+       SESSION_VAR(rbr_exec_mode_options), NO_CMD_LINE,
+       rbr_exec_mode_names, DEFAULT(RBR_EXEC_MODE_STRICT),
+       ON_CHECK(prevent_global_rbr_exec_mode_idempotent),
+       ON_UPDATE(NULL));

3. Rename Row_log_event:slave_exec_mode to Row_log_event:rbr_exec_mode

4. In Rows_log_event::do_apply_event() the following hunk will be needed

@ -10856,7 +10856,10 @@
                              &m_cols_ai : &m_cols);
     bitmap_intersect(table->write_set, after_image);

-    this->slave_exec_mode= slave_exec_mode_options; // fix the mode
+    if (thd->slave_thread) // fix the mode for slave
+      this->rbr_exec_mode= slave_exec_mode_options;
+    else //fix the mode for user thread
+      this->rbr_exec_mode= thd->variables.rbr_exec_mode_options;

     // Do event specific preparations
     error= do_before_row_operations(rli);
 Here we check if the thd is a slave thread or a user session thread, and we set 
the value of Rli:rbr_exec_mode accordingly.

1. Add a new option for mysqlbinlog client 

myoption my_long_options[] =
  {"idempotent", 'i', "Notify the server to use idempotent mode on the "
   "server before applying Row Events", &idempotent_mode, &idempotent_mode, 0,
   GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},

2. make sure that a "SET @@session.rbr_exec_mode=IDEMPOTENT;" statement s 
printed at the start of the output.

+  /*
+    In case '--idempotent' or '-i' options has been used, we will notify the
+    server to use idempotent mode for the following events.
+   */
+  if (idempotent_mode)
+    fprintf(result_file,
+            "/*!50700 SET @@SESSION.RBR_EXEC_MODE=IDEMPOTENT*/;\n\n");

Also unset this variable at the end of the output