Bug #9125 LOAD DATA FROM MASTER: Replication fails to start on slave
Submitted: 11 Mar 2005 11:36 Modified: 30 Aug 2006 18:28
Reporter: Conrad Micallef Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:4.0.22 OS:Linux (gentoo linux)
Assigned to: Andrei Elkin CPU Architecture:Any
Tags: corruption, myisam

[11 Mar 2005 11:36] Conrad Micallef
Description:
I have a 4.0.22 master and a new 4.0.22 slave
I had replication setup but I deleted all the (old) binlogs and attempted to restart replication.

when starting up and using LOAD DATA FROM MASTER i get the following error(s)
050311  9:56:45 Found block that points outside data file at 765612
050311  9:56:45 Found block that points outside data file at 765728
050311  9:56:45 Found block that points outside data file at 767268
050311  9:56:45 Found block that points outside data file at 767296
050311  9:56:45 Found block that points outside data file at 767324
050311  9:56:45 Found block that points outside data file at 768296
050311  9:56:45 Found block that points outside data file at 768324
050311  9:56:45 Found block that points outside data file at 768584
050311  9:56:45 Found block that points outside data file at 768712
050311  9:56:45 Found block that points outside data file at 768752
050311  9:56:45 Found block that points outside data file at 768976
050311  9:56:45 Found block that points outside data file at 768988
050311  9:56:45 Found block that points outside data file at 769236
050311  9:56:45 Found block that points outside data file at 769516
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 233983 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x82f5568
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
frame pointer (ebp) is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x83cc3e0 = CREATE TABLE `TMApplicationModules` (
  `ModuleID` int(11) NOT NULL auto_increment,
  `ApplicationId` int(11) NOT NULL default '0',
  `ModuleName` varchar(12) NOT NULL default '',
  `Module` longblob,
  `RecLastModified` datetime default NULL,
  PRIMARY KEY  (`ModuleID`),
  KEY `ApplicationId` (`ApplicationId`)
) TYPE=MyISAM
thd->thread_id=3

i downloaded debugging version and did a full backtrace:

0xb7ca640c in memcpy () from /lib/libc.so.6
(gdb) bt
#0  0xb7ca640c in memcpy () from /lib/libc.so.6
#1  0x081baf28 in _mi_rec_unpack (info=0x82d4d08, to=0x82d2fe1 "",
    from=0xa2735cb3 <Address 0xa2735cb3 out of bounds>, found_length=25)
    at mi_dynrec.c:1013
#2  0x081d0c5b in sort_get_next_record (sort_param=0xbf3fd494)
    at mi_check.c:2913
#3  0x081cc07b in mi_repair (param=0xbf3fdc54, info=0x82d4d08,
    name=0x8 <Address 0x8 out of bounds>, rep_quick=0) at mi_check.c:1285
#4  0x0815ddd9 in ha_myisam::repair(THD*, st_mi_check_param&, bool) (
    this=0x82d3fa0, thd=0x8295720, param=@0xbf3fdc54, optimize=8)
    at ha_myisam.cc:614
#5  0x0815d946 in ha_myisam::repair(THD*, st_ha_check_opt*) (this=0x82d3fa0,
    thd=0x8295720, check_opt=0xbf3ff1f4) at ha_myisam.cc:512
#6  0x08184cc5 in create_table_from_dump (thd=0x8295720, net=0xbf3ff344,
    db=0x82ace78 "ecom_transterminalnet",
    table_name=0x82f97e0 "TMApplicationModules", overwrite=true)
    at slave.cc:1240
#7  0x08184fc7 in fetch_master_table(THD*, char const*, char const*, st_master_info*, st_mysql*, bool) (thd=0x8295720,
    db_name=0x82ace78 "ecom_transterminalnet",
    table_name=0x82f97e0 "TMApplicationModules", mi=0x8, mysql=0xbf3ff344,
    overwrite=true) at slave.cc:1285
