coderchris.com

I’m looking to setup some replication between two servers and server B has none of the databases server A has, so obviously I have to populate B with A’s data.

Easy you might think, simply start the slave and issue some like:

mysql> load data from master;

But actually, in my case, this fails because of the following bug when loading master data and the fact that views exist on server A

As it says here http://bugs.mysql.com/bug.php?id=20596

[30 Aug 2006 20:36] Trudy Pelzer

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1, 5.0 and 5.1 of MySQL. We will introduce a more advanced technique (online backup) in a future (>5.1) version, that will have the added advantage of working with more storage engines.

Due to the deprecated status of the statements, this bug will not be fixed. For 5.1 and earlier, the recommended alternative solution to using LOAD DATA|TABLE FROM MASTER is mysqldump on the master piped (or output copied) to the mysql client on the slave. This also has the advantage of working for all storage engines.

A deprecation warning will be added for these statements in 4.1, 5.0 and 5.1 shortly.

Now there’s a number of ways around this e.g. exporting the views table, dropping them, doing load data from master then re-importing them or just copying the mysql data dir’s from A to B, or using mysql dump as suggested above, but this shouldn’t be necessary!

This is a bug that’s been known about by MySQL for almost 3 years, should be a fairly simple thing to fix really and hasn’t been. I don’t remember seeing any  deprecation warning in the latest version of MySQL and have wasted more time as a result.

Hopefully now SUN seem to be taking a proper hold of MySQL some of these more minor but pain in the ass issues will start to be fixed!!!

Related posts on coderchris.com:

1. MySQL 5.1 multiple tmpdirs and incorrect key file for table errors
   Well I found out a few new things today... Firstly if you see an error...
2. MySQL 5.1 partitioning and loosing all your databases (or not)!
   I can across a few other funky things in MySQL 5.1 today that I thought...
3. Google storage update: More space for your photos and email
   No google storage team, thank you... Hi, We wanted to let you know about some...
4. This Week’s Tweets: 2009-05-10
   This Week’s Tweets: 2009-05-03 http://ff.im/-2xkPE # Some Recent Stuff on Web Application Security http://ff.im/-2B8ZP...
5. Setting Up Ubuntu PHP 5.2, MySQL 5.1 and Apache 2 on a PS3
   Recently I wanted a new development box/streaming media server for use around the house and...