Bug #18822 LOAD DATA FROM MASTER corrupts data
Submitted: 5 Apr 2006 21:02 Modified: 24 Sep 2006 23:59
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0 OS:Linux (linux)
Assigned to: Andrei Elkin CPU Architecture:Any
Tags: corruption, myisam

[5 Apr 2006 21:02] Victoria Reznichenko
Description:
LOAD DATA FROM MASTER corrupts table during copying to the slave.

On the master:

mysql> check table phpbb2_search_wordlist;
+-----------------------------+-------+----------+----------+
| Table                       | Op    | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------+
| test.phpbb2_search_wordlist | check | status   | OK       |
+-----------------------------+-------+----------+----------+
1 row in set (1.55 sec)

mysql> select count(*) from phpbb2_search_wordlist;
+----------+
| count(*) |
+----------+
|   255919 |
+----------+
1 row in set (0.00 sec)

On the slave after LOAD DATA FROM MASTER:

mysql> select count(*) from phpbb2_search_wordlist;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

and in the error log:

060405 15:42:11 [ERROR] Key 1 - Found wrong stored record at 0
060405 15:42:11 [Note] Retrying repair of: './test/phpbb2_search_wordlist' with keycache
060405 15:42:11 [ERROR] Key 1 - Found wrong stored record at 0

How to repeat:
1. restore table from the archive on the master
2. set up replication (better add option replicate-ignore-db=information_schema)
3. run LOAD DATA FROM MASTER on the slave
4. check number of rows on the slave
5. check error log file

Table was uploaded to ftp: load_data_from_master_corruption.zip
[18 Aug 2006 8:56] Erik van Dam
Having the same issue on mysql 4.1.12 pro!
[19 Aug 2006 1:07] Lars Thalmann
Since the current implementation of LOAD DATA FROM MASTER
is very limited, a possible plan is to deprecate this
functionality from versions 4.1, 5.0 and 5.1 and in
later versions (> 5.1) introduce a more advanced technique
(online backup) that cover more engines.
[29 Aug 2006 18:09] James Day
For 5.1 and earlier the recommended alternative solution 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.
[30 Aug 2006 18:26] 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.

A deprecation warning will be added for these statements in
4.1, 5.0 and 5.1 shortly.
[30 Aug 2006 23:00] 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/11136

ChangeSet@1.2538, 2006-08-31 02:00:40+03:00, aelkin@dsl-hkigw8-feaaf900-177.dhcp.inet.fi +1 -0
  BUG#18822 LOAD DATA FROM MASTER corrupts data
  
  there is a bunch of dups. It has been decided to declare this feature as
  deprecated.
[31 Aug 2006 11:48] Andrei Elkin
bug#9125, bug#12187, bug#14399, bug#15025, bug#20596 are non-to-be-fixable childs.
[31 Aug 2006 19:04] 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.
[23 Sep 2006 7:17] Lars Thalmann
Deprecation warning pushed into 4.1.22, 5.0.26 and 5.1.12.
[24 Sep 2006 23:59] Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs, and in
corrresponding upgrade sections of installation
chapter.