Moving WordPress Databases

Although I keep telling people I don't do servers, I'm looking into moving a friend's active Phorum-based BBS to a new host. I'm considering FutureQuest as the new host. I'd heard good things about them, though they're a little pricey compared to other plans.

This is a good opportunity for me also to evaluate new hosts for the blog. I'm currently using Pair Networks, who have been awesome for the past 8 years, but their database servers start pooping out when you get over a certain number of transactions per minute, and it's starting to become an issue.

This is the first time I've had to move a WordPress blog from one host to another, so I'm documenting the process. WARNING! GEEKY NOTES FOLLOW!

Different Server, Different MySQL Versions

Conceptually, it is as simple as uploading my current WordPress files to the new server, and then transferring the MySQL database that holds all my posts and stuff. POOF, it should work, right?

As it turns out, the FutureQuest DB servers are using MySQL version 4.0.x, and Pair is using 4.1.x, so I have to downgrade to an older server version.

MySQL Import Woes

There were three easy ways I found that would backup a Wordpress database, which I tried one-after-the-other.

• First up was Aaron Brazell's WordPress-to-WordPress Import exporter / importer. WordPress doesn't come with a WordPress importer, so that's what this package adds.

  The plugin exports your posts in an extended RSS format, which you can then upload via the import plugin from your browser. Great in theory! Unfortunately, my blog backup is 9MB, which exceeds the 2MB upload cap of this server for PHP. I may try hacking this to read the backup file from a location on disk, but I decided to try another approach.

• Next, I looked at Skippy's WP-DB-Backup plugin, which is included with WordPress 2.0, gives you the ability to produce a SQL dump file from the convenience of your admin panel, without all that fussing around with the command line.

  So I did this, and then tried importing the dump file into the new server using the standard mysql < dumpfile.sql style approach. It didn't work: 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1'

  Darn! This looked so promising.

• I moved on to Lester Chan's WP-DBManager 2.05 plugin, which allows you to do a lot more with your WP database. It uses mysqldump to create a compatible SQL dump file, which you can then use to re-initialize a new WordPress installation. SAME ERROR in attempting to import. DOUBLE DARN!

Downgrading from MySQL 4.1 to MySQL 4.0

After some digging, I found that MySQL 4.1.x added a different way of handling character sets, and this was reflected by the DEFAULT CHARSET command added to the end of the CREATE TABLE statements in the dump file. MySQL 4.0.x has no idea what this is, so it throws an error.

The workaround I found was to modify lines 32 and 36 of Lester's dbmanagerdatabase-backup.php file. I added:

--create-options --compatible=mysql40

...before the '|' character on line 32, and before the '<' character on line 36. The first parameter tells mysqldump to use MySQL-specific syntax for creating the table, and the second parameter makes those problematic DEFAULT CHARSET commands go away.

The Backup Procedure

So here's what I ended up doing:

1. On the CLONE blog: Install WordPress 2.0.4, plain vanilla, uncustomized. This is the "blank clone" on which my old blog will be imprinted.

2. Install Lester Chan's WP-DBManager 2.05 plugin on both old and clone blogs.

3. On my OLD server: I edited the plugin as described in the previous section, so the database backup file created would be compatible with the new server. Again, my current server uses MySQL 4.1, and the new server uses MySQL 4.0.

4. On my OLD server: Created a database backup with the patched version of Lester's plugin. Yay!

5. Then, copy the database backup to the CLONE server. It's in wp-contentbackup-db if you've successfully installed the DBManager plugin; just copy it using FTP or SCP to the same location.

Preparing for Restore

• First of all, my CLONE server has only a numeric IP address, not a domain name. I don't plan on transferring the davidseah.com domain over until I make sure everything works. So the URL I use for it is, for the same of example, is http://192.168.1.100.

• WordPress keeps track of your blog's URL in the database table, and uses that to generate links to various parts of your blog. When you do a database restore with DBManager, it overwrites your entire database, including that blog URL setting. When that happens, you won't be able to use the admin page, because you'll be redirected to your OLD blog everytime you try to login.

• To get around that, you need to be able to edit the database directly and reset the BlogURL setting. I installed PHPMyAdmin on the clone, to be ready to edit the database manually when I did the database restore.

1. To the CLONE server: Copy the database backup file to the wp-content/backup-db folder, where DBManager looks.

Now you're ready to restore the database.

The Restore Procedure

1. On the CLONE server: Copy all your plugins, template files, and so forth over to the new WordPress installation.

