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.