1 down, 200 (or so) more to go

December 25, 2009 0 By archshrk

I’m very happy to have read this message in my admin panel just now

Downloading update from http://wordpress.org/wordpress-2.9.zip.

Unpacking the update.

Verifying the unpacked files

Installing the latest version

Upgrading database

WordPress upgraded successfully

It means I’ve finally managed to upgrade my database from MySQL4 to MySQL5 – something of a feat when hosted by 1and1.com. Typically, I love 1and1 hosting because it’s very cheap affordable. The problem is, they’re very cheap. While I get to host several domains for one low price, the features/functions I need as webmaster are not really there. They don’t keep up with the latest version of software/program language and access to basic data (like error logs) is not available.

For the most part, this isn’t a problem – but when there is a problem,it becomes a big one. Take for instance the latest upgrade for WordPress (2.9 in this case). This version pretty much requires SQL5 to run smoothly. In fact, it does require MySQL version 4.1.2 or greater. I had 4.0.

Now the process for turning your database into MySQL5 is pretty simple. Just export the existing MySQL4 tables and import them into a new MySQL5 database. Then point your WordPress install to the new database. The problem is, my database was too big to import.

I followed the online instructions exactly but kept getting a “no SQL query” message. So I contacted Tech Support and they offered their less than stellar help, which is sadly – very typical. They asked for the “dump” file, and gave some advice which didn’t work. Then they asked for the database names (which I already gave) so they could look more closely. Their response was to use SSH (Secure Shell Access) to import the tables.

If you’ve ever worked with SSH, you know it’s not typically as easy as it sounds but I gave it a shot. After several mis-steps, I simply gave up and tried a new approach which someone else had mentioned earlier. Create smaller dump files and upload them one at a time. So here are the instructions for anybody having similar problems.

Upgrade WordPress and MySQL with 1and1 hosting.

I just did this (with 1and1) the process is not as easy as it should be.
Step 1) create a new MySQL5 database (this will take a few minutes to finish)
Step 2) export your database tables from the old database. Make sure you use gzipped compression and keep the final output under 2Mb (about 8Mb uncompressed).
Step 3) Import the handful of files to the new database (should be ready by now)*

At this point you should have 2 databases with the same data. One will be MySQL4 (existing) and the other MySQL5 (new). Now to point your WP install to the new database.

Step 4) Backup and change the database info in your wp-config.php file (Top level of your existing WordPress install)

Step 5) That should be it.

Now about the upgrade process. There is another problem with 1and1 which is php Memory Limit is too low and you can’t change that. So you’ll need to do a couple of things.

Step 6) Deactivate all plugins. This helps free up the php memory so you can perform the upgrade.

Step 7) Use the built-in upgrade feature to install WP 2.9. Once upgraded, reactivate all you plugins.

Enjoy your new WP install and database.

*In theory, you could export the whole database and import it using SSH but SSH isn’t very user friendly for beginners or casual bloggers. For instructions on how to import/export check with the 1and1 FAQ.