MySQL Bugs: #12187: LOAD TABLE FROM MASTER unable to load a certain MyISAM table

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

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.

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.

I've tested 5.0.18 to 5.0.18 and it has the same issue.

5.1.7 beta has the same bug. Using the mysql-compiled RPMs.

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

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

Test run on FreeBSD as well, since that was the original platform.  Again there was no problem.

We are experiencing this bug too using 5.0.18 as both master and slave, running on Solaris 9

Got the same issue during copy from 4.0.27 -master (x86 Solaris) to 5.0.19 -slave (FreeBSD)

The same one from 4.0.27 (x86 Solaris 9) to 5.0.21 (FreeBSD 5.4). =(

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.

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.

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.

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.