SQL Database Transfer


#1

I was trying to move a mySQL Database between my old dreamhost account to a new personal one.

I can get it exported but when I go to import it via PHPMyAdmin it says I cannot access the database. I tried adding the ip address it listed on the error to the old username on the database and re-exported it. But that didn’t work either.

Any help in a resolution to this would be greatly appreciated.


#2

Are you sure the “new” database (the one into which you are trying to import the data) has been successfully created and the DNS for the new host has propagated?

–rlparker


#3

Yes I checked the sql data file before going into the Import function of PHPMyAdmin. It’s all there. I have other databases on my new account (both with dreamhost) and have been able to access them fine.


#4

I’m stumped…I’m sorry but I have no idea what might be happening. It seems contradictory in that if you can access the database via phpMyAdmin, it shouldn’t gag returning a “can’t access database” error when running a query (which is all the import is) against the database.

I may not be understanding what is happening here from your description, but I don’t know what to suggest. If no body else pop up with some help it might be time for the tech support ticket approach.

–rlparker


#5

Would it be a problem that I am trying to import it into a new domain altogether that doesn’t have anything to do with the old sql database?

Could I get around this by using a program to connect to the accounts and move the database like that?


#6

I’ve had a problem where the database name in my dump didn’t agree with my new database name. Look in your dumpfile to see something like:
– Current Database: **mydbname**
USE **mydbname**;

to see if it’s the same name as you’re trying to import into.

-Scott

[edit]Oh, and if they don’t agree, edit the dumpfile so they do agree.


#7

It doesn’t give me an option using the web interface to import it into a database. Here is the exact error message I’m getting when I import it. I’m importing it into another domain and sql database, I’m starting to think that is the main problem.

Error
SQL query:

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

– Host: blog.kay.flawed-reflection.com
– Generation Time: Nov 09, 2006 at 11:28 AM
– Server version: 4.1.14
– PHP Version: 4.4.4

– Database: kayblog

CREATE DATABASE kayblog DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL said:

#1044 - Access denied for user ‘anirok’@‘208.113.128.0/255.255.224.0’ to database ‘kayblog’


#8

Ok, looks like an permissions issue. Are you trying to do the import while logged into a DreamHost machine? At least it gave you a hint about the problem.

From the Panel->Goodies->Manage MySQL, click on the user associated with the database. Hopefully you’ll see the “kayblog” database listed with an anirok user. Click on “anirok” to see the access permissions, including the IP address(es) from which you can connect.

On the other hand, if “anirok” is your shell user account, you’ll likely have another user associated with the database. Your restore command should look like:

mysql -u anirok -p -h blog.kay.flawed-reflection.com kayblog

Again, make sure that “anirok” is the database user listed when you go to Manage MySQL.

-Scott


#9

I don’t believe that the domain has much to do with it. The import query shouldn’t care about the "host"name (which contains/references the domain name), only the database name. I really don’t think that is the issue.

–rlparker


#10

Okay, now it is starting to become clear. Posting the whole error message helped a lot. The error message can be a little misleading, and the situation is complicated further by the way Dreamhost has MySQL permissions set up.

On Dreamhost, you cannot create databases via phpMyAdmin, or from the command line for that matter. You first create your database from the Goodies–>Manage Mysql section of the Control Panel. This is the only way on Dreamhost that you can create a database.

Once you have done that, or if you have already done it (which I suspect is the case as you can access the database via phpMyAdmin!), you can then import your data from the previous export.

The problem you are having is because the export included a “create” statement (you could have simply exported the tables, and then imported them). The error message is not telling you that you do not have “access to the database”, but that your "access to create the database is denied as you do not have permission to do that (which is correct as explained above). Fortunately the fix is very simple.:

Open the exported query file in a text editor (not a word processor) and simply remove the line “CREATE DATABASE kayblog DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;”, then save the file and attempt your import again.

Now it will not “gag” on the “CREATE database” statement , which is not needed anyway, since you already created the database, and will proceed to import and populate the tables. When completed, you should be good to go.

–rlparker


#11

I figured things out, thanks to you all.

I was trying to create it just using the import and not being in any database (problem one) because I got a little confused when I tried to make the database and didn’t go into it and hit import and it said it was alreay there.

But I removed the CREATE DABASE tag and did it in the blog itself and that worked just swimmingly. Thank you all again


#12

Absoulutely, but the problem is not with the user’s permissions, rather due to the fact that the import query contains a “Create Database” statement, which users cannot execute from phpMyAdmin on Dreamhost. The Create Database access is what is denied, and that can’t be "fixed or “tweaked” from the panel.

The fix is, as described in my reply to the original poster, having already created the database via the panel, to remove the “Create Database …etc” line from the sql file; the rest of the query should then execute without trouble.

Another alternative is to only export the tables and data, rather than the whole database, which will build a different query, without the “create database” statement, for use in the subsequent import.

–rlparker


#13

I’ve done command line restores from dumps that contain the CREATE DATABASE command, but mysql seems to ignore the command if the database already exists.

-Scott


#14

Ah! Good point there, Scott, which makes it pretty clear that phpMyAdmin is what is throwing the error. I’m not sure whether not reporting the “ignored” command is a “good thing” or not, but it sure is a good thing to know. Thanks!

–rlparker