Archive Table Corrupt

An ARCHIVE table got corrupted in my production.

开发者_JAVA百科

I tried

REPAIR TABLE TBL_NAME;

It wasn't able to repair the table. Does only MyISAM table support repairing?

I dropped the table, recreated it and then restored it from the dump I already had.

Q1: What could have been the better option to handle this scenario?

Q2: Why databases/tables getting corrupted so often?

Q3: What is the best that we could do to prevent tables from getting corrupt?

---

Q1: What could have been the better option to handle this scenario?

Given the circumstances I think that what you did was the best solution. There is an ARCHIVE engine recovery tool called archive_reader that might have been able to help you recover rows if you'd not had a backup

The fact that you had backups is good and saved you here. If you want to be able to perform a full recovery it could be worth enabling binary logging or adding a replicated slave server.

Q2: Why databases/tables getting corrupted so often?

In normal operation they shouldn't be. I would look in your MySQL error log to see if there are any error messages that corresponded to the time of the table crash. Disk or other problems on the server could make it more likely to corrupt tables. Perhaps you've found a bug in the ARCHIVE engine?

Q3: What is the best that we could do to prevent tables from getting corrupt?

As mentioned in Q2 have a good look for error messages. If you find that you can predictably replicate crashing a table be sure to file a MySQL bug report.

---

FOR MyISAM table:-

1) Identify all corrupted tables using myisamchk

2) Repair the corrupted table using myisamchk -r

If the tables are still getting used by your application and other tables.To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory

You can also Perform check and repair together for entire MySQL database Example : myisamchk --silent --force --fast --update-state .....*.MYI