Tag Archives: crash

Repair a MySQL table

databases, , ,

I'm running mysql 5.0.23 on a FreeBSD server. I have several databases there and a few phpbb forums.

I noticed the tables used for searching the forums ( phpbb_search_wordlist and phpbb_search_wordmatch ) crash quite a lot lately from various reasons but mainly because of hardware problems ( like lack of power 🙂 ). Nothing unusual here so far. When I notice this I go into mysql and do a repair like :

  1.  

But this time I got this answer: " Table is already up to date". So MySQL client tells me the table is fine but in phpbb when I try to search something I get this error message: "SQL Error : 145 Table './simscripts_phpbb/phpbb_search_wordlist' is marked as crashed and should be repaired". I try to read the table from the mysql client and I get a similar message.

Going through the MySQL documentation I find some extra parameters i can pass to repair table. And use_frm seems to be the one that fixes the problem. This parameter should recreate the indexes by looking at the .frm file ( the structure definition of the table )

  1.  

did the job and search in phpbb is back online.

Unfortunately it seem that the table was so badly damaged that no rows could be recovered so the repair did was to recreate the database structure. Good thing I had a backup!

OR maybe it was just because my table was created on an older mysql version and as the documentation says:

Caution

Do not use USE_FRM if your table was created by a different version of the MySQL server than the one you are currently running. Doing so risks the loss of all rows in the table.

I don't know if this is a bug only in the version I run, but I think MySQL should really look at indexes and if they need to be recreated it should just do it automatically or atleast tell you the table is not ok instead of lying like that.

Update:

If you have shell access to your server with root or mysql user permissions you can go in the mysql data directory ( usually /var/lib/mysql or /var/db/mysql on freebsd ) go into your broken database directory and use myisamchk to repair the table without the risk of losing all the rows in it :

  1.