[MDEV-23108] Point in time recovery of binary log fails when sql_mode=ORACLE Created: 2020-07-07  Updated: 2021-04-19  Resolved: 2020-07-22

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.3.24, 10.4.14, 10.5.5

Type: Bug Priority: Major
Reporter: Sujatha Sivakumar (Inactive) Assignee: Sujatha Sivakumar (Inactive)
Resolution: Fixed Votes: 1
Labels: None


 Description   

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';
CREATE TABLE t(f INT) ENGINE=INNODB;
INSERT INTO t VALUES (10);
DROP TABLE t;
FLUSH LOGS;
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/test.sql
 
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql

Output:
======

MariaDB Version 10.3.24-MariaDB-debug
 - SSL connections supported
 - binaries are debug compiled
 - binaries built with wsrep patch
Collecting tests...
Installing system database...
 
==============================================================================
 
TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------
 
worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 16020..16039
SET @@SQL_MODE = 'ORACLE';
CREATE TABLE t(f INT) ENGINE=INNODB;
INSERT INTO t VALUES (10);
DROP TABLE t;
FLUSH LOGS;
binlog.binlog_ptr_fails_oracle_mode1 'innodb,mix' [ fail ]
        Test ended at 2020-07-07 13:43:57
 
CURRENT_TEST: binlog.binlog_ptr_fails_oracle_mode1
ERROR 1064 (42000) at line 42: 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

Syntax error is reported for "BEGIN" statement.

 27 use `test`/*!*/;
 28 SET TIMESTAMP=1594109637/*!*/;
 29 SET @@session.pseudo_thread_id=10/*!*/;
 30 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 31 SET @@session.sql_mode=8858427918/*!*/;
 32 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 33 /*!\C latin1 *//*!*/;
 34 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
 35 SET @@session.lc_time_names=0/*!*/;
 36 SET @@session.collation_database=DEFAULT/*!*/;
 37 CREATE TABLE t(f INT) ENGINE=INNODB
 38 /*!*/;
 39 # at 473
 40 #200707 13:43:57 server id 1  end_log_pos 515 CRC32 0xa823c787  GTID 0-1-2 trans
 41 /*!100001 SET @@session.gtid_seq_no=2*//*!*/;
 42 BEGIN



 Comments   
Comment by Sujatha Sivakumar (Inactive) [ 2020-07-17 ]

Hello Elkin

Please review the fix for MDEV-23108.

Patch: https://github.com/MariaDB/server/commit/9b999e79a358de2c86ae44fe83cf6c62cff1d2ef
BuildBot Testresults: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.3-sujatha

Thank you.

Comment by Andrei Elkin [ 2020-07-17 ]

The patch is approved.

Comment by Sujatha Sivakumar (Inactive) [ 2020-07-22 ]

Fix is implemented in 10.3.24.

10.3 to 10.4 merge works fine.

All patches are tested on buildbot.

10.4 changes: https://github.com/MariaDB/server/commit/fb51ef85c8d60f88f49715ae5086476efdbf264d

In 10.5: The fix will have to be implemented in 'sql/log_event_client.cc'.
Patch: https://github.com/MariaDB/server/commit/8b9fe5b597898fc5f9faf11bdd49a83e19b57517

Generated at Thu Feb 08 09:19:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.