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 13:56	Modified:	4 Feb 2009 11:38
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Andrei Elkin	CPU Architecture:	Any
Tags:	bfsm_2007_10_18, Contribution, cross version replication, LOAD DATA INFILE

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."

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/63747

2749 Andrei Elkin	2009-01-21
      Bug #31240 load data infile replication between (4.0 or 4.1) and 5.1 fails
      
      The reason of the bug appeared to be an error introduced into 5.1 source code.
      That piece of code in Create_file_log_event::do_apply_event() did not have test coverage
      which allowed make test and pb pass smooth.
      
      Fixed with inverting the old value of the return value from 
      Create_file_log_event::do_apply_event().

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/63815

2749 Andrei Elkin	2009-01-22
      Bug #31240 load data infile replication between (4.0 or 4.1) and 5.1 fails
      
      It's a regression issue.
      The reason of the bug appeared to be an error introduced into 5.1 source code.
      A piece of code in Create_file_log_event::do_apply_event() did not have test
      coverage which made make test and pb unaware.
            
      Fixed with inverting the old value of the return value from 
      Create_file_log_event::do_apply_event().
      The rpl test suite is extended with `rpl_cross_version' the file to hold 
      regression cases similar to the current.

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/63917

2751 Andrei Elkin	2009-01-23
      Bug #31240 load data infile replication between (4.0 or 4.1) and 5.1 fails
      
      an additional changeset to remove printing a path name.

Pushed to bugteam trees.

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/64031

2748 Andrei Elkin	2009-01-26
      Bug #31240  	load data infile replication between (4.0 or 4.1) and 5.1 fails
      
      Overriding a default value of --secure-file-priv on pb.mtr with a value allowable 
      the test to pass on pb.

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/64035

2749 Andrei Elkin	2009-01-26
      Bug #31240  	load data infile replication between (4.0 or 4.1) and 5.1 fails
            
      Overriding a default value of --secure-file-priv on pb.mtr to be $MYSQLTEST_VARDIR which 
      makes pb's mtr happy to execute the regression test for the bug.

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/64118

2750 Andrei Elkin	2009-01-27
      Bug #31240  	load data infile replication between (4.0 or 4.1) and 5.1 fails
      
      Fixing pb failures caused by mtr invokes the test with --tmpdir to which
      there are  sensitive operations in the test
      and setup_fake_relay_log.inc also needed --secure-file-priv for its own separate
      directory.
      
      Fixed with removing SELECT INTO OUTFILE and deploying --exec echo instead in the marco.
      The test's opt file should contain --secure-file-priv=$MYSQL_TEST_DIR as the test
      operations deal with files located in $MYSQL_TEST_DIR.

Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:aelkin@mysql.com-20090127113330-pklbg0b2pgyeawe1) (merge vers: 5.1.32) (pib:6)

Documented in the 5.1.32 changelog as follows:

        LOAD DATA INFILE statements did not replicate correctly from 
        a master running MySQL 4.1 to a slave running MySQL 5.1.

I have not made any further substantial changes in "Replication Compatibility Between MySQL Versions" because my understanding is that our official position on this issue remains that we don't guarantee that replication that "skips" major versions will work.

Set status to Need Doc Info, because I need to know whether we'll also make this fix in 6.0.

Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:joro@sun.com-20090128151745-pfmwvldqshy3ia5y) (merge vers: 6.0.10-alpha) (pib:6)

Fix also documented in the 6.0.10 changelog. Closed.

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)