#8  0x08195f80 in fetch_db_tables (thd=0x8295720, mysql=0xbf3ff344,
---Type <return> to continue, or q <return> to quit---
    db=0x82ace78 "ecom_transterminalnet", table_res=0x82f50a8, mi=0x82903d0)
    at repl_failsafe.cc:726
#9  0x08196621 in load_master_data(THD*) (thd=0x8295720)
    at repl_failsafe.cc:866
#10 0x08102e52 in mysql_execute_command() () at sql_parse.cc:1564
#11 0x081042df in mysql_parse(THD*, char*, unsigned) (thd=0x8295720,
    inBuf=0x8295850 "\t", length=21) at sql_parse.cc:3023
#12 0x080ff038 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8295720,
    packet=0x82a8e21 "load data from master", packet_length=21)
    at sql_parse.cc:1083
#13 0x080fea18 in do_command(THD*) (thd=0x8295720) at sql_parse.cc:953
#14 0x080fe221 in handle_one_connection (arg=0x8295720) at sql_parse.cc:737
#15 0xb7e97dd6 in pthread_start_thread () from /lib/libpthread.so.0
#16 0xb7e97e60 in pthread_start_thread_event () from /lib/libpthread.so.0
#17 0xb7cf29aa in clone () from /lib/libc.so.6

How to repeat:
i restart my slave server and issue LOAD DATA FROM MASTER;
[11 Mar 2005 13:11] Guilhem Bichot
Hi,
could we get a copy of your master's ecom_transterminalnet.TMApplicationModules, please (take MySQL on your master down, copy the frm, MYD, MYI files of this table, add the master's and slave's configuration files, and upload a compressed archive of all this)?
You could upload it here in the "Files" section (there is a tickbox to make sure this file will be accessible only to MySQL employees).
This way, we could see if we can reproduce it.
Thanks.
[11 Mar 2005 13:48] Conrad Micallef
as requested

Attachment: tomysql.tgz (application/x-compressed, text), 4.29 KiB.

[11 Mar 2005 13:50] Conrad Micallef
if I can add something:
I did an ALTER TABLE on the masterand changed the longblob to mediumblob. After doing this i could issue LOAD DATA FROM MASTER on the slave without crashes, but still a number of "corrupt indexes" is reported.
Let meknow if I can upload anything else to help assist.

I also tried changing the slave to 4.1.10 and the problem repeats itself in exactly the same way
[11 Mar 2005 14:41] Guilhem Bichot
I could not make the slave issue any error or crash (I tried 4.0.24 and 4.1.10a, debug versions). But the table you uploaded is corrupted:
[guilhem 15:15 /tmp/d] /m/mysql-4.0/myisam/myisamchk TMApplicationModules.MYI
Checking MyISAM file: TMApplicationModules.MYI
Data records:       0   Deleted blocks:       0
- check file-size
myisamchk: warning: Size of datafile is: 2061            Should be: 0
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
myisamchk: error: Wrong bytesec: 31-139-8 at linkstart: 0
MyISAM-table 'TMApplicationModules.MYI' is corrupted
Fix it using switch "-r" or "-o"

CHECK TABLE showed the same kind of output.
I repaired the table:
[guilhem 15:16 /tmp/d] /m/mysql-4.0/myisam/myisamchk -r TMApplicationModules.MYI
- recovering (with sort) MyISAM-table 'TMApplicationModules.MYI'
Data records: 0
- Fixing index 1
Wrong bytesec:  31-139-  8 at          0; Skipped
Found block that points outside data file at 188
Found block that points outside data file at 852
Found block with too small length at 948; Skipped
Found block that points outside data file at 980
Found block that points outside data file at 1080
Found block that points outside data file at 1140
Found block that points outside data file at 1168
Found block with too small length at 1284; Skipped
Found block that points outside data file at 1328
Found block that points outside data file at 1388
Found block that points outside data file at 1400
Found block that points outside data file at 1448
Found block that points outside data file at 1572
Found block that points outside data file at 1576
Found block that points outside data file at 1604
- Fixing index 2

The table was empty ("0 records").

