Hi, The only other method I've used is documented in the mysql manual, method 3, this worked fine and brought the table back to life. If your data file is corrupt though, that may mean time to look at your backup. It seems that myisamchk will only really work on the indexes not the data (I may well be wrong, somebody please correct me if I am).
http://dev.mysql.com/doc/mysql/en/Repair.html Stage 3: Difficult repair You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows: 1. Move the data file to some safe place. 2. Use the table description file to create new (empty) data and index files: shell> mysql db_name mysql> SET AUTOCOMMIT=1; mysql> TRUNCATE TABLE tbl_name; mysql> quit If your version of MySQL doesn't have TRUNCATE TABLE, use DELETE FROM tbl_name instead. 3. Copy the old data file back onto the newly created data file. (Don't just move the old file back onto the new file; you want to retain a copy in case something goes wrong.) Go back to Stage 2. myisamchk -r -q should work now. (This shouldn't be an endless loop.) As of MySQL 4.0.2, you can also use REPAIR TABLE tbl_name USE_FRM, which performs the whole procedure automatically. Stage 4: Very difficult repair You should reach this stage only if the `.frm' description file has also crashed. That should never happen, because the description file isn't changed after the table is created: 1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r. 2. If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, then move the `.frm' description and `.MYI' index files from the other database to your crashed database. This gives you new description and index files, but leaves the `.MYD' data file alone. Go back to Stage 2 and attempt to reconstruct the index file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -----Original Message----- From: J S [mailto:[EMAIL PROTECTED] Sent: Tuesday, 18 January 2005 6:32 AM To: mysql@lists.mysql.com Subject: RE: lost data Just reposting in case anyone missed it! Or have I asked a dumb question?! >Hi, > >I've run a safe recover on a table after I had to recreate the index file: > ># myisamchk --tmpdir=/proxydb/mysql/tmp --safe-recover internet_usage >- recovering (with keycache) MyISAM-table 'internet_usage' >Data records: 290804216 >Data records: 519541696 ># > >After that I ran a query on the table, but the data seems to be corrupted. >I tried flushing the tables but still got bad data despite the checks below >which show everything looks fine: > >mysql> check table internet_usage; >+---------------------------+-------+----------+----------+ >| Table | Op | Msg_type | Msg_text | >+---------------------------+-------+----------+----------+ >| proxy_logs.internet_usage | check | status | OK | >+---------------------------+-------+----------+----------+ >1 row in set (35 min 34.97 sec) > ># myisamchk internet_usage >Checking MyISAM file: internet_usage >Data records: 519541696 Deleted blocks: 0 >- check file-size >- check record delete-chain >- check key delete-chain >- check index reference >- check data record references index: 1 > > ># myisamchk -dvv internet_usage > >MyISAM file: internet_usage >Record format: Fixed length >Character set: latin1 (8) >File-version: 1 >Creation time: 2004-10-27 16:49:48 >Recover time: 2005-01-16 7:14:48 >Status: checked >Data records: 519541696 Deleted blocks: 0 >Datafile parts: 519541696 Deleted data: 0 >Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 >Datafile length: 16625334272 Keyfile length: 5006235648 >Max datafile length: 137438953470 Max keyfile length: 4398046510079 >Recordlength: 32 > >table description: >Key Start Len Index Type Rec/key Root Bloc >1 2 4 multip. unsigned long 0 445844480 > >Field Start Length Nullpos Nullbit Type >1 1 1 >2 2 4 >3 6 4 >4 10 4 >5 14 4 >6 18 2 1 2 >7 20 4 1 4 >8 24 4 1 8 >9 28 1 >10 29 1 >11 30 1 >12 31 2 > ># ls -l internet* >-rw-rw---- 1 mysql mysql 16625334272 Jan 16 07:14 >internet_usage.MYD >-rw-rw---- 1 mysql mysql 5006235648 Jan 17 09:05 internet_usage.MYI >-rw-rw---- 1 mysql mysql 8856 Jul 09 2004 internet_usage.frm > >mysql> show create table internet_usage \g > >| internet_usage | CREATE TABLE `internet_usage` ( > `uid` int(10) unsigned NOT NULL default '0', > `time` timestamp(14) NOT NULL, > `ip` int(10) unsigned NOT NULL default '0', > `urlid` int(10) unsigned NOT NULL default '0', > `timetaken` smallint(5) unsigned default '0', > `cs_size` int(10) unsigned default '0', > `sc_size` int(10) unsigned default '0', > `method_ID` tinyint(3) unsigned NOT NULL default '0', > `action_ID` tinyint(3) unsigned NOT NULL default '0', > `virus_ID` tinyint(3) unsigned NOT NULL default '0', > `useragent_ID` smallint(5) unsigned NOT NULL default '0', > KEY `uid` (`uid`) >) TYPE=MyISAM MAX_ROWS=4294967295 | > >1 row in set (0.00 sec) > >mysql> > > >Could anyone give me some advice on what else I can try to recover the data >please? > >Thanks, > >JS. > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
