MySQL Bugs: #221: LOAD_FILE is not replicated

Bug #221	LOAD_FILE is not replicated
Submitted:	2 Apr 2003 2:10	Modified:	24 Mar 2009 18:59
Reporter:	Guilhem Bichot	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0, 4.1, 5.1	OS:	Any (all)
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
See synopsis

How to repeat:
Stop the slave.
On master :
#echo 100 > /tmp/picture
create table t(a int);
insert into t select load_file("/tmp/picture");
#rm /tmp/b

On slave :
START SLAVE

See the error :
ERROR: 13  Can't get stat of '/tmp/picture' (Errcode: 2)
ERROR: 13  Can't get stat of '/tmp/picture' (Errcode: 2)
030402 11:44:33  Slave: error 'Can't get stat of '/tmp/picture' (Errcode: 2)' on query 'insert into t select load_file("/tmp/picture")', error_code=13
030402 11:44:33  Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'gbichot-bin.000001' position 354

Suggested fix:
Probably like we do for LOAD DATA INFILE : write the contents of the file
to the master's binlog using Append_block events etc.

Sorry, I meant
#rm /tmp/picture
and not
#rm /tmp/b

Suggested fix:
It may be simpler that LOAD DATA INFILE : we could write to the binlog :
SET SESSION @@LOAD_FILE_CONTENT="tagada";
insert into t select load_file("/tmp/picture");

and when the slave reads this, he know he must not try to read /tmp/picture but instead use the content of @@LOAD_FILE_CONTENT (this would be a new session variable). For several load_file in one query, we need
SET @LOAD_FILE_CONTENT_1="tagada";
SET @LOAD_FILE_CONTENT_2="tsoin-tsoin";
etc

This issue is documented in the MySQL manual.

yes... I have documented all these bugs, indeed.

The proposed previous solutions won't work for
INSERT INTO t SELECT LOAD_FILE(u.a) from u;
So the bug will be fixed when we have row-level binary logging.

Should be replicated with RBR ok.
In mixed mode the statement should switch to RBR if LOAD_FILE is used.

See also Bug#39701

I made several tests here by using actual MySQL 5.1 bzr tree.

Test:
$ echo 100 > /home/myhome/picture
create table t(i integer);
insert into t select load_file("/home/myhome/picture");

all work fine when master/slave is on same machine. So I used two machines here. One for the master the other for the slave. I used Unix (Ubuntu for master, FreeBSD for slave but this should not matter).

First I always did some easy tests to figure out that replication really works.

I put the file only on the master machine.

1) default bin_format (STATEMENT)
on master:
create table t(i integer);
insert into t select load_file("/home/myhome/picture");
select * from t;
result: 100

on slave:
select * from t;
result: NULL

stop daemons, add in my.cnf from master bin-format=mixed, start both daemons again ...
some tests if replication works ... it works

on master:
insert into t select load_file("/home/myhome/picture");
select * from t;
result: 100

on slave:
select * from t;
result: NULL

stop daemons again, changed my.cnf to bin-format=row, start daemons again
some tests if replication works ... it works

on master:
insert into t select load_file("/home/myhome/picture");
select * from t;
result: 100

on slave:
select * from t;
result: 100

Summary:
This is only fixed in row based replication neither in mixed nor statement based replication.

The workaround for this problem is to store the result in a user variable and then use that in the statement. For example:

  create table t1 (a int);
  set @my_int = load_file('/tmp/picture');
  insert into t1 select @my_int;

This won't be fixed.

There is a viable workaround. Furthermore, a mixed mode patch for supporting load_file has been just queued, which increases the options for making this work.

See BUG#39701.