The MySQL slave still should not crash if it finds a corrupted table, but something is already really wrong on your master.
The table you provided is very small; in your slave's error log you had messages like
050311  9:56:45 Found block that points outside data file at 765612
which mean they are about a larger table.
You may have several tables corrupted on your master.
To check them, you can either:
- shutdown mysqld on the master, cd to the database directory and run myisamchk *.MYI.
- or run the SQL command CHECK TABLE on each table.
[11 Mar 2005 14:51] Conrad Micallef
I just did the following
stopped mysql on master
did myisamchk on all tables and it reported nothing wrong
started mysql on master

went on slave
executed
SLAVE STOP;
RESET SLAVE;
LOAD DATA FROM MASTER;

and this is the output i got:
050311 15:47:12 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:12 Run recovery again without -q
050311 15:47:12 Note: Retrying repair of: './ecom_transframework/serv_virtualpos
' with keycache
050311 15:47:12 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:12 Run recovery again without -q
050311 15:47:12 Note: Retrying repair of: './ecom_transframework/systemlocks' wi
th keycache
050311 15:47:12 Delete link points outside datafile at 124
050311 15:47:12 Note: Retrying repair of: './ecom_transterminalnet/ApplicationPr
ofiles' with keycache
050311 15:47:12 Delete link points outside datafile at 124
050311 15:47:12 Delete link points outside datafile at 496
050311 15:47:12 Delete link points outside datafile at 52
050311 15:47:12 Note: Retrying repair of: './ecom_transterminalnet/BinRanges' wi
th keycache
050311 15:47:12 Delete link points outside datafile at 52
050311 15:47:12 Delete link points outside datafile at 212
050311 15:47:12 Delete link points outside datafile at 100
050311 15:47:12 Note: Retrying repair of: './ecom_transterminalnet/CardProfiles'
 with keycache
050311 15:47:12 Delete link points outside datafile at 100
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/Ecom_BlackLis
t' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/Ecom_BlackLis
t_HostAccounts' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/HostApplicati
onProfiles' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/HostCardProfi
les' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/HostTerminalI
ds' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/Hosts' with k
eycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/RouterHosts'
with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/SitePrintLayo
utsTemp' with keycache
050311 15:47:13 Delete link points outside datafile at 0
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/TMApplication
s' with keycache
050311 15:47:13 Delete link points outside datafile at 0
050311 15:47:13 Delete link points outside datafile at 152
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/TXNRouterAcce
ss' with keycache
050311 15:47:13 Delete link points outside datafile at 152
050311 15:47:13 Delete link points outside datafile at 0
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/TXNRouterTerm
inal' with keycache
050311 15:47:13 Delete link points outside datafile at 0
050311 15:47:13 Found block that points outside data file at 472
050311 15:47:13 Found block that points outside data file at 752
050311 15:47:13 Found block that points outside data file at 1144
050311 15:47:13 Found block that points outside data file at 1928
050311 15:47:13 Found block with too small length at 1972; Skipped
050311 15:47:13 Found block with too small length at 2908; Skipped
050311 15:47:13 Found block with too small length at 2964; Skipped
050311 15:47:13 Found block that points outside data file at 3132
050311 15:47:13 Found block that points outside data file at 3332
050311 15:47:13 Found block that points outside data file at 5608
050311 15:47:13 Found block that points outside data file at 5648
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/TemplateAppli
cationProfiles' with keycache
050311 15:47:13 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:13 Run recovery again without -q
050311 15:47:13 Note: Retrying repair of: './ecom_transterminalnet/TerminalMessa
ges' with keycache
050311 15:47:17 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:17 Run recovery again without -q
050311 15:47:17 Note: Retrying repair of: './ecom_transterminalnet/security_mp_h
ostaccounts' with keycache
050311 15:47:17 Couldn't fix table with quick recovery: Found wrong number of de
leted records
050311 15:47:17 Run recovery again without -q
050311 15:47:17 Note: Retrying repair of: './test/table1' with keycache

