Mysql – Automatic Recovery of MyISAM table

myisamMySQL

I have an application in C language which inserts the report data in MyISAM table by using mysql_stmt_execute() function.
Due to load on the table, it gets crashed.

For auto repair of crashed table, I have added following option in /etc/my.cnf file

myisam-recover-options=FORCE,BACKUP

When the table gets into a crashed state, then mysql_stmt_execute() function fails and I can't insert the reports. As I have specified recovery options, expected result should be, table gets repaired and application should work fine.

QUESTIONS

How to repair the table automatically when it gets crashed ?
Why myisam-recover-option can't recover the table when query is executed ?

Best Answer

myisam_recover_options is not a dynamic variable. It is among the startup options for MyISAM.

If you have set this option in my.cnf, mysqld must be restarted

Please restart mysqld with

# service mysql restart

or if you have Windows

C:\> net stop mysql
C:\> net start mysql

You should try switching your table to InnoDB and make your table crash-safe.

Related Solutions

MySQL Table not repairing

could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

Absolutely. If you kill -9 mysqld and you're using MyISAM tables, you're almost guaranteed to crash some tables unless the server is really, really, really not busy... although the definition of a "crashed" table isn't as catastrophic as it sounds in all cases... it only means, at the simplest level, that the table is not in the state that it should have been in had the server closed it properly.

Is there anther way to fix/repair/recover that table?

Have you been through the steps covered in How to Repair MyISAM Tables?

Also, consider these things, which may give you some ideas that could get you on a faster path to recovery if they are not familiar concepts:

With MyISAM tables, everything is in the files. You could literally take your wp_options.* files (assuming for the sake of discussion that they were not crashed), copy them into a database directory on another MySQL server, SHOW TABLES and you would find that your table is visible and available on that other server. For future recovery, if you had a slave server, you could shut it down and copy this table's files directly from there back to the master and be back online. (This is only true for MyISAM and ARCHIVE, not InnoDB, but by contrast, InnoDB tables are extremely immune from this sort of corruption due to a hard/forced shutdown).
The myisamchk utility is not technically a part of the actual MySQL Server itself -- it is a standalone utility built from many of the same underlying libraries, but can be used on a different computer, even without MySQL server installed. The myisamchk utility has various capabilities and levels of aggression.
You could, therefore, stop MySQL, copy the table files to a different machine -- one with a nice speedy SSD, for example, could make a big difference in recovery time -- and repair the files with myisamchk, then (with the MySQL server stopped again) copy the table files back. If you have other things running on your MySQL server, you could bring those other things back online while not bogging the server down with this repair.

By contrast, mysqlcheck is nothing but a simple program that connects to your MySQL server using the client libraries and...

...uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

...so while the two utilities use many of the same methods to repair the tables, they are also fundamentally very different.

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 !!!

QUESTIONS

Best Answer

Related Solutions

MySQL Table not repairing

MySQL Tables Require Daily Repairs – Server, Table or HD

Related Question