How To Quickie: Repair MySQL Tables

May 31, 2006 · 64 comments

After a long, hard day of fetching and editing tables, MySQL can become overburdened and create overhead. This can be almost directly compared to how defragmentation occurs on a hard drive. For example, something needs to be replaced in one location but there is not enough space so part of it goes here and part goes there; discontinuous. Over time, overhead for very active tables in your database can reach high levels and the result will be something like what happened to me in March. You can ensure a healthy database by following this quick fix every once in a while.

1) Locate and login to your phpMyAdmin client.
2) Select the database and click on the name of the database (circled).

3) You should see all of the tables for your database.

4) Select the checkboxes for each table that has an overhead value.

This was only for example. Tables with an overhead value under a kilobyte aren’t really anything to worry about.

5) Go to the drop-down menu with selected on the bottom and select repair.
6) If everything works out, you will see the something like the image below.

7) Clicking on the database name again should show the screenshot below, with no overhead for any of the tables. That’s it!

Tagged as: How To's, Software

Like this post? Here are more just like it.

Get PaulStamatiou.com updates via RSS or Email.

Show & Tell

Tweet This

Stumble This

{ 17 trackbacks }

WordPress Database Error: Got error 28 from storage engine » The Affiliate Marketing Blog: November 11, 2006 at 5:18 am
MySQL veritabanÄ± tablolarÄ±nÄ±n onarÄ±lmasÄ± - GÃ¼nlÃ¼k HaftalÄ±k AylÄ±k: March 23, 2007 at 9:15 am
MySQL veritabanÄ± tablolarÄ±nÄ±n onarÄ±lmasÄ± | Her Yol PARÄ°S ! Benden Korkun Diyen Herkes KorkaktÄ±r Rap GÃ¶kgÃ¼rÃ¼ltÃ¼lÃ¼ SaÄŸnaktÄ±r !: March 25, 2007 at 2:46 pm
WordPress database error: [Got error 28 from storage engine] | Wahlau .NET: May 3, 2007 at 11:43 pm
Repairing MySQL Tables: June 29, 2007 at 11:58 am
Optimize, Repair And Backup Your Database Within Wordpress | Tubetorial: September 26, 2007 at 2:46 pm
Error 28 en MySQL at El MÃ³dem: October 12, 2007 at 10:46 am
Database Restored: October 19, 2007 at 1:07 am
4server » Tabelas: October 27, 2007 at 3:11 pm
Database Crash — Full Time in NM: November 15, 2007 at 1:15 pm
New year housekeeping at Blogjer is about blogger: January 6, 2008 at 11:13 am
Mehrere MySQL Tabellen gleichzeitig reparieren | adminlife.net: March 20, 2008 at 7:34 am
נשבר לי הוורדפרס, מה עושים? » ITbananas: June 18, 2008 at 7:16 pm
WordPress database error: Got error 122 from storage engine | Chiropractic Blogs: September 10, 2008 at 10:51 pm
InformateGeek - WordPress database error: [Got error 28 from storage engine]: September 18, 2008 at 5:35 pm
MySQL veritabanı tablolarının onarılması | Utku Çakır: April 25, 2009 at 10:00 am
Repair a crashed mysql table « petersteinberger.com: May 3, 2009 at 7:27 am

{ 47 comments… read them below or add one }

1 Ben May 31, 2006 at 2:18 am: Thanks, nice tip
Ben

Reply More from author
2 titanium_geek May 31, 2006 at 3:39 am: cool!

thanks for the quickies- so easy to understand, I could do it in my sleep! Now, if only I could get myself to “sleep surf”….

Reply More from author
3 Cory May 31, 2006 at 7:47 am: thats awesome

Reply More from author
4 maxpower May 31, 2006 at 8:55 am: Apparenltly, I had a lot of overhead. Now its all gone, thanks!

Reply More from author
5 Ben Gray May 31, 2006 at 9:06 am: Thanks for this tut, Paul! I’m usually wary about tinkering with stuff on my server because I’m still a relative n00b at it. This helps me out a lot.

Reply More from author
6 franky June 2, 2006 at 7:49 pm: And why not optimize all the tables at the same same time? ;-)

Check all —> optimize. Can/will quite often reduce the size of the database quite impressively (up to 10% or even more).

Reply More from author
7 Paul Stamatiou June 4, 2006 at 3:56 am: Yeah what franky said. =D

Reply More from author
8 Nipon June 15, 2006 at 12:25 am: Thanx a lot for the tut.

