Bug #12187 LOAD TABLE FROM MASTER unable to load a certain MyISAM table
Submitted: 26 Jul 2005 21:30 Modified: 31 Aug 2006 11:50
Reporter: Guilhem Bichot Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0 OS:Solaris (Solaris)
Assigned to: Andrei Elkin CPU Architecture:Any

[26 Jul 2005 21:30] Guilhem Bichot
Description:
Testcase provided by our kind user at
The end result is that LOAD TABLE FROM MASTER produces an empty table.
See how-to-repeat for the testcase.

How to repeat:
Set up 4.1->5.0 replication (haven't tested with 4.1->4.1 and 5.0->5.0, should be tested too). Get it running fine. On master do:
CREATE TABLE `test` (`osid` int(10) unsigned NOT NULL default '0', `os` varchar(255) NOT NULL default '', PRIMARY KEY (`osid`), FULLTEXT KEY `os` (`os`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` VALUES (1, 'OS: Microsoft Windows XP Service Pack 2');
INSERT INTO `test` VALUES (2, 'OS: Microsoft Windows 98 SE A');
INSERT INTO `test` VALUES (3, 'OS: Microsoft Windows XP');
INSERT INTO `test` VALUES (4, 'OS: Microsoft Windows XP Service Pack 1');
INSERT INTO `test` VALUES (5, 'OS: Microsoft Windows 2000 Service Pack 4');
INSERT INTO `test` VALUES (6, 'OS: Microsoft Windows Millennium Edition\n');
INSERT INTO `test` VALUES (7, 'OS: Microsoft Windows 2000 Service Pack 2');
INSERT INTO `test` VALUES (8, 'OS: Microsoft Windows XP Dodatek Service Pack 2');
INSERT INTO `test` VALUES (9, 'OS: Microsoft Windows XP Dodatek Service Pack. 1');
INSERT INTO `test` VALUES (10, 'OS: Microsoft Windows XP Service Pack 2, v.2082');
INSERT INTO `test` VALUES (11, 'OS: Microsoft Windows Server; 2003 family');
INSERT INTO `test` VALUES (12, 'OS: Microsoft Windows XP Service Pack 2, v.2096');
INSERT INTO `test` VALUES (13, 'OS: Microsoft Windows 95');
INSERT INTO `test` VALUES (14, 'OS: Microsoft Windows 2000');
INSERT INTO `test` VALUES (15, 'OS: Microsoft Windows XP Szervizcsomag 1');
INSERT INTO `test` VALUES (16, 'OS: Microsoft Windows 98');
INSERT INTO `test` VALUES (17, 'OS: Microsoft Windows 95 OSR2 B');
INSERT INTO `test` VALUES (18, 'OS: Microsoft Windows 2000 Service Pack 3');
INSERT INTO `test` VALUES (19, 'OS: Microsoft Windows XP Service Pack 2, v.2055');
INSERT INTO `test` VALUES (20, 'OS: Microsoft Windows XP Service Pack 2, v.2135');
INSERT INTO `test` VALUES (21, 'OS: Microsoft Windows 95 OSR2 C');
INSERT INTO `test` VALUES (22, 'OS: Microsoft Windows XP Szervizcsomag 2');
INSERT INTO `test` VALUES (23, 'OS: Microsoft Windows 98 B');
INSERT INTO `test` VALUES (24, 'OS: Microsoft Windows 98 A');
INSERT INTO `test` VALUES (25, 'OS: Microsoft Windows 2000 Service Pack 1');
INSERT INTO `test` VALUES (26, 'OS: Microsoft Windows XP Service Pack 2, v.2149');
INSERT INTO `test` VALUES (27, 'OS: Microsoft Windows Millennium Edition\n A');
INSERT INTO `test` VALUES (28, 'OS: Microsoft Windows 2000 Service Pack 3, RC 3.51');
INSERT INTO `test` VALUES (29, 'OS: Microsoft Windows XP Service Pack 2, v.2162');
INSERT INTO `test` VALUES (30, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 2');
INSERT INTO `test` VALUES (31, 'OS: Microsoft Windows XP Service Pack 1, v.1081');
INSERT INTO `test` VALUES (32, 'OS: Microsoft Windows XP Service Pack 2, v.2126');
INSERT INTO `test` VALUES (33, 'OS: Microsoft Windows XP Service Pack 1, v.1050');
INSERT INTO `test` VALUES (34, 'OS: Microsoft Windows Millennium Edition');
INSERT INTO `test` VALUES (35, 'OS: Microsoft Windows 95 b');
INSERT INTO `test` VALUES (36, 'OS: Microsoft Windows 95 a');
INSERT INTO `test` VALUES (37, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 1');
INSERT INTO `test` VALUES (38, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1433');
INSERT INTO `test` VALUES (39, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1039');
INSERT INTO `test` VALUES (40, 'OS: Microsoft Windows XP Service Pack 2, v.2138');
INSERT INTO `test` VALUES (41, 'OS: Microsoft Windows 2000 Service Pack 4, RC 3.154');
INSERT INTO `test` VALUES (42, 'OS: Microsoft Windows XP Service Pack 1, v.1105');
INSERT INTO `test` VALUES (43, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1289');
INSERT INTO `test` VALUES (44, 'OS: Microsoft Windows NT Service Pack 2');
INSERT INTO `test` VALUES (45, 'OS: Microsoft Windows XP Service Pack 2, v.2120');
INSERT INTO `test` VALUES (46, 'OS: Microsoft Windows NT Service Pack 5');
INSERT INTO `test` VALUES (47, 'OS: Microsoft Windows XP Service Pack 2, v.1204');
INSERT INTO `test` VALUES (48, 'OS: Microsoft Windows 2000 Service Pack 4, RC 4.68');
INSERT INTO `test` VALUES (49, 'OS: Microsoft Windows XP Service Pack 2, v.2142');
INSERT INTO `test` VALUES (50, 'OS: Microsoft Windows Server; 2003 family Service Pack 1');
INSERT INTO `test` VALUES (51, 'OS:');

Check that you can see the new table `test` on the slave and the data is ok too.
Now on the slave do:
DROP TABLE `test`;
LOAD TABLE `test` FROM MASTER; 
and see in the slave's error log:
050726 23:20:45 [ERROR] Key 1 - Found wrong stored record at 0
050726 23:20:45 [Note] Retrying repair of: './test/test' with keycache
050726 23:20:45 [ERROR] Key 1 - Found wrong stored record at 0
and do
SLAVE> select * from test;
Empty set (0.00 sec)
(instead of 51 rows)
Here is the slave's table now:
[guilhem 23:25 ~] ll /m/data/5/2/test/test.*
-rw-rw----  1 guilhem users 8586 2005-07-26 23:20 /m/data/5/2/test/test.frm
-rw-rw----  1 guilhem users    0 2005-07-26 23:20 /m/data/5/2/test/test.MYD
-rw-rw----  1 guilhem users 1024 2005-07-26 23:20 /m/data/5/2/test/test.MYI

Suggested fix:
Maybe related to the fact that LOAD TABLE FROM MASTER, like LOAD DATA FROM MASTER, does ugly things with the index file. At least REPAIR TABLE test USE_FRM works fine, so LOAD is probably fixable. FYI Valgrind on slave shows no error during the LOAD.
Possibly related entry: BUG#9125.
[1 Feb 2006 22:38] Richard Sonnen
We can replicate this on multiple slaves running FreeBSD 6/MySQL 5.0.18, and FreeBSD 4.11/MySQL 5.0.18 against a master running FreeBSD 4.6.2/MySQL 4.0.22.   

Of 5 replicated production tables, 2 load, then drop to zero length with a 'Found wrong stored record at 0' error in the logs.  This occurs with either 'LOAD DATA FROM MASTER' or 'LOAD TABLE <tablename> FROM MASTER'.

Does put a bit of a kink in the upgrade path.
[17 Mar 2006 22:09] Patrick Feliciano
I've tested 5.0.18 to 5.0.18 and it has the same issue.
[18 Mar 2006 21:17] Steve Slater
5.1.7 beta has the same bug. Using the mysql-compiled RPMs.
[27 Apr 2006 3:55] Bugs System
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/5621
[27 Apr 2006 3:56] Greg Lehey
Created test case (rpl_load_data_from_master).  Under 5.0/5.0, it was not possible to reproduce this behaviour (i.e. the test case passed).
[1 May 2006 4:27] Greg Lehey
Test run on FreeBSD as well, since that was the original platform.  Again there was no problem.
[8 May 2006 9:34] Paul Abel
We are experiencing this bug too using 5.0.18 as both master and slave, running on Solaris 9
[15 May 2006 10:55] Michael Vychizhanin
Got the same issue during copy from 4.0.27 -master (x86 Solaris) to 5.0.19 -slave (FreeBSD)
[15 May 2006 10:57] Michael Vychizhanin
The same one from 4.0.27 (x86 Solaris 9) to 5.0.21 (FreeBSD 5.4). =(
[22 May 2006 2:36] Greg Lehey
It seems that this problem still appears in conjunction with Solaris, so I'm reopening this bug and changing the OS to Solaris.  It was set to "Linux", which doesn't match the problem description.
[9 Jun 2006 11:41] Julien Huang
I've tested this and experiencing the same issue.

- Master (Debian Sarge 32bits)
$ uname -a
Linux ns4 2.4.31 #2 SMP Mon Jul 18 17:25:56 CEST 2005 i686 GNU/Linux
$ mysql --version
mysql  Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i686) using readline 4.3

- Slave (Ubuntu 5.10 64bits)
$ uname -a
Linux gina 2.6.12-10-amd64-generic #1 Fri Apr 28 13:18:42 UTC 2006 x86_64 GNU/Linux
$ mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for unknown-linux-gnu (x86_64) using readline 5.0

Both MySQL servers are standard binaries.

We have also slaves running MySQL 4.1 on an Ubuntu 5.10 64bits system without the above issue.
$ uname -a
Linux eva 2.6.12-10-amd64-k8-smp #1 SMP Fri Apr 28 13:28:18 UTC 2006 x86_64 GNU/Linux
$ mysql --version
mysql  Ver 14.7 Distrib 4.1.15, for unknown-linux-gnu (x86_64) using readline 4.3

The problem seems to be in MySQL 5 branch.
[28 Jun 2006 7:29] Paul Abel
I've tested this on Solaris 8, which mirrors our production systems.
I can reproduce the problem using MySQL built Solaris 8 64bit binaries of 5.0.22 & 5.1.11beta.  4.1.20 works with no issues, so looks like the bug was introduced in version 5. 

I will try to capture the log output from the same versions but the debug builds.
[31 Aug 2006 19:05] 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.

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.
Another alternative is mysqlhotcopy, which is faster because
it does a binary copy, but works only for MyISAM tables.

A deprecation warning has been added for these statements in
4.1, 5.0 and 5.1.