Databases

apps

#1

I need some help trying to update my wordpress database. I just installed wordpress with a new database and need to update with my old one from another host. If you can help with step by step that would be great! :slight_smile:

Thanks,
Matt


#2

You might want to check the Dreamhost Wiki Article on Migrating MySQL databases for “step by step” directions, and then write back here if you have a particular question.

–rlparker


#3

OKay, here is the error message I got:

Error
SQL query:

– phpMyAdmin SQL Dump
– version 2.8.2.4
http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Sep 28, 2006 at 01:10 AM
– Server version: 4.1.21
– PHP Version: 4.4.2

– Database: mevans_wrdp2

CREATE DATABASE mevans_wrdp2 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL said:

#1044 - Access denied for user ‘wordpress’@‘64.111.96.0/255.255.224.0’ to database ‘mevans_wrdp2’


#4

Well, while you didn’t tell us what you were doing (what command you executed, etc.) when you got the error, we will have to make some assumptions.

It looks as though you tried to “create” a database using PhpMyAdmin on Dreamhost. As the error message indicates, your user does not have “create” permissions via PhpMyAdmin on dreamhost; you have to create your database from the Contol Panel.

After you have done that, you can then import the data from your old database. If you got this message upon trying to “import” your old database, you should open the .sql file that you were running as a query and delete the “create” line, save the file, and try again.

It looks as though you tried to do an import that included a create statement, and that won’t work on Dreamhost

Bear in mind that is only a guess, as you didn’t give any information other than an error message. In order to give you more help, we have to know what you were doing when you got the message.

–rlparker


#5

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… :wink:

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.

Ben Conley

http://benconley.net
http://teamshocker.com