2. On the CLONE blog: Go to the admin page and click the DATABASE tab that DBManager has added. Go to the MANAGE BACKUP DB tab, and choose the database file to restore (you did copy it to the right place, right?).

3. Restore the database by clicking the RESTORE button! If everything goes well, you'll see a SUCCESS message. Congratulations! Your database has been cloned onto the new wordpress installation!

4. Visit the cloned blog server and see if the home page works. It's probably messed up in some ways if you've forgotten to transfer everything over, and you'll find that you keep getting re-directed back to the old blog if you click on anything. That'll be fixed next.

5. On the CLONE server: Run the MySQL Administration Tool of your choice. Using PHPMyAdmin, I went to the wp-options table and clicked BROWSE. I edit the siteurl option value to point to the CLONE server (not the CURRENT blog).

   Here's an example:

   My old site is davidseah.com. My new clone site is, say, on a local server 192.168.1.100, and WordPress is installed in a directory called wordpress. The new siteurl (at least for now) is http://192.168.1.100/wordpress, so that's what I should set the siteurl to. Your new webhost should have provided you a URL that you can use to FTP files to, so just use that.

6. You should now by able to login and visit the CLONE blog's admin page. Go to Options and change BLOG ADDRESS so it's also pointing to the new server for testing.

After all that, the cloned blog seems to be working, but I won't be able to tell what genetic defects may be lying dormant until they manifest. Database performance seems a little sluggish to me, so I'm not sure if the way that I performed this database move somehow messed up the DB. Maybe some MySQL guru out there can give me some pointers.

Next Steps

Before I move everything over, I need to make sure that my .htaccess is set up correctly, get ALL the downloadable files moved over, update my basecamp FTP settings, and also ensure that my new email addresses are set up on the new server. What a pain in the butt!

My DNS is handled by another registrar, so I should be able to just change the nameservers when I'm ready and see what happens. If it doesn't work out, I can just switch the nameservers back, and resume operations at my current host.

UPDATE:

• My Server Move Log, which also details the different way I moved the database with straight mysqldump and mysql restore commands from the shell.

Editable Comments and WP Cache 2.0

I've been making various improvements to my WordPress installation, hopefully making it a little easier to play here on Better Living through New Media. One long-standing peeve was the lack of user-editable comments. I had seen Andrew Sutherland's Edit Comments Plugin before, but had put off installing it because the post warned of the "complex modifications" needed to your theme files. As it turns out it actually isn't bad, but I had to fix a compatibility issue with WP Cache 2.0 to get it to work right on my install of WordPress 2.0.3.

Geeky notes follow.

The Installation

As I said, I found the mods actually aren't too bad if you understand PHP and WordPress at an intermediate level. There are basically only two steps:

1. Upload the plugin and activate it in WordPress' plugin administration window.

2. Edit your comments.php file. The variable is how your particular theme implements that file, so it is potentially confusing to those unfamiliar with WordPress internals. The readme.txt file that Andrew includes is very clear on what you need to edit. It's a delight to come across a technical document that establishes both "what you need to do" with "what it does" and "how it works". Excellent! There are about three things you need to change in the comments.php file, and it's actually pretty straightforward. I put this all off for nothing!

There was one wrinkle, of course, otherwise I wouldn't be writing this. WP Cache 2.0, the cache plugin I use to lighten the load on my shared host, caches the comment so your edits don't show up after you're done. They are saved, but you can't see them until the cache expires or you edit again. Annoying.

The Modifications

First, I went to my WP Cache Options in the WordPress Admin panel, and added jal_edit_comments to the REJECTED URIS list. Save the strings. This prevents any page that uses the Edit Comment Plugin's method for communicating with WordPress (a GET value called jal_edit_comments) from being cached. It occurs only when someone clicks the "edit" link for an editable comment.

Second, I modified my copy of jal-edit-comments.php to detect WP-Cache 2.0 and delete the associated cache file for the current post being commented on. As far as I can tell it works. I added the following function:

// if wpcache is installed, delete cache file for this page
// .. added by david seah 07/14/06 
// .. called by jal_edit_comment_init(), jal_do_edit()

