maria-developers team mailing list archive

[sujatha <sujatha.sivakumar@xxxxxxxxxxx>]

Hello Kristian,

Thank you for the email.

On 24/07/20 3:07 am, Kristian Nielsen wrote:
> sujatha <sujatha.sivakumar@xxxxxxxxxxx> 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.