Just a few days ago, my database went kaput. :( I restored from a backup but that was a few days old — so I lost some comments.

Reply More from author
9 Ricardo October 4, 2006 at 4:13 pm: If my database is not open how i can fix the tables?. the phpmyadmin does not open.

Thanks

Reply More from author
10 richard December 21, 2006 at 6:53 pm: Good advice, thanks a lot. I’ve been creating, changing, deleting a lot of tables. Was beginning to wonder what this overhead deal I was seeing was about. Cleaned it all up nicely. Happiness abounds….

Reply More from author
11 chaoskaizer January 6, 2007 at 2:54 am: tips, dont over optimize ur table too often, unless you want to increase the table lock.

Reply More from author
12 Glen February 27, 2007 at 4:01 am: Thanks a lot !!!

Reply
13 Cameron B March 21, 2007 at 2:45 am: Hey thanks very very much. That would have taken me forever to figure out.

Reply More from author
14 toremygg May 15, 2007 at 3:37 am: Thanks! I can’t believe how easy that was… I was afraid I’d have to rebuild my whole DB… Ouch. And then all I needed to to was select Repair. The wonders of modern technology. At least now I know to check my DBs from time to time. :)

Reply More from author
15 Phil May 28, 2007 at 12:39 pm: Thanks for taking the trouble to stick it on the web. It worked perfectly.

Reply
16 nowax June 29, 2007 at 11:41 am: Glad to find this at the top of google for repair mysql table

good lookin out

Reply More from author
17 Eriko July 30, 2007 at 11:44 am: Thanks a lot !!!

Reply
18 Sy Ali August 28, 2007 at 4:06 pm: Thanks for this post. phpMyAdmin is so convoluted that even though I’ve done this before I couldn’t remember how to do it a second time. I’m glad I found this.

Reply More from author
19 Ryan September 29, 2007 at 8:17 pm: Thanks man. I was wondering what was happening to my mysql database. Saved my forum! :]

Reply More from author
20 HART (1-800-HART) October 29, 2007 at 8:02 am: Hi Paul. Last night I received this error:

MySQL said:
#1194 – Table ‘wp_options’ is marked as crashed and should be repaired

I basically panic’ed and turned some more of my hair into grey hair

But – I found this post and you literally saved me from doing something silly, like restoring a backup, or backstepping to the taped backups, etc … all I needed to do was repair the affected table like you suggested and everything worked out pretty good.

Thanks for the tip. It’s timeless.

Reply More from author
21 George November 18, 2007 at 12:11 pm: Thanks for your help, Paul. I have been running around like a crazy man for the past two (2) days trying to resolve this error before my class presentation day. Finally, i came across your post and that helped me to resolved my problem. Kudos to you!!!!.

Reply
22 LiNTEK January 28, 2008 at 11:35 pm: This tip is very useful for MySQL admins…..

Reply More from author
23 Stephane February 22, 2008 at 9:19 am: I am fairly new at these thing..my site whent down and did not know what to to!!I found your post and everything is good now!!!!

THANK YOU VERY VERY VERY MUCH!!!!!

Reply
24 Spielen March 10, 2008 at 1:47 pm: Thx for tips

Reply More from author
25 MattG March 25, 2008 at 4:38 pm: thanks, you just saved my blog!
the wp_options table had crashed..
the blog was showing the WordPress install page and erroring out if I ran the install.
your fix sorted it out!!
:)
Matt Garrett

Reply More from author
26 Sherine April 2, 2008 at 8:51 am: Really useful article!! Thank you

Reply
27 Mike April 27, 2008 at 8:07 pm: I found a couple quick mysqlcheck commands that optimize and repair MySQL databases on the following site: http://tuxrx.org/content/repairing-and-optimizing-mysql-databases

Reply
28 Tim May 19, 2008 at 3:38 pm: Good article. My problem is related, I suspect, but different.

All the data on my damaged table seems to have disappeared into the overhead area. When I browse the table, no records show, but autoindex shows 265. The material was being properly recorded until some unknown recent event. The overhead item shows 14,625 b in it.

1) Is there any way to recover the data that is in the overhead section? Optimize will just erase it. Can “repair” be used to fix it?

2) Any ideas as to what might have caused this problem? (All the other tables on the database seem to be working properly, and the data entry person doesn’t remember anything unusual happening.)

Thanks for any help you can offer.

Tim

Reply More from author
29 TuTay May 23, 2008 at 2:17 pm: Thanks. My Search Index in Phpbb3 is working now !

Reply More from author
30 Anjar Priandoyo May 29, 2008 at 6:57 am: Thanks for great tips, i’m googling and found this information really useful. My website based on drupal and facing this problem.

