Today I ran into a major MySQL problem that cost me a few hours of my day... one of my tables apparently became corrupted. Whenever I tried to alter the design I would get a message stating "1051 # The table doesn't exist".
I tried to delete, rename, alter, and everything else imaginable. I even resorted to deleting/dropping the entire database... same error message was thrown no matter which action I took. I also attempted to restore a previous backup, which was capable of restoring all tables except the one that was corrupted because it was incapable of overwriting the table because it would then cmplain that it already existed.
I tried all of these actions on three separate MySQL as administration applications (mysql command line console, MySQL Administrator, and Navicat for MySQL) and ALL threw the exact same error message.
I then restored my entire machine (not a full wipe, but a simple windows system restore) and.... nothing. Still the same issue.
To make a long story short, here's how I was able to solve the problem:
- Navigated to my WAMP folder here: C:\wamp\bin\mysql\mysql5.0.45\data\mydatabase_name
- Deleted the "corrupted_table_name.frm" file from that folder
- Restored my database from one of my previous backups (execute sql)
- Done... problem solved!
You likely will not run into this problem... corrupt tables are rare, however, the lesson to learn here is KEEP BACKUP snapshots of your databases. Yes, despite the fact my backups were not restoring, I nonetheless had a text-based document (the db backup) which I could refer to if all else failed (which it almost did). The solution I described above may come in handy some day, so I thought I would share it with the rest of the class.