MySQL Bugs: #39701: Mixed binlog format does not switch to row mode on LOAD

Bug #39701	Mixed binlog format does not switch to row mode on LOAD_FILE
Submitted:	27 Sep 2008 16:59	Modified:	15 Apr 2009 10:00
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1.28, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	load_file, mixed, replication, row-based

Description:
MIXED mode binlog_format should switch to ROW mode whenever there is a risk of breaking replication.
However, LOAD_FILE, which depends on a file on the same host, does not trigger a switch to ROW based mode. 

Related to Bug#221

Example:
set binlog_format=mixed;
Query OK, 0 rows affected (0.00 sec)

insert into t1 values (load_file('/tmp/xx'));
Query OK, 1 row affected (0.00 sec)

show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000008
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.28-rc-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000008
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 213
       Info: use `test`; insert into t1 values (load_file('/tmp/xx'))
2 rows in set (0.00 sec)

set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

insert into t1 values (load_file('/tmp/xx'));
Query OK, 1 row affected (0.00 sec)

show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000008
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.28-rc-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000008
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 213
       Info: use `test`; insert into t1 values (load_file('/tmp/xx'))
*************************** 3. row ***************************
   Log_name: mysql-bin.000008
        Pos: 213
 Event_type: Query
  Server_id: 1
End_log_pos: 281
       Info: use `test`; BEGIN
*************************** 4. row ***************************
   Log_name: mysql-bin.000008
        Pos: 281
 Event_type: Table_map
  Server_id: 1
End_log_pos: 323
       Info: table_id: 19 (test.t1)
*************************** 5. row ***************************
   Log_name: mysql-bin.000008
        Pos: 323
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 376
       Info: table_id: 19 flags: STMT_END_F
*************************** 6. row ***************************
   Log_name: mysql-bin.000008
        Pos: 376
 Event_type: Query
  Server_id: 1
End_log_pos: 445
       Info: use `test`; COMMIT
6 rows in set (0.00 sec)

How to repeat:
use test;
select repeat('x',20) into outfile '/tmp/x';
drop table if exists t1;
create table t1 (t text);

set binlog_format='MIXED';
insert into t1 values (load_file('/tmp/x'));

# inspect the binlog file
SHOW BINLOG EVENTS;

set binlog_format='ROW';
insert into t1 values (load_file('/tmp/x'));
SHOW BINLOG EVENTS;

Suggested fix:
Add LOAD_FILE to the list of events that trigger a switch to ROW based mode when binlog_format is MIXED.

Thank yo for the report.

For me it looks like duplicate of bug #34283

Sorry: misread LOAD_FILE and LOAD DATA INFILE

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66553

2801 Luis Soares	2009-02-17
      BUG#39701: Mixed binlog format does not switch to row mode on LOAD_FILE
      
      MIXED mode binlog_format should switch to ROW mode whenever there is a risk of breaking 
      replication. However, LOAD_FILE, which depends on a file on the same host, does not trigger 
      a switch to ROW based mode. This leads to scenarios on which the slave replicates the 
      statement with 'load_file' and it will try to load the file from local file system. Given that 
      the file is mostly likely inexistent in slave filesystem the operation will not succeed 
      (probably returning NULL), causing master and slave(s) to diverge.
      
      This patch addresses this bug by marking the load_file function as unsafe. When in mixed mode 
      logging, this will make the procedure that decides on which logging format to use to be aware of
      the load_file function and decide accordingly.
      added:
        mysql-test/suite/rpl/r/rpl_mix_load_file.result
        mysql-test/suite/rpl/t/rpl_mix_load_file.test
      modified:
        sql/item_create.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68153