function jal_purge_wpcache() {

  if (function_exists('wp_cache_get_cookies_values')) {
    // import wp-cache configuration
    if (@include(ABSPATH . 'wp-content/wp-cache-config.php') ) {
      // strip out everything after the ? in the uri
      $uri = preg_replace('/?.*$/', '',$_SERVER['REQUEST_URI']);
      // create wp-cache 2.0.x compatible filename
      $key = md5( $uri . wp_cache_get_cookies_values() );
      $cache_filename = $file_prefix . $key;
      // delete the cached file
      if (function_exists('wp_cache_clean_cache')) {
        wp_cache_clean_cache($cache_filename);
      }
    }
  }

}

Now you have to modify two other functions in jal-edit-comments.php:

• Modify jal_edit_comment_init() by adding the following right after the first echo statement:

  echo '<p><input type="hidden" name=...
  // dseah: purge wp-cache file
  jal_purge_wpcache(); 
  

• Modify jal_do_edit() by adding the cache-purge call before the first die() statement:

  // dseah: purge wp-cache file
  jal_purge_wpcache();
  die();
  

• Now purge all the cached files using WP Cache's option panel, and you should be set.

What's Supposed to Happen

WP Cache 2.0 creates a unique filename for every web page it caches, and stores that in a special cache folder. The modifications I made to EditComments recreates that unique filename, and passes it to WP Cache's "Delete Cache File" function at two critical moments:

• Clicking the "edit" link adds a special GET variable called jal_edit_comments. This is intercepted by jal_edit_comment_init(), which now also calls our new function to purge the cached file. This makes sure that if there's a timeout, the cached page is nuked, otherwise the "edit" link will still be served up.

• Clicking the "submit" button invokes jal_do_edit (), which steals the comment text away from the usual comment handler (wp-comments-post.php), handling the database insertion itself. After that's done, it redirects the browser back to the comment page before terminating the script; however, before we do that, we purge the cached page again, because we want to show the updated text (the edited comment) instead of the cached version (which has the pre-edited comment)

So that's what I think I'm doing, anyway. It appears to work on my installation of WordPress 2.03, WP Cache 2.0.17, and Edit Comments 0.3 Beta. There may be a situation where some of the "You aren't allowed to edit this comment" messages won't show up if multiple people are accessing the same page and therefore thrashing the cache out from under each other, but we'll see what happens.

Availability

I thought about making my modified version available, but I'll just let the author of the original plugin know after I do a bit more testing. I don't want to create a technical support headache with two versions of the same plugin, with him having to support my possibly-buggy code. And if you figured out how to install WP Cache and Editable Comments in the first place, you probably can apply these modifications yourself. Good luck!

Flickr Integration

In celebration of the Independence Day, I upgraded my Flickr account. Flickr has really been impressing me lately, so for $24/yr, I was happy to do the upgrade to Pro so I could have more albums. I figured it might be nice to actually show a few more photos on the website too; my blog is rather, er, word-heavy, so providing an alternative means to browse content seems like the thing to do.

I am using FAlbum for the Flickr integration. It's pretty cool! Geeky notes follow.

Installing FAlbum

FAlbum is a WordPress plugin that talks to Flickr through the Flickr API. It's a pretty nifty plugin, though I was almost convinced it wasn't what I needed. A few tricks to know:

• I had some hiccups with the installation; this post at idano.com cleared up some of the requirements for the .htaccess file and page creation. After that, it was a matter of chasing down the little theme incompatibilities.

• If you still have a WordPress 1.2-style theme all in a single index.php file (as this site was until 2 hours ago), you'll get a few errors and a mysterious lac of formatting. You'll need to modify the wp-content/plugins/falbum/wp/album.php file to eliminate the get_header(), get_sidebar() and get_footer() calls. This file is what generates the actual page layout on your photo URL. I decided to break out my header, sidebar, and footer into separate files like a good boy.

• I had to dig through the FAlbum_WP.class.php file to figure out what the options are, as the documentation is still pretty light. You'll also want to familiarize yourself with the content of the ../falbum/styles/ directory; in particular the falbum.css.php file so you can customize the layout for your theme.

• I am having one cosmetic issue with the default size that Flickr returns. It's 500 pixels wide, and my default column with is 458 pixels; as a result, there's some nasty resizing artifacts in the browser. Very irking.

One thing I'm concerned about is the additional processing load FAlbum may impose. For example, there's a giant XPath library class included with the plugin that's over a megabyte in size...that can't be good. I'm also noticing that the cacheing is not really working correctly; will have to track that down :/

That said, it is pretty neat that people can browse my Flickr photos without leaving the site. For one thing, I can add a real portfolio section again!

Social Bookmarking Twiddling

