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 :
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 )
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_FRMif 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 :
This post saved my life. [Ironic you posted it on my birthday!]
My server is in [was] in the middle of an upgrade from mySql 4 to mySQL 5. I like that you offer your own experience. I tried your suggestion and was successful. Now if I can just keep that table [wp_posts] from crashing everytime i try to now alter a post… hopefully the server’s being worked on and it has to do with the upgrade though my mySQL knowledge is limited.
Thanks for this. I Stumble thumbed it up! I’ll be back! Fabulous.
I’m glad to hear the post was helpful. Which suggestion did you try the one with USE_FRM or the one with myisamchk ?
I used myisamchk because I did read time and time again from various tech -related resources that to use_frm was a baddie if you’d previously built your tables with one version and then got upgraded – which was my case.
In my case I was built and contrived on mySQL 4 and then host went to v. 5. There was no issue until an edit. When I edited a page…it was like the WordPress ‘E bomb’ exploded.
I utilized your suggestion and all was well.
Then I went back to edit again and again…it happened. This time no commands were working. I restored a backup table and that was a success. Currently my server tech’s are looking into it. My version of WP is so old my grandmother was wearing black and white clothes when it came out…so I am thinking it’s an incompatibility with mySQL 5 that’s causing the original crashes.
I’ll keep you posted if you like. If other people find this nice post under similar circumstances they may be interested to hear any solution I find?
I never heard of a table crash when editing a post before. what version are you using exactly? Maybe you could copy the same blog but create a new database and see if it still crashes. If it does then it’s most likely a conflict between your mysql version and your wordpress version. If not then it may just be a problem with the database you repaired, in which case I would export it as SQL, delete it and then recreate it from SQL. Btw does this still happens after you restored the backup table?
I never heard of a table crash when editing a post before.
Tell me about it!
Let me give you the specs. WP 2.0.6 [Don’t laugh! :P] I’ll have to see if it happens again after restoring the table. It wasn’t a “blog post” per se…but was a page off the parent. [This page: Resources.]
I needed to update some information. It may turn out whacked because I’m going in to test editing it to see if it yields a crash. If you watch you may be able to see it. …oh and of course….when it crashes [although it references only the one table as being crashed and in need of repair…the entire site goes down and I get 404’s sitewide.
http://samsara.ihostyou.com
Alright. You were right!
Apparently when I repaired or restored the table…it really repaired the issue! I went back and piece by piece began editing pages. I was nervous and made a backup beforehand. It took!
I really do need to edit the pages also because apparently upon repair or the restore [I am not sure which] it added a lot of ? [question marks] to random areas. I corrected the most recent posts of this.
Thanks for all of your help. And now I am not scared to edit my posts thanks to your question. LoL…Still. I need to get my blog moved to the domain I got and update my WordPress.
I am also now a member of your MyBlogLog community so I can
stalkfollow your blog. 🙂I’m glad to hear you solved the problem and thanks for following 🙂
What a life saver !!!
This seems to be an issue when moving from V4-5 with large tables (I only had the issue on one table our of 40 I was migrating).
I was getting the “table crashed error” but the “up to date” message when trying to do a MYSQL repair or a mysqlcheck on the table. Based on the post, I ran the myisamchk (rather than USE_FRM on the repair cmd)… sweet !
Thank you vey much.
You helped me twice, yesterday for femiboard and today for femiforum
New release for my server + disk full have make big problems on my websites.
You saved mu life too !!!
Thank you so much!!!