Nightmare with Wordpress, Crashed Corrupt Table and Fixing The Problem

Nightmare with Wordpress, Crashed Corrupt Table and Fixing The Problem

By Strictly Software

This has been a real nightmare day for me!

You can read all about it on the Wordpress forum here: Suddenly Templates Not Working. (not that I actually got any help from anyone!)

I first noticed I had a problem on one of my Wordpress sites when I went to one of my pages that uses a custom page template and saw that it was totally blank. It should have been showing a feed with a Twitter widget in the sidebar and special content in the middle.

I edited the page and saw that the template was set to "Default Template". I set it to the correct template and tried saving it only for the page to reload with the Default Template still selected!

I turned on the WP_DEBUG constant and was met with a load of MySQL errors that all indicated that my wp_postmeta table was corrupt e.g

WordPress database error: [Incorrect file format 'wp_postmeta']
INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES (7381,'_edit_last','1')

I then ran a REPAIR (FULL) on the table only for it come back ASAP with error messages such as:

.wp_postmeta check Error Incorrect file format 'wp_postmeta'
.wp_postmeta check error Corrupt

If a REPAIR or OPTIMIZE wouldn't fix it I knew I was in the shit!

I tried searching the web but I couldn't find anything of use.

I was using the WP-DBManager plugin to create backups for me on this site but for some reason it had stopped working after my recent upgrade to WP 3.8. By the way, the amount of problems I have had since upgrading to this version would fill a book by now!

Anyway the last manual database update was done just before Christmas so it wasn't too far back and anyway the table only holds SEO META, Flags set by plugins and mostly guff that you don't really need anyway.

All my tags, categories, pictures and so on were still the site so it wasn't too major a problem except I hate BUGS! Especially ones I can't fix!

Therefore I created a new empty table with the same structure, indexes and settings as the existing wp_postmeta table and called it wp_postmetaNEW.

I turned off APACHE and then dropped the old table. I renamed my new table to wp_postmeta (I could have truncated the old one by the way) and checked it was working.

I then restored my old backup to a new database on the server called RESTORE.

I checked the wp_postmeta table wasn't corrupt first and then ran a statement (whilst logged in as root to Navicat) to copy across all the meta data I did have from the old database to my new table e.g

INSERT INTO MYDB.wp_postmeta
(post_id,meta_key,meta_value)
SELECT post_id,meta_key,meta_value
FROM RESTORE.wp_postmeta

This took a long time!

Especially for only 94,310 records.

I was waiting over 30 minutes with the server monitor ticking away saying it was repairing the table.

Then I noticed in my Putty console with a TOP command that no MYSQL process was actually running!

This made me think the process had crashed and NAVICAT wasn't telling me the truth.

By the way I have had this happen many a time whilst doing REPAIRS, waiting for the NAVICAT window to give me the results when I notice no MYSQL process is running and that the actual job had finished ages ago.

So I killed the process and checked how many rows were in the table - none. I did another REPAIR and it said the table was CRASHED again!

By this time I was so pissed off and wanting to go home I just truncated the table and turned APACHE back on.

From looking at the old RESTORE wp_postmeta table it was mostly guff and flags anyway and custom SEO META's. Therefore I thought fuck it, I'm not wasting anymore time on this. However before I could go the sites freezed up again and the Server Monitor was telling me a REPAIR by SORT was going on.

However yet again on the servers console running a TOP showed 0.00 load and no MySQL processes!

What was going on I have no idea but it seems truncating a corrupt / crashed table didn't help. So I had to manually re-create the table, indexes, options, PK, auto-increment amount etc by hand.

Then I turned APACHE back on and everything was blistering fast. I waited a little bit to ensure an automatic REPAIR wouldn't start again and then ensured all my pages were using the correct templates, filling in Yoasts SEO boxes at the same time and then left.So far so good it is all working and I don't seem to have lost any data apart from 90,000 rows of guff.

As I don't add my images in by hand (a lot of these blogs are pretty much automated) they are not in the wp_postmeta table. Instead they are just referenced from their local or foreign location which was lucky for me.

The key to this lesson is - always have up to date backups and don't rely on Wordpress Plugins to do it for you. 

You can see my debate over WP-DBManager not working on Wordpress here if you want and it was only a manual backup I did through NAVICAT that would have saved me if the insert had worked as the plugins backups were so out of date.

Anyway that was my "fix" for my problem.

Listen, learn and take notes from my mistakes so you don't make the same ones!