13 July 2008

How to replicate LOAD DATA from 4.1 to 5.1

It turns out that there is a bug that causes replication on a 5.1 slave to fail when reading LOAD DATA statements from a 4.1-or-earlier master.

About a month ago, I set up a data warehouse for an old 4.1.21 system that can't be upgraded. I decided to use the latest 5.1 beta to take advantage of partitioning, but within the first day replication stopped rather suddenly and with a nondescript error:
[ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with
"SLAVE START". We stopped at log 'xxx' position xxx
Using mysqlbinlog, I compared the relay log and the masters binary log around that position, and noticed the relay log appeared corrupt! I don't recall the exact steps I took, but I isolated the problem to a LOAD DATA INFILE statement, and filed my first issue with MySQL Enterprise support.

The great folks at MySQL Support had a response for me within 30 minutes: it is a known bug, documented at http://bugs.mysql.com/bug.php?id=31240. Since skipping a major version is not a recommended practice, fixing it wasn't a priority for them. Thankfully there is a fairly easy workaround - create a "blackhole relay" to translate the logs into a format 5.1 can read.

In essence, since 5.0 can read logs from 4.1, and 5.1 can read from 5.0, stick a 5.0 server in the middle and save on resources by converting all its tables to the blackhole engine. The steps to do this are pretty simple, and the hardest [optional] part is running two versions of mysql on the same server side by side. mysqld_multi does a good job at managing multiple instances, and since that is fairly well documented - and it would work just as well to have the relay on it's own box - I'll skip the details of my setup and get straight to the replication configuration.
  1. Use your preferred method to initialize the data on the new 5.1 slave, but do not START SLAVE yet. You will need the initial replication position later, however.
  2. Use mysqldump --nodata to get just the DDL from the 4.1 master. Load this into the 5.0 server.
  3. Convert the tables on the 5.0 relay that you want to replicate to the blackhole engine. Of course, don't do this to the mysql.* tables. I used a cheesy bash script:

    for t in `mysql $SCHEMA -NBre 'show tables'`; do
    echo "alter table $SCHEMA.$t engine=blackhole;" >> runme.sql;
    cat runme.sql | mysql

  4. Tell the blackhole relay to read from your 4.1 master at the point-in-time that you initialized the 5.1 slave. If you created the 5.1 slave by using "mysqldump --master-data --single-transaction", then the positions you want can be read from the first few lines of the output of mysqldump.
  5. Set the 5.1 slave to read from the blackhole relay, making sure that it does not read all those ALTER statements. I suggest a quick RESET MASTER on the relay.
  6. Start replication on both the 5.0 and 5.1 servers!
The blackhole relay won't use any (significant) disk space for data files (since it only stores the DDL locally) and it won't use much CPU since it isn't maintaining any indexes. However, you do need to rotate it's binary logs, and the addition of a relay between the real master and slave does create a little delay before data propagates to the warehouse.

I've been running this configuration for over a month now with no problems :)


Sheeri K. Cabral said...

The MySQL manual states that while replication works for a newer slave and an older master (but not vice versa), that does NOT hold true for a master running 4.1 and a slave running 5.0 or higher.

So I'm not surprised you're finding failures, I'm actually surprised you're finding success with all the other commands....

Mark Callaghan said...

Where does the manual state that? That is not stated at http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html. The claim there is that this generally works.

Besides, 'Enterprise' in 'Enterprise MySQL' means that upwards compatibility should be asserted and tested.

Unknown said...

From the page Mark referenced:
"For example, a master of 4.1.23 and a slave of 5.0.24 should work together.... In general (but not always), slaves running MySQL 5.1.x can be used with older masters, but not the reverse."

The sentence following that was added because a few folks (including myself) have run into a specific issue, though the doc team didn't quite get it right when they added:
"However, there are known issues with trying to replicate from a 4.0 or earlier master to a 5.1 or later slave (Bug#31240)."

Bug#31240 is the bug to which I posted a workaround, but it affects "4.1 or earlier", not 4.0.

Anonymous said...

Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!