CODEWITHSUNDEEP
mysqlsqllinux
Kyle S
Kyle S

Reputation: 134

MySQL Table Corrupted

We have a website that has a table of tax rates that somehow got corrupted one day.

I tried selecting * from theTable but it literally took hours to yield nothing.

I tried truncating and dropping the table. Produced the same results as the select. Eventually the actions stopped. So, I ran a 

myisamchk -r theTable.MYI

Repaired looks like it went okay, but I tried selecting * from it again and still producing the same results.

Engine is MySQL

It would be easy to reproduce the table, can I just delete theTable.* (frm, MYD, MYI)?

Or if there is a better approach to what I'm doing.

Upvotes: 1

Views: 706

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

Yes, you can delete the .FRM, .MYD, .MYI files to remove a MyISAM table. This is what happens when you DROP TABLE.

MyISAM tables are infamous for getting corrupted. But the fact that you got repeated problems after recreating the table makes me wonder if you have a failing hard drive. It would be worth testing the health of your hard drive.

You should also switch to use InnoDB instead of MyISAM. InnoDB has a lot of protections against corruption, including page checksums, synchronous disk writes, crash recovery, etc. MyISAM is being slowly phased out and is receiving no fixes from MySQL developers. InnoDB is now the default storage engine as of MySQL 5.5 (circa 2010), and it will continue to be the focus of future development.

Note that with InnoDB, you cannot simply delete files to achieve dropping a table like you could with MyISAM. You must use DROP TABLE so InnoDB synchronizes that drop with its internal data dictionary.

Upvotes: 1

Related Questions