How can a table be "crashed" on a product server?

Question

today my site was down all day, I checked logs (I manually log all bad SQL queries) and I scared to death:

SELECT MIN(DATEAT),MAX(DATEAT) FROM table = Table './table' is marked as crashed and last (automatic?) repair failed

this has never happened in 3 years. I repaired the table from phpMyAdmin, the result was:

db.table  repair  warning  Number of rows changed from 0 to 3279
db.table  repair  status  OK

it looks OK now but Im not relaxed - I might lost some datas? And how to prevent so that it wont happen again in the future? This table is an usual myIsam table.

last time it happened to me it was a hardware issue. yes you could of lost data, of course you have backups? — , Apr 14 '14 at 22:36
I have, but I dont know how to compare record (manual anyone?), it seems I didnt lose newest records. But I posted the result what it gave me, it said only "number of rows" were changed — John Smith, Apr 14 '14 at 22:39
you can restore the back up with a different name and then write some code to compare the two. depending on how this is hosted i would be calling the host up proto — , Apr 14 '14 at 22:41
http://dba.stackexchange.com may be a better palce to ask this — , Apr 14 '14 at 22:45
You can't guarantee that it won't happen again, wait long enough and it will.... you just take steps to make it easier to recover, such as regular backups (with periodic tests to ensure you can do a recovery from them); and basic housekeeping to reduce the likelihood/frequency of it happening again — Mark Baker, Apr 14 '14 at 23:01
There are only 2 kinds of people: those who take backups, and those who've never had a disk crash — Mark Baker, Apr 14 '14 at 23:02
it should be "and those who have never *yet* had a disk crash" — , Apr 14 '14 at 23:05

score 2 · Answer 1 · edited May 23 '17 at 12:27

MyISAM is notorious for corrupting data during a crash. It's a good reason to convert all your important tables to InnoDB. See also MyISAM versus InnoDB.

The only MyISAM tables you keep should be those that you can recreate easily from data that is safely in InnoDB tables (some people do this to use MyISAM's fulltext indexes, but keep the canonical data in InnoDB).

For tools to compare data, I recommend pt-table-sync --print.

See Compare two MySQL databases for other options.

How can a table be "crashed" on a product server?

1 Answers1