This is a very long post, so most of you might want to just stop reading now. I am pretty confident this will solve your problem though mattkelly, so you may want to read on.
You can either reexport the original with some minor modifications, if you can still connect to the old host. A better option, if you’re comfortable making some minor modifications to the exported SQL script, is a really simple change. I’ll further detail both options.
1) EXPORTING PROPERLY WHEN YOU HAVE NO CREATE DATABASE PRIVILEGES:
When you login to the phpMyAdmin interface for your mysql server and click “export”, you’re taken to the export configuration screen. This is a full database export and the resulting sql script will always attempt to “CREATE DATABASE”, whether you have “Add DROP DATABASE” checked or not.
To avoid this issue you should instead choose the database you want to backup from the select list in the left frame, then click the export tab you see on the right frame. Click “select all” under the tables list in the left portion of the right frame to mark everything in that db for export.
The other default settings will work if you are aggregating disparate data from two sources, but you’ll end up with duplicate data if you run an import on the same script more than once. Since I like to keep my various MySql servers in sync, I always check the “Add DROP TABLE” and “Add IF NOT EXISTS” options.
The first option will make sure you end up with an exact copy of the original database export on the server you’re importing to. The second option ensures that you won’t throw any errors if you’re importing to an existing database with the tables already defined. If you don’t check this option you have to manually drop all the tables on the destination database prior to running the import script.
It would be appropriate to leave these unchecked if you have other stuff in the destination database that you don’t want to lose. An example might be two different MySQL servers with similar data models, that run two different sites. You might want to only add the unique stuff from db1 to db2.
2) SIMPLE SCRIPT MODIFICATION TO EXISTING SQL SCRIPT:
If you’re comfortable modifying the existing SQL script you’ve already got, it will save a lot of wasted cpu cycles and valuable time. Since you mentioned that you want to update your recent install, but the first line of your script is “CREATE DATABASE” rather than “DROP DATABASE”, I’m pretty sure you exported using the default settings.
Even if you’re not comfortable modifying this script, I suspect you no longer can access the old server since you’ve changed hosts. There are a few things you’ll need to modify within the script in order to keep it from blowing up, but none of it is terribly difficult. Keep in mind that this will cause you to lose any existing data, and will result in an exact copy of the original. I don’t know what’s different though, such as pathing and junk like that. For an extremely portable app like WordPress, I strongly suspect the server specific stuff is contained in config files rather than within the database. As with most advice CAVEAT EMPTOR, but you can just reinstall WP through the DH panel if there is anything it screws up.
First, you have to eliminate the offending line from the SQL script. Open the script up and put two dashes before the line starting with “CREATE DATABASE”. This will comment it out and eliminate the first problem. Next you have to conditionally drop every table that the script tries to create. This ensures both that you don’t throw an error when attempting to create a table that already exists, as well as not throwing an error when you try to drop a table that doesn’t exist.
Do a “Find” on the script looking for the term “CREATE TABLE” (w/o quotes) followed by a tablename. On the line immediately preceding this, add a line referencing the created table in this format: “DROP TABLE IF EXISTS
*tablename*;” (w/o quotes). Obviously you’ll need to replace the “tablename” text with whatever table is created. “F3” through the script until you’ve done this for every table that it’s trying to create. Save off the script and run the import again. Now you’re ready to make a million dollars…
I can’t think of anything that would be a problem, as long as WP hasn’t made a major overhaul to it’s design between the various versions. This is highly unlikely considering that even scripts from the Mambo / Joomla split can be salvaged. Obviously back your script and existing database up first though.