Notice anything different about the blog? (Hopefully not!)

This weekend I made some updates behind the scenes that hopefully haven’t broken anything. In December, 1&1 (my webhost) sent an email saying they would discontinue support for php 4 on April 1 so I needed to update my site to php 5.4. Naturally I waited until mid-March to deal with it. I actually wanted to do this update a while ago, because my blog was running on an ancient version of WordPress (2.9), but for a long time 1&1 only supported php 4 and the newer versions of WordPress need php 5.

Switching the php version turned out to be a simple matter of selecting the new version number from a drop-down menu in the 1&1 tools.

You can switch the php version setting by clicking Global PHP Version in the 1&1 Control Panel.

Of course, once I did this my blog displayed as a blank page, because my ancient version of WordPress didn’t work with php 5.4. As it turns out, newer versions of WordPress also require that the database is MySQL 5. Mine was using MySQL 4, because that’s what was available from 1&1 when I created the site. Switching this was not a simple matter of selecting a version number from a drop-down menu, however. I had to create a new database and move everything from the old version into the new version—a task that’s far beyond my comfort zone as far as this stuff goes.

1&1 has a fairly straightforward FAQ that explains the process… problem is, if it doesn’t work exactly right, they don’t tell you what to try next! According to an article from 2010, fewer than 6% of WordPress users were using MySQL 4 even then. (Lucky me!) So the problems I experienced probably apply to a relatively small number of people, but if you’ve found this post because you’re a 1&1 user who can’t figure out what to do next, here’s what worked for me.

First, from the 1&1 Control Panel, click MySQL Administration to get to the page that displays your databases. Click New Database. You’ll get this page that asks for a database description and password.

Give it a description that will make it easy to distinguish from your current database (my original MySQL database was called “blog” so I named the new one “blog2”). For the password, I chose to use the same password I had currently. I don’t remember choosing this password or even setting up my original database, so I’m not sure where it came from, but I found it in the wp-config.php file. Download this from your WordPress install via ftp, open it with a text editor, look for DB_PASSWORD, and copy/paste the password from inside the quotes and into the fields on the New Database page. (And then hold onto the wp-config.php file, as you’ll need it again later.)

The database takes a few minutes to set up. While this is going on, make a backup of your original database. The instructions 1&1 gives work well enough, and WordPress suggests a similar process.

When making the backup, rather than selecting all the tables at once I suggest selecting all of them EXCEPT wp_posts. After you’ve made one backup of the other tables, back up wp_posts separately. 1&1 doesn’t give a suggestion for which compression to use, but for me “gzipped” seemed to work best. (Don’t bother with plain old “zipped”; every time I tried to import a backup that had been compressed that way I got an error message.) And when you do back up the wp_posts table separately, DO NOT select “extended inserts” under data—this caused a lot of problems for me.

The next step is importing your backup into the new database. This time, 1&1’s instructions leave something to be desired. In Step 4, they tell you to click the little SQL box under the 1&1 logo. Every time I tried to import my data via this button I got weird errors that meant absolutely nothing to me. After several failed attempts and a bit of web poking-around, I realized that there’s another way to import that (surprise surprise!) works much better.

By all means, try following their instructions first. If it works for you, great. If not, click the database id on the left side of the screen, and then click the SQL tab on the right side. You’ll see this:

Click in the field under “Or Location of the text file” and browse to the first backup you made (the one that doesn’t include wp_posts). I’m not sure if selecting the compression type or leaving it on “Autodetect” matters, but you might as well pick whatever you used for compression before clicking Go.

Hopefully this will work without a hitch. If it doesn’t, you might get an error message that says “MySQL server has gone away”. According to my friend Doug, a database programmer who answered my frantic email plea for help (thanks!!), this basically means the server timed out before the upload was finished. In other words, 1&1 choked because you’re trying to import too much data at once. Try going back to your original database and exporting the tables in smaller groups. (Remember, don’t bother with wp_posts for now!) If you keep getting errors, you can also try the other compression methods or no compression, and try selecting “complete inserts” and “extended inserts” in the Data field. I don’t know exactly what combination ended up working for me, just that I had to try this multiple times.

Once the rest of your tables are in, it’s time to tackle wp_posts. This is the table that houses all of your WordPress posts, including every revision you’ve ever made. Subsequently, it’s probably a huge amount of data compared to the other tables. When I tried to import this, I initially kept getting errors that said “got a packet bigger than ‘max_allowed_packet’ bytes”. After a little Google sleuthing I figured out that this has to do with ALL of the post content (from every post and revision I’d ever made) displaying as one long line separated by commas. Apparently it’s a result of ticking off “extended inserts” when you made the backup. So, if you didn’t do that, hopefully you’ll never see this error at all.

Once I’d redone my wp_posts table without the extended inserts, I started getting “MYSQL server has gone away” again. Based on what Doug told me about the meaning of that error, I figured it was still too much content to upload without a timeout. Compressing the backup didn’t make any difference so the only option appeared to be to paste in the content manually. This requires opening the backup file with a text editor such as TextEdit or Notepad (or something more robust if you have it!) If your computer is unable to unzip the compressed file, you can make another backup of the wp_posts table without compression, and open that.

My import had worked to the point that the table itself was set up, so I now saw wp_posts among the list of tables and the table structure existed. If you don’t have this, copy the text at the top of the backup file, from DROP TABLE IF EXISTS down to the last semi-colon before the double dash, and paste it into the “Run SQL query/queries on database” box. (This is on the same SQL tab you used to import the other tables.) If there’s text in the query window, it’s okay to overwrite this. (And remember, you only need to do this step if you don’t already see wp_posts among the list of tables at the left of the screen!)

(You might get a dialog asking if you’re sure, click OK.)

You should now have a wp_posts table listed among the rest of your database tables. Finally, you need to get your post content into that table. This is done the same way, but this time you’ll copy the blocks of text that start with INSERT INTO. Depending on how many posts you’ve made, this may include thousands of chunks of text! Try doing a few at a time to make sure it’s working. (Again, if there’s text in the query window already, it’s okay to paste over it.) If everything seems to be going well, you can start copying/pasting bigger portions.

Hopefully you now have all of your content ready to go! The last step is to get your blog pointed to the correct server. Open your wp-config.php file and look for the fields DB_NAME, DB_USER, and DB_HOST. Change these to the new database name, user name, and host name that are listed on the 1&1 MySQL Administration page (the page that lists your databases). Then save the wp-config.php file and upload it to your WordPress directory via ftp, overwriting the original file. Your blog will now be pointing to the new database.

Once I got all this working and was satisfied that everything looked right and my content had survived the transition, I switched to php 5.4 from the 1&1 Control Panel, then upgraded WordPress manually to version 3.5.1. (The manual upgrade was necessary because automatic upgrading didn’t seem to work under php 4, and my blog wouldn’t display at all under php 5.4… so, no way to access the wp-admin pages.) I’d expected this part to be the biggest hassle, but by comparison it was a piece of cake. I followed the instructions here and found them very clear and easy to understand.

If you’ve read this far, it should be pretty obvious that I know just enough about this stuff to get myself into trouble, so if anything I’ve said contradicts what you’ve read elsewhere or what you know to be true, use your judgment! I’m just hoping to save someone else the time suck and frustration I went through this weekend.