Reply More from author
31 Phill August 26, 2008 at 6:50 am: you just saved me pulling my hair out!!!!

Cheers

Reply More from author
32 andy September 11, 2008 at 5:33 pm: Hi,
How long does REPAIR table takes?

I have it running right now for more than 2 hours.

Query | 9724 | Repair by sorting | REPAIR TABLE BettingOfferArchive QUICK

these are the files sizes. Please comment.

-rw-rw—- 1 mysql mysql 1158317853 Sep 11 08:44 BettingOfferArchive.MYD
-rw-rw—- 1 mysql mysql 1102354432 Sep 12 00:40 BettingOfferArchive.MYI
-rw-r—– 1 root root 248512512 Sep 12 02:39 BettingOfferArchive.TMD
-rw-rw—- 1 mysql mysql 8688 May 27 22:14 BettingOfferArchive.frm

Reply
33 andy September 11, 2008 at 5:39 pm: It has been running for more than 2 hours. Why?

10434 | Repair by sorting | REPAIR TABLE BettingOfferArchive QUICK

——-

-rw-rw—- 1 mysql mysql 1158317853 Sep 11 08:44 BettingOfferArchive.MYD
-rw-rw—- 1 mysql mysql 1102354432 Sep 12 00:40 BettingOfferArchive.MYI
-rw-r—– 1 root root 248512512 Sep 12 02:39 BettingOfferArchive.TMD
-rw-rw—- 1 mysql mysql 8688 May 27 22:14 BettingOfferArchive.frm

Reply
34 SMS Admin September 21, 2008 at 5:24 pm: Found your site from Google while I was searching on how to Optimize my wordpress database. Thanks to your post, I have reduced my headache to minimal. :)

Reply More from author
35 Colin Jensen September 26, 2008 at 9:24 am: Thanks, Saved me from my morning warning:
“/phpbb_sessions’ is marked as crashed and should be repaired”

Reply More from author
36 Andre October 17, 2008 at 10:15 am: Worked for me. Thank you.

Reply
37 Lindsey Kuper December 16, 2008 at 12:01 am: Thanks for the tip!

Reply More from author
38 dutchmarbel December 16, 2008 at 1:16 pm: Thank you very much for this clear tutorial!

Reply More from author
39 pavan January 5, 2009 at 2:09 am: Hi all,
I have got this error

table “xyz” is marked as crashed and last (automatic?) repair failed..

What to do..

Thx

Reply
40 JBucknoff January 10, 2009 at 11:21 pm: I’m having a problem displaying more than one category. From some of the other message boards I’ve read, I can see that other people are having a similar problem.

I go to the admin Dashboard and open the Categories page. I can add new categories and sub-categories here, but they never show up on the blog page. I am able to edit the original (”uncategorized”) category to another name, but that is all.

I “repaired” all of my MySQL tables, as suggested, but this didn’t help. By the way, I don’t see a wp_category table in my database (described in some older documentation), but I do see the name of my (missing) category in the wp_terms table as well as the description of the (missing) category in the wp_term_taxonomy table.

These are the 10 tables in my MySQL database:

wp_comments
wp_links
wp_ptions
wp_postmeta
wp_post
wp_terms
wp_termwp_relationships
wp_termwp_taxonomy
wp_metadata
wp_users

I think I’m using 2.7. I installed it through the GoDaddy interface.

Can anyone help me? Thanks !!!

Jerry Bucknoff
PM Best Practices

Reply More from author
41 LRMajer January 19, 2009 at 4:30 am: Thanks Paul.
You’re tip was exactly what I needed to repair a crashed table.
My next option was to go to a back up of the table and loose a weeks worth of work.

Thanks again.
LR

Reply
42 Nees February 2, 2009 at 11:53 am: Thanks.
This has solved so many problems that I didn’t understand.

Thanks

Reply More from author
43 Jon February 5, 2009 at 4:43 pm: Lifesaver… love the quick tips you’ve written – brilliant – thank you!

Reply
44 Kabyware February 16, 2009 at 2:35 pm: You are a LIFESAVER!
Brilliant and I did not have to spend the whole day
trying to fix my database. THANKS A LOT!

Reply More from author
45 Diane Faye Zerr March 5, 2009 at 12:16 pm: I followed your instructions but have many that say they’ve “been marked as crashed and should be repaired”. Is there anything else I can do?

Here’s a screen shot of the error messages:
Screen Shot

Reply More from author
46 Adriana March 25, 2009 at 1:28 pm: Great tip!

Reply
47 Thejesh GN April 30, 2009 at 12:57 am: thanks a lot for the tutorial. It saved my day.

Reply More from author