:http://del.icio.us I was chatting with Brad over the weekend about making it easier for people to find our content, and he mentioned a plugin that would automatically add links to [del.icio.us], digg, and other social bookmarking services. The idea is that by making these bookmarking icons more accessible, people are more likely to bookmark interesting posts, which theoretically drives traffic.

Geeky notes follow!

Notable

I hunted down Cal Evan's WordPress nifty plugin Notable and installed it. Couldn't be easier! Every post now has 5 service icons; Notable actually supports a lot more, but I thought I'd start with the five ones that I had actually heard of first, and add more later if it didn't start getting out-of-hand.

Because I can't leave things alone, I did make some tweaks:

• The graphic icons were of varying heights, which made them float unevenly in my layout. I made them all the same height (now 16 pixels, faded 50% to better work with my layout).

• I added text to identify the service each icon represented. Rolling over the icons with the mouse does reveal which service the link goes to, but I am not a fan of rollover information. Help is one thing; information should be laid out where you can see it. Personal preference. Plus it looks kind of cool.

• I fixed some typos in the function notable_define_array() that caused the output to fail XHTML validation.

This last case was the common "& where an & should be" HREF encoding problem, but I couldn't find it. I eventually figured out that that the digg, magnolia, and segnalo post_url values did not have their &s urlencoded properly, though all of the other ones were.

What made this typo particularly tricky to fix was that I thought I could just fix the definitions in notable_define_array(), not realizing that these values are copied once upon first-time installation of the Notable plugin. After that, they are pulled from the database and never updated even if you fix the typos. I was tearing my hair out trying to figure out if it was the browser cache, some mysterious PHP cache, or maybe a plugin post-processing my ampersands. Nope.

The workaround is to put the following code just before the blogbling_post_options (notable, $notable_settings) line at the end of the function:

    // ds: force update of post_url values
    $work = notable_define_array(); 
    foreach ($notable_settings['sites'] as $site=>$values_array) {
        $notable_settings['sites'][$site]['post_url'] = $work[$site]['post_url'];
    }

After doing that, the XHTML validates again. Whew!

Another minor gotchya is the "image path". I originally thought this was the full path to the plugin's image directory, but it's actually part of the URL that Notable uses to generate the <img> tags to display the icons. In other words, you're filling out this:

http://www.yoursite.com/notable_image_path

not this:

/home/virtual/yoursite-com/htdocs/wp-content/plugins/notable_image_path

Anyway, now that I have these little icons in place, I'm finding their colors too distracting, but maybe that will encourage people to click them. I'm a little doubtful that this will make a difference, but we'll see.

UPDATE: I couldn't stand it so I faded them out a bit.

The experiment in distributing original content continues!

Bad Behavior 2 Beta 1

Some nice commenters let me know that they were still having trouble posting comments and receiving errors. This was due to the anti-comment spam plugin I was using, Bad Behavior 2. Compounding the issue was the cryptic error messages BB2 generated when its "5-second wait-before-you-can-post" trigger was activated. I eventually modified my copy so it didn't give the people the impression my entire site was on the verge of collapse; most people just saw ERROR and tons of tiny text, and (naturally) assumed that some kind of severe system error had occured due to errors in my PHP coding or my ISP's server configuration. That is in fact what I thought until I read the text more carefully.

Anyway, today I was hapy to see that Beta 1 is available. It gets rid of the 5-second wait that caused the problems I just mentioned.

• Just installed it. Crossing my fingers.

• I was about to give WP-Hashcash a try again, but people have been reporting that it's also letting spam comments through again...figured I would try BB2 first.

• I also deactivated Spam Karma 2 and enabled Akismet 1.15 to see what happens. I know, it looks like bad debugging methodology...I should just keep BB2 on for a while first to establish any change in behavior, then try swapping Akismet in. However, the input is pretty variable: new spamming techniques arise every day, as do new spammers and spam surges. I wouldn't be able to tell, without spending a lot of time I don't have, what the causal relationship is between spam stopped and a particular plugin combination. So I might as well just see how they work from an administrative perspective.

Without these plugins, I would have to shut off comments. It is incredibly irritating that these spammers STEAL OTHER PEOPLES TIME AND PROPERTY to make their money. This starts to make the non-anonymous Internet look attractive, but then you have the problem of TRUSTING THE GATEKEEPERS of the system to maintain the egalitarian policies of a Free Internet in the face of commercial greed. Sigh.