| 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: | |
| 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 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.
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;