maria-developers team mailing list archive

[sujatha <sujatha.sivakumar@xxxxxxxxxxx>]

revision-id: 9b999e79a358de2c86ae44fe83cf6c62cff1d2ef (mariadb-10.3.21-180-g9b999e79a35)
parent(s): 73aa31fbfd793bdb597bb19c52118ab4e637f4bc
author: Sujatha
committer: Sujatha
timestamp: 2020-07-17 12:23:32 +0530
message:

MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE

Problem:
========
During point in time recovery of binary log syntax error is reported for
BEGIN statement and recovery fails.

Analysis:
=========
In MariaDB 10.3 and later, setting the sql_mode system variable to Oracle
allows the server to understand a subset of Oracle's PL/SQL language. When
sql_mode=ORACLE is set, it switches the parser from the MariaDB parser to
Oracle compatible parser. With this change 'BEGIN' is not considered as
'START TRANSACTION'. Hence the syntax error is reported.

Fix:
===
Add a new option to 'mysqlbinlog' tool named 'sql_mode_oracle'. When
'sql_mode_oracle' option is specified 'BEGIN' statements will be replaced
with 'START TRANSACTION' in the mysqlbinlog output.

---
 client/mysqlbinlog.cc                              |   7 +-
 .../compat/oracle/r/binlog_ptr_mysqlbinlog.result  | 100 +++++++++++++++++
 .../oracle/t/binlog_ptr_mysqlbinlog-master.opt     |   1 +
 .../compat/oracle/t/binlog_ptr_mysqlbinlog.test    | 120 +++++++++++++++++++++
 sql/log_event.cc                                   |  15 +--
 sql/log_event.h                                    |   1 +
 6 files changed, 237 insertions(+), 7 deletions(-)

diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc
index a732a6ab8c3..ad995629272 100644
--- a/client/mysqlbinlog.cc
+++ b/client/mysqlbinlog.cc
@@ -145,6 +145,7 @@ static MYSQL* mysql = NULL;
 static const char* dirname_for_local_load= 0;
 static bool opt_skip_annotate_row_events= 0;
 
+static my_bool opt_sql_mode_oracle= 0;
 static my_bool opt_flashback;
 #ifdef WHEN_FLASHBACK_REVIEW_READY
 static my_bool opt_flashback_review;
@@ -1045,7 +1046,8 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
   DBUG_ENTER("process_event");
   Exit_status retval= OK_CONTINUE;
   IO_CACHE *const head= &print_event_info->head_cache;
-
+  /* Propogate sql_mode_oracle settings to event */
+  ev->is_sql_mode_oracle= opt_sql_mode_oracle;
   /* Bypass flashback settings to event */
   ev->is_flashback= opt_flashback;
 #ifdef WHEN_FLASHBACK_REVIEW_READY
@@ -1704,6 +1706,9 @@ static struct my_option my_options[] =
    "use --base64-output=never",
    &short_form, &short_form, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
    0, 0},
+  {"sql_mode_oracle", 0, "Converts BEGIN statement to 'ORALCE' "
+    "sql_mode compatible START TRANSACTION.", &opt_sql_mode_oracle,
+    &opt_sql_mode_oracle, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"socket", 'S', "The socket file to use for connection.",
    &sock, &sock, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,
    0, 0},
