[Maria-developers] 9b999e79a35: MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE
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
Hi, sujatha! On Jul 17, sujatha wrote:
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.
Why not to print START TRANSACTION unconditionally? It worsk in all modes, a user won't need to learn a new option. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hello Sergei, On 17/07/20 1:46 pm, Sergei Golubchik wrote:
Hi, sujatha!
On Jul 17, sujatha wrote:
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. Why not to print START TRANSACTION unconditionally? It worsk in all modes, a user won't need to learn a new option.
There can be user applications that parse the mysqlbinlog output for BEGIN, they may suffer if we change the expected BEGIN to something else. Hence the current solution was chosen for GA versions. In 10.6 the BEGIN can be replaced to START TRANSACTION unconditionally. Thank you S.Sujatha
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi, Sujatha! On Jul 17, Sujatha wrote:
Hello Sergei,
On 17/07/20 1:46 pm, Sergei Golubchik wrote:
Hi, sujatha!
On Jul 17, sujatha wrote:
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. Why not to print START TRANSACTION unconditionally? It worsk in all modes, a user won't need to learn a new option.
There can be user applications that parse the mysqlbinlog output for BEGIN, they may suffer if we change the expected BEGIN to something else. Hence the current solution was chosen for GA versions. In 10.6 the BEGIN can be replaced to START TRANSACTION unconditionally.
Backward compatibility is important, sure. Still, I like to make a difference between the intentional documented behavior, that we promise to keep the way it is, compatible. And things that just happened to be the way they are, internal implementation details that one shouldn't rely on. You know, order of records in the output of SELECT without ORDER BY. I feel like one should not rely on that and that we can change the latter at any time. Do you think that "BEGIN" belongs to the first category? Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
sujatha <sujatha.sivakumar@mariadb.com> writes:
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.
Why not instead turn off the sql_mode=oracle at the start of the mysqlbinlog output? Just like the output of mysqlbinlog already sets up a number of other session modes/parameters for the following statements to work correctly, eg: SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; In fact, the above output from my 10.3.22 mysqlbinlog already is setting sql_mode explicitly - why doesn't that clear the sql_mode=oracle and prevent the problem from occurring in the first place? Could that be the real bug? - Kristian.
Hello Kristian, Thank you for the email. Please find my replies inline. On 19/07/20 3:17 pm, Kristian Nielsen wrote:
sujatha <sujatha.sivakumar@mariadb.com> writes:
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. Why not instead turn off the sql_mode=oracle at the start of the mysqlbinlog output? Just like the output of mysqlbinlog already sets up a number of other session modes/parameters for the following statements to work correctly, eg:
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1073741824/*!*/;
In fact, the above output from my 10.3.22 mysqlbinlog already is setting sql_mode explicitly - why doesn't that clear the sql_mode=oracle and prevent the problem from occurring in the first place? Could that be the real bug?
DBA's enable sql_mode='ORACLE' when they would like to use ORACLE's PL/SQL language. Hence the syntax will be different than the default mode. For example: (sql_mode='ORACLE') ============ MariaDB [test]> set sql_mode='ORACLE'; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> DELIMITER // MariaDB [test]> CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END; -> // Query OK, 0 rows affected (0.056 sec) MariaDB [test]> DELIMITER ; For example: (sql_mode=DEFAULT) =========== MariaDB [(none)]> use test; Database changed MariaDB [test]> DELIMITER // MariaDB [test]> CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END; -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUT INT) AS BEGIN SELECT COUNT(*) INTO param1 FROM t; END' at line 1 Even if we turn off the sql_mode='ORACLE' at the start of mysqlbinlog output, the binlog replay will still fail as the ORACLE's PL/SQL syntax is not understood by regular parser. For example:(Replaying binlog generated with 'ORACLE' mode by replacing 'ORACLE/DEFAULT'. =========== ERROR 1064 (42000) at line 38 in file: 'test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"simpleproc"(param1 OUT INT) AS BEGIN SELECT COUNT(*) INTO param1 FROM t; ...' at line 1 Regarding Fix: ============== The approach taken in commit-id: '9b999e79a35' is not valid anymore. A new fix approach is considered. Please refer Commit-id: 'f963fa52ed0' 'mysqlbinlog' tool will replace all 'BEGIN' statements with 'START TRANSACTION' unconditionally. Thank you S.Sujatha
- Kristian.
sujatha <sujatha.sivakumar@mariadb.com> writes:
DBA's enable sql_mode='ORACLE' when they would like to use ORACLE's PL/SQL language.
But this is a property of the individual query executed, not a global property of the binlog. The binlog will in general consist of a mix of queries that require sql_mode=oracle, and queries that require default mode.
Even if we turn off the sql_mode='ORACLE' at the start of mysqlbinlog output, the binlog replay will still fail as the ORACLE's PL/SQL syntax is not understood by regular parser.
The mysqlbinlog output needs to set the correct sql_mode=oracle/default for each query, it's not enough to set it at the start of the output. And as Andrei pointed out, this is already done - just for the GTID event, it does not happen. Thus the BEGIN output by mysqlbinlog for GTID event can fail if the last query before that happened to set sql_mode=ORACLE. So it seems to me the natural fix here would be to set a default sql_mode as part of the output for GTID event which matches the syntax used (BEGIN). This makes the mysqlbinlog output for GTID work the same as other binlog events, and might also protect against other strange sql_modes that could interfere with the auto-generated "BEGIN" statement.
A new fix approach is considered. Please refer Commit-id: 'f963fa52ed0' 'mysqlbinlog' tool will replace all 'BEGIN' statements with 'START TRANSACTION' unconditionally.
That may be fine - though as you pointed out yourself to Sergei, there might be backwards compatibility concerns (especially when done in a GA release which gets automatically updated as security updates in production sites) for user scripts that parse the mysqlbinlog putput. So it seems worth considering if the fix of setting sql_mode for GTID event output, just as for other events, would be a more appropriate fix. Hope this helps, - Kristian.
Hello Kristian, Thank you for the email. On 24/07/20 3:07 am, Kristian Nielsen wrote:
sujatha <sujatha.sivakumar@mariadb.com> writes:
DBA's enable sql_mode='ORACLE' when they would like to use ORACLE's PL/SQL language. But this is a property of the individual query executed, not a global property of the binlog. The binlog will in general consist of a mix of queries that require sql_mode=oracle, and queries that require default mode.
Even if we turn off the sql_mode='ORACLE' at the start of mysqlbinlog output, the binlog replay will still fail as the ORACLE's PL/SQL syntax is not understood by regular parser. The mysqlbinlog output needs to set the correct sql_mode=oracle/default for each query, it's not enough to set it at the start of the output.
'sql_mode' can be set at session level. Hence it is effective for all the queries in the current session. And all queries should have their sql_mode=ORACLE. MariaDB [test]> set sql_mode=ORACLE; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> CREATE TABLE t1 (f INT) ENGINE=INNODB; Query OK, 0 rows affected (0.022 sec) MariaDB [test]> INSERT INTO t1 VALUES (10); Query OK, 1 row affected (0.012 sec) MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> INSERT INTO t1 VALUES (20); Query OK, 1 row affected (0.001 sec) MariaDB [test]> COMMIT; Query OK, 0 rows affected (0.011 sec) MariaDB [test]> BEGIN; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 MariaDB [test]> In the above output 'sql_mode=ORACLE' works fine for both DDLs and DMLs. The issue is 'mysqlbinlog' tool incorrectly outputs 'BEGIN' which is not a valid begin of transaction in 'sql_mode=ORACLE'. CREATE TABLE t1 (f INT) ENGINE=INNODB /*!*/; # at 480 #200724 10:21:13 server id 1 end_log_pos 522 CRC32 0xc570178e GTID 0-1-2 trans /*!100001 SET @@session.gtid_seq_no=2*//*!*/; BEGIN /*!*/; # at 522 #200724 10:21:13 server id 1 end_log_pos 620 CRC32 0xc7c8b5d1 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1595566273/*!*/; INSERT INTO t1 VALUES (10) /*!*/; # at 620 #200724 10:21:13 server id 1 end_log_pos 651 CRC32 0xda1d1e6a Xid = 17 COMMIT/*!*/; # at 651 #200724 11:32:41 server id 1 end_log_pos 693 CRC32 0xec4e2c1d GTID 0-1-3 trans /*!100001 SET @@session.gtid_seq_no=3*//*!*/; BEGIN /*!*/; # at 693 #200724 11:32:38 server id 1 end_log_pos 791 CRC32 0x45ada501 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1595570558/*!*/; INSERT INTO t1 VALUES (20) /*!*/; # at 791 #200724 11:32:41 server id 1 end_log_pos 822 CRC32 0x3fd8447c Xid = 20 COMMIT/*!*/; Current plan is to add 'sql_mode' to 'Gtid_log_event' in upcoming development version. For GA versions 'mysqlbinlog' tool will replace all 'BEGIN' statements with 'START TRANSACTION'. We discussed with Alexander Barkov and concluded that it is safe to print 'START TRANSACTION' unconditionally. With this we ensure that "mysqlbinlog | mysql" works fine as promised. Thank you S.Sujatha
And as Andrei pointed out, this is already done - just for the GTID event, it does not happen. Thus the BEGIN output by mysqlbinlog for GTID event can fail if the last query before that happened to set sql_mode=ORACLE.
So it seems to me the natural fix here would be to set a default sql_mode as part of the output for GTID event which matches the syntax used (BEGIN). This makes the mysqlbinlog output for GTID work the same as other binlog events, and might also protect against other strange sql_modes that could interfere with the auto-generated "BEGIN" statement.
A new fix approach is considered. Please refer Commit-id: 'f963fa52ed0' 'mysqlbinlog' tool will replace all 'BEGIN' statements with 'START TRANSACTION' unconditionally. That may be fine - though as you pointed out yourself to Sergei, there might be backwards compatibility concerns (especially when done in a GA release which gets automatically updated as security updates in production sites) for user scripts that parse the mysqlbinlog putput.
So it seems worth considering if the fix of setting sql_mode for GTID event output, just as for other events, would be a more appropriate fix.
Hope this helps,
- Kristian.
participants (3)
-
Kristian Nielsen -
Sergei Golubchik -
sujatha