Thesql Wrong bytesec: 0-0-0 at linkstart: 0

corruptionmyisamMySQLrecovery

I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns:

> +-----------------+-------+----------+----------------------------------- |

> clldata.mytable | check | warning  | Table is marked as crashed  

> clldata.mytable | check | warning  | Size of datafile is: 4654864 Should be: 4602520

> clldata.mytable | check | error    | Wrong bytesec: 0-0-0 at linkstart: 0

> clldata.mytable |
> check | error    | Corrupt                                            
> |
> +-----------------+-------+----------+-------------------------------------------------------+

4 rows in set (0.01 sec)

Running REPAIR TABLE mytable results in truncating about 90% of the rows that were originally in the table (and the size of the .MYD file also shrinks about 90%). And most of the remaining rows in mytable are clearly actually from one of the other corrupted tables.

Is there any hope of recovering data from such a table?

Best Answer

MY CONDOLENCES

MyISAM only caches index pages in the MyISAM Key Buffer. MyISAM never caches data. Notice how the messages from CHECK TABLE is only mentioning the datafile (that's the .MYD file) .

Since MyISAM data is fully reliant on the OS, rows of data are cached only in the OS. The MyISAM tables that are corrupt had open file handles when the crash happened (doesn't matter if mysqld crashed or the OS that crashed)

Recovery of the data is not possible.

FUTURE CONSIDERATIONS

Switching to InnoDB

In the future, please use InnoDB for all tables. InnoDB has a higher tolerance for crashes because it has transaction logs and double write buffers to recover data that was committed but not yet written as well as rollback segments and undo logs for rolling back uncommitted transactions.

Still Want to Use MyISAM

You should backups of the MyISAMs regularly
You should keep binary logs enabled, even if you don't use Replication. That way, you could use backups of the MyISAM tables and use the binary logs to replay INSERTs, UPDATEs, and DELETEs up to the point before the crash.
You should setup MySQL Repication so that the Slaves have good copies of the same tables

Give it a try!!!

Related Solutions

MySQL Tables Require Daily Repairs – Server, Table or HD

You should get away from MyISAM as soon as possible. Why ???

MyISAM caches only index pages in the Key Buffer
InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool

I have mentioned this many times before

May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
Apr 08, 2013 : If MySQL isn't caching, then what is?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?

This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed. A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:

Switch to InnoDB
Increase installed RAM
Configure as much buffer pool as possible

Give it a Try !!!

Mysql – Check for table corruption

SHOW TABLE STATUS;
and then run 
show warnings;

will show you what all tables are corrupted.