diff --git a/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result b/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result
new file mode 100644
index 00000000000..3080a4b69a0
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result
@@ -0,0 +1,100 @@
+SET @@SQL_MODE = 'ORACLE';
+##########################################################################
+#      Test verifies Gtid_log_event/Xid_log_event specific print         #
+##########################################################################
+CREATE TABLE tm (f INT) ENGINE=MYISAM;
+INSERT INTO tm VALUES (10);
+CREATE TABLE t(f INT) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS
+BEGIN
+SELECT COUNT(*) INTO param1 FROM t;
+END;
+/
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+RETURN 10;
+END;
+/
+FLUSH LOGS;
+##########################################################################
+#      Delete data from master so that it can be restored from binlog    #
+##########################################################################
+DROP FUNCTION f1;
+DROP PROCEDURE simpleproc;
+DROP TABLE tm;
+DROP TABLE t;
+##########################################################################
+#      Post recovery using mysqlbinlog --sql-mode-oracle                 #
+##########################################################################
+SHOW TABLES;
+Tables_in_test
+t
+tm
+SELECT * FROM tm;
+f
+10
+SELECT * FROM t;
+f
+10
+SELECT f1();
+f1()
+10
+CALL simpleproc(@a);
+SELECT @a;
+@a
+1
+"*****     Clean Up     *****"
+DROP TABLE t,tm;
+DROP PROCEDURE simpleproc;
+DROP FUNCTION f1;
+RESET MASTER;
+##########################################################################
+#      Test verifies Gtid_log_event/Xid_log_event/Qery_log_event         #
+#      specific print along with flashback option                        #
+##########################################################################
+CREATE TABLE tm(f INT) ENGINE=MYISAM;
+INSERT INTO tm VALUES (10);
+INSERT INTO tm VALUES (20);
+CREATE TABLE t(f INT) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+INSERT INTO t VALUES (20);
+##########################################################################
+#     Initial data                                                       #
+##########################################################################
+SELECT * FROM tm;
+f
+10
+20
+SELECT * FROM t;
+f
+10
+20
+FLUSH LOGS;
+DELETE FROM tm WHERE f=20;
+DELETE FROM t WHERE f=20;
+FLUSH LOGS;
+##########################################################################
+#      Data after deletion                                               #
+##########################################################################
+SELECT * FROM tm;
+f
+10
+SELECT * FROM t;
+f
+10
+FOUND 2 /START TRANSACTION/ in test_sql_mode.sql
+##########################################################################
+#      Data after recovery using flashback, sql-mode-oracle              #
+##########################################################################
+SELECT * FROM tm;
+f
+10
+20
+SELECT * FROM t;
+f
+10
+20
+"*****     Clean Up     *****"
+DROP TABLE t,tm;
diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt
new file mode 100644
index 00000000000..8f0cc182f51
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt
@@ -0,0 +1 @@
+--flashback
diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test
new file mode 100644
index 00000000000..07a1b5c4fdb
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test
@@ -0,0 +1,120 @@
+# ==== Purpose ====
+#
+# Test verifies that a binary log which was generated by setting
+# 'sql_mod=ORACLE' can be succefully used for point in time recovery with
+# following options.
+# 1) mysqlbinlog --sql-mode-oracle
+# 2) mysqlbinlog --flashback --sql-mode-oracle
+#
+# BEGIN statement is printed in three places
+# 1) "Gtid_log_event::print"
+# 2) "Xid_log_event::print" if flashback is enabled
+# 3) "Query_log_event::print" if flashback is enabled and engine is
+#    non-transacional.
+#
+# Test verifies all these cases.
+#
+# ==== References ====
+#
+# MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE
+#
+--source include/have_log_bin.inc
+--source include/have_innodb.inc
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+SET @@SQL_MODE = 'ORACLE';
+
+--echo ##########################################################################
+--echo #      Test verifies Gtid_log_event/Xid_log_event specific print         #
+--echo ##########################################################################
+CREATE TABLE tm (f INT) ENGINE=MYISAM;
+INSERT INTO tm VALUES (10);
+
+CREATE TABLE t(f INT) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+
+DELIMITER /;
+CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS
+ BEGIN
+  SELECT COUNT(*) INTO param1 FROM t;
+ END;
+/
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+  RETURN 10;
+END;
+/
+DELIMITER ;/
+
+FLUSH LOGS;
+--echo ##########################################################################
+--echo #      Delete data from master so that it can be restored from binlog    #
+--echo ##########################################################################
+DROP FUNCTION f1;
+DROP PROCEDURE simpleproc;
+DROP TABLE tm;
+DROP TABLE t;
+
+--exec $MYSQL_BINLOG --sql-mode-oracle $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/test.sql
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql
+
+--echo ##########################################################################
+--echo #      Post recovery using mysqlbinlog --sql-mode-oracle                 #
+--echo ##########################################################################
+SHOW TABLES;
+SELECT * FROM tm;
+SELECT * FROM t;
+--horizontal_results
+SELECT f1();
+CALL simpleproc(@a);
+SELECT @a;
+
+--echo "*****     Clean Up     *****"
+DROP TABLE t,tm;
+DROP PROCEDURE simpleproc;
+DROP FUNCTION f1;
+--remove_file $MYSQLTEST_VARDIR/tmp/test.sql
+RESET MASTER;
+
+--echo ##########################################################################
+--echo #      Test verifies Gtid_log_event/Xid_log_event/Qery_log_event         #
+--echo #      specific print along with flashback option                        #
+--echo ##########################################################################
+CREATE TABLE tm(f INT) ENGINE=MYISAM;
+INSERT INTO tm VALUES (10);
+INSERT INTO tm VALUES (20);
+CREATE TABLE t(f INT) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+INSERT INTO t VALUES (20);
+--echo ##########################################################################
+--echo #     Initial data                                                       #
+--echo ##########################################################################
+SELECT * FROM tm;
+SELECT * FROM t;
+FLUSH LOGS;
+DELETE FROM tm WHERE f=20;
+DELETE FROM t WHERE f=20;
+FLUSH LOGS;
+
+--echo ##########################################################################
+--echo #      Data after deletion                                               #
+--echo ##########################################################################
+SELECT * FROM tm;
+SELECT * FROM t;
+--exec $MYSQL_BINLOG --flashback --sql-mode-oracle $MYSQLD_DATADIR/master-bin.000002 > $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql
+
+--let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/test_sql_mode.sql
+--let SEARCH_PATTERN=START TRANSACTION
+--source include/search_pattern_in_file.inc
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql
+
+--echo ##########################################################################
+--echo #      Data after recovery using flashback, sql-mode-oracle              #
+--echo ##########################################################################
+SELECT * FROM tm;
+SELECT * FROM t;
+
+--echo "*****     Clean Up     *****"
+DROP TABLE t,tm;
+--remove_file $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 57a39085e86..e06e6a1b1d6 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -5358,8 +5358,9 @@ bool Query_log_event::print(FILE* file, PRINT_EVENT_INFO* print_event_info)
     }
     else if (strcmp("COMMIT", query) == 0)
     {
-      if (my_b_write(&cache, (uchar*) "BEGIN", 5) ||
-          my_b_printf(&cache, "\n%s\n", print_event_info->delimiter))
+      if (my_b_printf(&cache, is_sql_mode_oracle ?
+                      "START TRANSACTION\n%s\n" : "BEGIN\n%s\n" ,
+                      print_event_info->delimiter))
         goto err;
     }
   }
