MySQL Bugs: #31240: load data infile replication between (4.0 or 4.1) and 5.1 fails

Bug #31240	load data infile replication between (4.0 or 4.1) and 5.1 fails
Submitted:	27 Sep 2007 15:56	Modified:	4 Jun 12:00
Reporter:	Andrei Elkin
Status:	Verified
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Andrei Elkin	Target Version:	5.1+
Tags:	Contribution, bfsm_2007_10_18
Triage:	D2 (Serious)

Description:
Create_file_log_event that is prepared by 4.0 master can not be parsed
by neither 5.1 client (mysqlbinlog) nor server.
Comparing executions of 5.1 and 5.0 mysqlbinlog I found that
given an instant of a Create_file_log_event prepared by a 4.0th server 5.1th version of
mysqlbinlog (or slave server) computes
 field_lens= (uchar*)sql_ex.init()
wrong which results in incorrect table_name etc.

How to repeat:
Use binlog files uploaded to the CSC page; execute

mysqlbinlog --start-position=1042490638 --stop-position=1045408279 binlog_file_722

with version(mysqlbinlog) == 5.1.

Suggested fix:
Besides the technical issue itself, this must be a general issue of a possible upgrade
path.
The problem is vast already in that it's not trivial to test cross-version replications
when master and slave are separated by several major version like in the current case.

Setting to Verified as it was done so.

Some notes after studding logs.

I found the load-data event which neither 5.1 client mysqlbinlog nor
the 5.1 server can handle whereas 5.0 client parses it without a
problem.
This means we have a bug in backward compatibility.
I will continue with analysis tomorrow.

The current status of the error codes (5.1-rpl at the moment) is
#define ER_SLAVE_RELAY_LOG_READ_FAILURE 1592
#define ER_SLAVE_RELAY_LOG_WRITE_FAILURE 1593

which means that there was a shift in the error list once happened as the log says
> 070919 15:51:28 [ERROR] Slave I/O: Relay log write failure: could not queue
> event from master, Error_code: 1592

Hi Andrei,

Please see bug #31543 - I think it is a duplicate of this bug.  Can you try applying the
patch that I provided and see if the binlog from 4.0 can be parsed successfully?

-dave

Bug #31543 was marked as a duplicate of this one. We have a patch contributed there that
may fix the problem for 4.0 as well.

Thank you for your bug report. This issue has been addressed in the documentation. The
updated documentation will appear on our website shortly, and will be included in the next
release of the relevant products.

Added 4.0- -> 5.1+ with LOAD DATA to replication known issues.

Hi,
I have the same issue, LOAD DATA INFILE statement from a 4.1.22 master to a 5.1.22 slave
fails causing the replication slave_sql process to fail.
i had tried with applying the read_str_patch.diff patch and compiling the source 5.1.
no improvement.the statement is causing the slave_sql to fail with Error:0.
when i verified the binlog of (4.1 master) and relaylog of (5.1 slave) files ,the entried
for load data file are missing in the relaylog.

when test script is showing no errors.
./read_str_test.sh
4.1.22 binlog: ok
5.1.22 binlog: ok

/sql/log_event.cc has the entries of 
	read_str(&ptr, buf_end, (const char **) &line_start, &line_start_len) ||
 	read_str(&ptr, buf_end, (const char **) &escaped,    &escaped_len))
       return 0;
+    buf += ptr-buf;
     opt_flags = *buf++;
   }
   else
Please confirm if this patch fixes for 4.1 version.

4.1.25 - > 5.1.26 testcase

create a data.txt file with a few lines containing numbers:

1
2
3
4

run this on 4.1.25 master:
--------------------------

drop table if exists t1;
create table t1 (id int)engine=myisam;
load data local infile '/tmp/data.txt' into table t1;

The 5.1.26 slave breaks:
------------------------
080604 11:55:49 [Note] Slave I/O thread: connected to master
'root@192.168.250.4:3306',replication started in log 'FIRST' at position 4
080604 11:56:22 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,
and restart the slave SQL thread with "SLAVE START". We stopped at log 'box1-bin.000001'
position 267

I've posted a workaround on my blog at:
http://devananda-vdv.blogspot.com/2008/07/how-to-replicate-load-data-from-41-to.html

In essence, since 5.0 can read logs from 4.1, and 5.1 can read from 5.0, I stuck a 5.0
server in the middle and saved on resources by converting all its tables to the blackhole
engine. 

The doc team should update
http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html; the fourth
paragraph references this bug report, but incorrectly states that it only affects "a 4.0
or earlier master to a 5.1 or later slave."