MySQL Bugs: #59267: LOAD DATA LOCAL INFILE not executed on slave with SBR

Bug #59267	LOAD DATA LOCAL INFILE not executed on slave with SBR
Submitted:	4 Jan 2011 3:43	Modified:	9 Feb 2011 21:14
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.9	OS:	Windows
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	regression

Description:
If LOAD DATA LOCAL INFILE statement is replicated in statement format, it appears in the binary log and does not cause any errors or warnings neither on master nor on slave side, but it is not executed on slave.

I only observe the problem on Windows, the same test on Linux and Solaris Sparc worked as expected.

The behavior is different from 5.5.8 release where the statement worked on slave, hence the regression tag.

Output for the test case from 'How to repeat' section (note SELECT COUNT(*) from the table after master and slave are synchronized):

5.5.9
=====

DROP TABLE IF EXISTS t;
CREATE TABLE t ( i INT );
LOAD DATA LOCAL INFILE 'f.load' INTO TABLE t;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.9-log, Binlog ver: 4
master-bin.000001       107     Query   1       220     use `test`; DROP TABLE IF EXISTS `t` /* generated by server */
master-bin.000001       220     Query   1       307     use `test`; CREATE TABLE t ( i INT )
master-bin.000001       307     Query   1       375     BEGIN
master-bin.000001       375     Begin_load_query        1       399     ;file_id=1;block_len=1
master-bin.000001       399     Execute_load_query      1       616     use `test`; LOAD DATA LOCAL INFILE 'f.load' IGNORE INTO TABLE `t` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`i`) ;file_id=1
master-bin.000001       616     Query   1       685     COMMIT
SELECT 'Master', COUNT(*) FROM t;
Master  COUNT(*)
Master  1
SELECT 'Slave', COUNT(*) FROM t;
Slave   COUNT(*)
Slave   0
STOP SLAVE;

5.5.8
=====

DROP TABLE IF EXISTS t;
CREATE TABLE t ( i INT );
LOAD DATA LOCAL INFILE 'f.load' INTO TABLE t;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.8-log, Binlog ver: 4
master-bin.000001       107     Query   1       220     use `test`; DROP TABLE IF EXISTS `t` /* generated by server */
master-bin.000001       220     Query   1       307     use `test`; CREATE TABLE t ( i INT )
master-bin.000001       307     Query   1       375     BEGIN
master-bin.000001       375     Begin_load_query        1       399     ;file_id=1;block_len=1
master-bin.000001       399     Execute_load_query      1       616     use `test`; LOAD DATA LOCAL INFILE 'f.load' IGNORE INTO TABLE `t` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`i`) ;file_id=1
master-bin.000001       616     Query   1       685     COMMIT
SELECT 'Master', COUNT(*) FROM t;
Master  COUNT(*)
Master  1
SELECT 'Slave', COUNT(*) FROM t;
Slave   COUNT(*)
Slave   1
STOP SLAVE;

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

--disable_warnings
DROP TABLE IF EXISTS t;
--enable_warnings

--perl

open(FILE,">f.load") || die "Could not open f.load for writing: $!\n";
print FILE "1";
close(FILE);

EOF

CREATE TABLE t ( i INT );
LOAD DATA LOCAL INFILE 'f.load' INTO TABLE t;

SHOW BINLOG EVENTS;
SELECT 'Master', COUNT(*) FROM t;

--sync_slave_with_master
SELECT 'Slave', COUNT(*) FROM t;

# Cleanup
STOP SLAVE;
DROP TABLE t;
--connection master
DROP TABLE t;
--remove_file f.load

--exit

I agree with the analysis. The original patch was written for 5.1 and during up merge I got the endif misplaced. 

There might be another issue as well:

* From 5.1:
[..]
	    my_error(ER_TEXTFILE_NOT_READABLE, MYF(0), name);
	    DBUG_RETURN(TRUE);
    }
    if ((stat_info.st_mode & S_IFIFO) == S_IFIFO)
	    is_fifo = 1; 
#endif

    if ((file=my_open(name,O_RDONLY,MYF(MY_WME))) < 0)
      DBUG_RETURN(TRUE);
[..]

* And from 5.5:
[..]

	    my_error(ER_TEXTFILE_NOT_READABLE, MYF(0), name);
	    DBUG_RETURN(TRUE);
    }
    if ((file= mysql_file_open(key_file_load,
                               name, O_RDONLY, MYF(MY_WME))) < 0)
#endif

      DBUG_RETURN(TRUE);
[..]

As you see it seems is_fifo is never set if for rad_info(..) further down. This probably also got lost during up merge. Could you please investigate if this statement is needed in the new patch:

    if ((stat_info.st_mode & S_IFIFO) == S_IFIFO)
	    is_fifo = 1; 

I also think we also need a test case like the one suggested in the bug report. The whole purpose of the risky rearrangement of code was to be able to have reliable test cases on both windows and linux.

Since this issue did not appear in an actual release, no changelog entry is needed.

Closed without further action.

Pushed into mysql-5.5 5.5.10 (revid:jonathan.perkin@oracle.com-20110208140736-1173xnoipufbhowh) (version source revid:jonathan.perkin@oracle.com-20110208135903-jhzy6wq16b2fx7pg) (merge vers: 5.5.10) (pib:24)

Pushed into mysql-trunk 5.6.2 (revid:georgi.kodinov@oracle.com-20110208155412-tfy4l5hqxi0g7o41) (version source revid:georgi.kodinov@oracle.com-20110208154951-gzqgr74u4bndswi8) (merge vers: 5.6.2) (pib:24)

re-closing; see my previous comment.