@@ -8254,9 +8255,10 @@ Gtid_log_event::print(FILE *file, PRINT_EVENT_INFO *print_event_info)
         goto err;
   }
   if (!(flags2 & FL_STANDALONE))
-    if (my_b_printf(&cache, is_flashback ? "COMMIT\n%s\n" : "BEGIN\n%s\n", print_event_info->delimiter))
+    if (my_b_printf(&cache, is_flashback ? "COMMIT\n%s\n" :
+                    (is_sql_mode_oracle ? "START TRANSACTION\n%s\n" :
+                    "BEGIN\n%s\n"), print_event_info->delimiter))
       goto err;
-
   return cache.flush_data();
 err:
   return 1;
@@ -8937,8 +8939,9 @@ bool Xid_log_event::print(FILE* file, PRINT_EVENT_INFO* print_event_info)
         my_b_printf(&cache, "\tXid = %s\n", buf))
       goto err;
   }
-  if (my_b_printf(&cache, is_flashback ? "BEGIN%s\n" : "COMMIT%s\n",
-                  print_event_info->delimiter))
+  if (my_b_printf(&cache, is_flashback ? (is_sql_mode_oracle ?
+                  "START TRANSACTION\n%s\n" : "BEGIN\n%s\n") :
+                  "COMMIT\n%s\n", print_event_info->delimiter))
     goto err;
 
   return cache.flush_data();
diff --git a/sql/log_event.h b/sql/log_event.h
index 73809953d70..66e29579fbc 100644
--- a/sql/log_event.h
+++ b/sql/log_event.h
@@ -1270,6 +1270,7 @@ class Log_event
 
   /* The following code used for Flashback */
 #ifdef MYSQL_CLIENT
+  my_bool is_sql_mode_oracle;
   my_bool is_flashback;
   my_bool need_flashback_review;
   String  output_buf; // Storing the event output