i can't explain how running myisamchk on the data on the master gives no errors, and when i run slave replication i get all those errors. There is something definately happening here which I can't quite pinpoint.
[11 Mar 2005 14:59] Guilhem Bichot
Sorry, maybe I didn't understand: the tables you uploaded in the "Files" section, where they from the master or from the slave?
1) If from the slave, it would be nice if you could upload the master's.
2) If from the master, something is strange: I download them on my machine, and just run myisamchk on them and saw they are corrupted. So you must see the same thing running myisamchk on your master's table.
[11 Mar 2005 15:43] Conrad Micallef
what i sent you were master files

i repeat - executing myisamchk -seT *.MYI on the master yields no errors

I have now uploaded you the master table TXNRouterTerminals which is still giving warnings as per previous submission.
[12 Mar 2005 13:55] Guilhem Bichot
Using the files in tomysql.tgz:
[guilhem 14:53 /tmp/d] ll
total 28
-rw-r--r--  1 guilhem users 2811 2005-03-11 14:44 my.cnf.master
-rw-r--r--  1 guilhem users 2905 2005-03-11 14:45 my.cnf.slave
drwxr-xr-x  2 guilhem users  168 2005-03-11 15:15 save
-rw-r-----  1 guilhem users 8714 2005-03-12 14:53 TMApplicationModules.frm
-rw-r-----  1 guilhem users 2061 2005-03-12 14:53 TMApplicationModules.MYD
-rw-r-----  1 guilhem users 1024 2005-03-12 14:53 TMApplicationModules.MYI
[guilhem 14:54 /tmp/d] md5sum TM*
3f001c888c31360af6033010e76f2e86  TMApplicationModules.frm
448000d99a79384bdaed66cb660d1569  TMApplicationModules.MYD
668684c889c2f0170fa01bd684f74bf0  TMApplicationModules.MYI
[guilhem 14:54 /tmp/d] /m/mysql-4.0/myisam/myisamchk -seT *.MYI
myisamchk: MyISAM file TMApplicationModules.MYI
myisamchk: warning: Size of datafile is: 2061            Should be: 0
myisamchk: error: Wrong bytesec: 31-139-8 at linkstart: 0
MyISAM-table 'TMApplicationModules.MYI' is corrupted
Fix it using switch "-r" or "-o"

...
[14 Mar 2005 8:41] Conrad Micallef
can you please forget the tomysql.tgz and focus on the other ones ?

In the mean time i had an idea, can you double check my configuration - i have delayed key writes throughout - even on the master- does this affect replication and can it explain this behaviour ?
[14 Mar 2005 14:41] Guilhem Bichot
Hi,
1) using your TXNRouterTerminal.MYI, I was indeed able to repeat that myisamchk shows no error on master, but LOAD DATA FROM MASTER on slave shows:
050314 15:32:48 Delete link points outside datafile at 0
050314 15:32:48 Note: Retrying repair of: './test2/TXNRouterTerminal' with keycache
050314 15:32:48 Delete link points outside datafile at 0
050314 15:32:48 Found block that points outside data file at 472
050314 15:32:48 Found block that points outside data file at 752
050314 15:32:48 Found block that points outside data file at 1144
050314 15:32:48 Found block that points outside data file at 1928
050314 15:32:48 Found block with too small length at 1972; Skipped
050314 15:32:48 Found block with too small length at 2908; Skipped
050314 15:32:48 Found block with too small length at 2964; Skipped
050314 15:32:48 Found block that points outside data file at 3132
050314 15:32:48 Found block that points outside data file at 3332
050314 15:32:48 Found block that points outside data file at 5608
050314 15:32:48 Found block that points outside data file at 5648

2) you say you are using delayed key writes, but SHOW CREATE TABLE TXNRouterTerminal does not report the table as having delay_key_write=1. I saw no mention of delay_key_write in your my.cnf.* files, which means the global value of the delay_key_write variable is equal to its default value, which is "ON", not "ALL". Please, where did you tell MySQL to use delayed key writes? In CREATE TABLE? somewhere else?
By the way, if you use delayed key writes, our manual recommends to use --myisam-recover too.