2801 Luis Soares	2009-03-04
      BUG#39701: Mixed binlog format does not switch to row mode on
      LOAD_FILE
      
      LOAD_FILE is not safe to replicate in STATEMENT mode, because it
      depends on a file (which is loaded on master and may not exist in
      slave(s)). This leads to scenarios on which the slave replicates the
      statement with 'load_file' and it will try to load the file from local
      file system. Given that the file may not exist in the slave filesystem
      the operation will not succeed (probably returning NULL), causing
      master and slave(s) to diverge. However, when using MIXED mode
      replication, this can be made to work, if the statement including
      LOAD_FILE is marked as unsafe, triggering a switch to ROW mode,
      meaning that the contents of the file are written to binlog as row
      events. Consequently, the contents from the file in the master will
      reach the slave via the binlog.
      
      This patch addresses this bug by marking the load_file function as
      unsafe. When in mixed mode and when LOAD_FILE is issued, there will be
      a switch to row mode. Furthermore, when in statement mode, the
      LOAD_FILE will raise a warning that the statement is unsafe in that
      mode.
     @ mysql-test/suite/rpl/r/rpl_loadfile.result
        Updated with the results from the test case added to this file.
     @ mysql-test/suite/rpl/r/rpl_stm_loadfile.result
        Result file for rpl_loadfile test split with the warnings in statement
        mode.
     @ mysql-test/suite/rpl/t/rpl_loadfile.test
        After splitting the original rpl_loadfile file, this one is only 
        required to be executed in mixed or row format.
        Appended the test for 39701 to this file.
     @ mysql-test/suite/rpl/t/rpl_stm_loadfile.test
        Split the original rpl_loadfile test because load_file now raises
        a warning when in statement mode. The goal of this split is 
        two-fold: i) make the test case more resilient; ii) assert that 
        warnings are indeed raised when in statement mode.
     @ sql/item_create.cc
        Added the set_stmt_unsafe call to lex.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68512

2801 Luis Soares	2009-03-06
      BUG#39701: Mixed binlog format does not switch to row mode on
      LOAD_FILE
            
      LOAD_FILE is not safe to replicate in STATEMENT mode, because it
      depends on a file (which is loaded on master and may not exist in
      slave(s)). This leads to scenarios on which the slave replicates the
      statement with 'load_file' and it will try to load the file from local
      file system. Given that the file may not exist in the slave filesystem
      the operation will not succeed (probably returning NULL), causing
      master and slave(s) to diverge. However, when using MIXED mode
      replication, this can be made to work, if the statement including
      LOAD_FILE is marked as unsafe, triggering a switch to ROW mode,
      meaning that the contents of the file are written to binlog as row
      events. Consequently, the contents from the file in the master will
      reach the slave via the binlog.
           
      This patch addresses this bug by marking the load_file function as
      unsafe. When in mixed mode and when LOAD_FILE is issued, there will be
      a switch to row mode. Furthermore, when in statement mode, the
      LOAD_FILE will raise a warning that the statement is unsafe in that
      mode.
     @ mysql-test/extra/rpl_tests/rpl_loadfile.test
        Extra file that is "sourced" on both rpl_loadfile and rpl_stm_loadfile
        test files.
     @ mysql-test/suite/rpl/r/rpl_loadfile.result
        Updated with the results from the test case added to this file.
     @ mysql-test/suite/rpl/r/rpl_stm_loadfile.result
        Result file for rpl_loadfile test split with the warnings in statement
        mode.
     @ mysql-test/suite/rpl/t/rpl_loadfile.test
        After splitting the original rpl_loadfile file, this one is only 
        required to be executed in mixed or row format.
        Appended the test for 39701 to this file.
     @ mysql-test/suite/rpl/t/rpl_stm_loadfile.test
        Split the original rpl_loadfile test because load_file now raises
        a warning when in statement mode. The goal of this split is 
        two-fold: i) make the test case more resilient; ii) assert that 
        warnings are indeed raised when in statement mode.
     @ sql/item_create.cc
        Added the set_stmt_unsafe call to lex.

Pushed to 5.1-bugteam and 6.0-bugteam.

Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:luis.soares@sun.com-20090324182733-oo3z07kj3lrazdqj) (merge vers: 5.1.34) (pib:6)

Documented in the 5.1.34 changelog as follows:

        The MIXED binary logging format did not switch to row-based mode
        for statements containing the LOAD_FILE() function.

Set status as NDI pending merge to 6.0 tree.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:luis.soares@sun.com-20090324184431-wckmuczwze1tt4qu) (merge vers: 6.0.11-alpha) (pib:6)

Fix also documented in 6.0.11 changelog; closed.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)