maria-developers team mailing list archive

[sujatha <sujatha.sivakumar@xxxxxxxxxxx>]

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@xxxxxxxxxxx> 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.