I'll leave you answer on 2), and will work on 1) meanwhile.
Thanks.
[14 Mar 2005 14:59] Conrad Micallef
about point 1 - can you identify where the problem is ?

about point 2 - you are right - i had assumed that ON meant ALL.

Conrad
[14 Mar 2005 22:20] Guilhem Bichot
About point 1): yes I know where the messages in the error log come from.
Simply put: LOAD DATA FROM MASTER copies the .frm and .MYD to the slave, and does a REPAIR TABLE USE_FRM to recreate the MYI.
If you run REPAIR TABLE USE_FRM on the table files (frm+MYD+MYI) which you provided in d2.tgz, you'll see the same messages as in your slave's error log:
mysql> repair table TXNRouterTerminal use_frm;
+-------------------------+--------+----------+----------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                           |
+-------------------------+--------+----------+----------------------------------------------------+
| test2.TXNRouterTerminal | repair | info     | Delete link points outside datafile at 0           |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 472   |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 752   |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 1144  |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 1928  |
| test2.TXNRouterTerminal | repair | info     | Found block with too small length at 1972; Skipped |
| test2.TXNRouterTerminal | repair | info     | Found block with too small length at 2908; Skipped |
| test2.TXNRouterTerminal | repair | info     | Found block with too small length at 2964; Skipped |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 3132  |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 3332  |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 5608  |
| test2.TXNRouterTerminal | repair | info     | Found block that points outside data file at 5648  |
| test2.TXNRouterTerminal | repair | status   | OK                                                 |
+-------------------------+--------+----------+----------------------------------------------------+
13 rows in set (0.01 sec)

As you see the messages are tagged with "info", which means they are not even warnings, so nothing to worry about. And the last line is "OK" which means things went ok. 
So, don't worry, your LOAD DATA FROM MASTER works, table on slave has same content as on master, and is not corrupted. You can ignore those messages.
These messages are still quite superfluous and annoying; I should remove them by making LOAD DATA FROM MASTER use plain REPAIR TABLE instead of REPAIR TABLE USE_FRM. But that will not happen in 4.0, maybe even not in 4.1 (frozen versions).
[6 Jun 2006 0:30] Maarten Bremer
This problem still exists on version 5 of MySQL :(
[10 Aug 2006 23:56] Lars Thalmann
See comment in BUG#18822.
[16 Aug 2006 10:14] justin ux
I use mysql5.0.24 as slave , 4.0.18 as master. after I excute "load data from master" from slave, the data in tables are removed, tables became empty!
What should do to fix it?
In the error log :
060816 18:13:17 [ERROR] Found block that points outside data file at 414964
060816 18:13:17 [ERROR] Found block that points outside data file at 415064
060816 18:13:17 [ERROR] Found block with too small length at 415108; Skipped
060816 18:13:17 [ERROR] Found block that points outside data file at 415476
060816 18:13:17 [ERROR] Found block that points outside data file at 415692
060816 18:13:17 [ERROR] Found block with too small length at 415736; Skipped
060816 18:13:17 [ERROR] Found block that points outside data file at 415788
060816 18:13:17 [ERROR] Found block that points outside data file at 415876
060816 18:13:17 [ERROR] Found block that points outside data file at 415904
060816 18:13:17 [ERROR] Found block that points outside data file at 415964
060816 18:13:17 [ERROR] Found block that points outside data file at 416048
060816 18:13:17 [ERROR] Found block that points outside data file at 416128
060816 18:13:17 [ERROR] Found block with too small length at 416160; Skipped
060816 18:13:17 [ERROR] Found block that points outside data file at 416208
060816 18:13:17 [ERROR] Found block that points outside data file at 416304
[18 Aug 2006 8:54] Erik van Dam
I'm having the same issue using 4.1.12, the tables are empty when issued 'load data from master;'
[18 Aug 2006 23:36] 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:10] 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:28] 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 usign 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.