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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[2 Apr 2003 2:10] Guilhem Bichot
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.
[2 Apr 2003 2:13] Guilhem Bichot
Sorry, I meant
#rm /tmp/picture
and not
#rm /tmp/b
[2 Apr 2003 5:08] Guilhem Bichot
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
[26 Apr 2003 7:27] Michael Widenius
This issue is documented in the MySQL manual.
[26 Apr 2003 8:52] Guilhem Bichot
yes... I have documented all these bugs, indeed.
[2 Jul 2003 9:52] Guilhem Bichot
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.
[30 Sep 2008 8:23] Konstantin Osipov
Should be replicated with RBR ok.
In mixed mode the statement should switch to RBR if LOAD_FILE is used.

See also Bug#39701
[11 Nov 2008 12:47] Susanne Ebrecht
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.
[2 Mar 2009 11:22] Mats Kindahl
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;
[24 Mar 2009 18:59] Luis Soares
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.