Posts Tagged ‘SQL’

WordPress Upgrade Issue

July 1st, 2010 No comments

So recently, when BurgerBlog went through the automatic update from WordPress 2.9.x to 3.0, it ran into a slight hiccup that corrupted ALL of the posts that were on there. The issue seemed to be with the format of the single and double quotes throughout all the posts. They were all, without fail, replaced with ‘?’s. Now, what baffles me is how the system saved the wrong double and single quote symbols in the first place.

The quotes were the ones that are seen in word processors, like Microsoft Word, that are directional. So if I “quoted” something, the first quote would be different from the second quote.

Regardless, the reason that the system originally saved them, but no longer saved them is what truly confused me. As a result, I went through the following procedure:

NOTE: SQL knowledge and access to the phpMyAdmin page is required.

  1. Accessed the phpMyAdmin page, found on the control panel for your site. No, not the WordPress control panel, the one that’s on the back-end of the site itself (gives you access to your site’s stats, FTP accounts, email accounts, etc.).
  2. Browsed to the WordPress database and used the following SQL statement to find all posts that now had ‘?’s in them:
  3. To get all the posts with ‘?’s in them and their IDs.
    SELECT ID, post_title FROM wp_blogname_posts WHERE post_content LIKE ‘%?%’

    To get the distinct post titles that contain ‘?’s in them.
    SELECT DISTINCT post_title FROM wp_blogname_posts WHERE post_content LIKE ‘%?%’

  4. From there, I was able to garner the proper ID of the post title (most easily found by finding the present blog post with that title and selecting edit, then looking in the URL bar for the ID), and use SQL back-ups where possible to garner the most recent version with the quotes and single-quotes in them still.
  5. Did a find and replace on my back-ups for all of the incorrect double and single quotes.
  6. Manually pasted in the corrected post content directly into the database to expedite the process (could have done it manually with each edit page, but that requires waiting for all the WordPress functions and AJAX to load).

It took a couple hours to go through the 40 posts on BurgerBlog that were messed up, and that’s not including the extra time required to fix the ones I didn’t have back-ups for (I had been slacking with my consistent back-ups). Now, it is almost entirely fixed, with the exception being the couple of ‘?’s that were missed in the ones that were done manually (those should be fixed by end of 7/2 anyways).