mySQL import error


#1

I am trying to import a ~20mb sql file for my Invision Power Board installation. If I compress it (*.gz if it matters) then import it through phpmyAdmin or if I try to use bigdump to import the file (not compressed), I get this error:

Error at the line 15: CREATE DATABASE ipbtest3 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Query: CREATE DATABASE ipbtest3 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL: Access denied for user ‘[username]’@‘xxx.xxx.xxx.x/xxx.xxx.xxx.x’ to database ‘ipbtest3’

I don’t know what’s wrong… the user has all of the permissions to create database and stuff but yet this isn’t working. It’s pissing me off because I’ve heard good success rates with bigdump but it doesn’t work for me. Help!


#2

The error message is your hint to what the problem is! :wink: On DreamHost, you cannot “create” databases in any other manner than via the Control Panel, and you “dump” has included the command to “create” the database within the dump.

Therefore, when you try to “run the SQL query”, it fails at the “CREATE DATABASE ipbtest3 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;” on line 15.

Assuming that you have already created the desired database via the control panel, the solution is to open the SQL query file in a text editor, delete that “create” line from the file, save the file without the create command in it, and import it again. :wink:

Without seeing the actual file, I can’t say for sure whether or not additional “tweaks” will need to be made, but generally this should solve your problem.

–rlparker


#3

Now I get this error:

MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CHARACTER SET latin1 COLLATE latin1_swedish_ci’ at line 1

This is where the error occurs:

CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE ipbtest3;

Thanks, though, since I’m getting a different error, it means I’m progressing :slight_smile:


#4

I had the same problem before when I try to transfer database from MySQL 4 to MySQL 5.

Maybe the difference of MySQL versions is the cause of the problem. I did not look into the problem because I just re-created the database manually (my database is very small). But you may find out something by reading MySQL manuals.

Save $97 (MAX Discount) with code: [color=#CC0000]97YES[/color] Sign Up NOW or More Codes here


#5

Going through the thing a line at a time is not really productive, and I’m especially confused about the “at line 1” part of the error message - you got well past line “1” before the last error, and you only were changing line 15, right?

If that is true, there is no reason why “line 1” should now be causing a problem when it didn’t before. :wink:

If you put the first 20 lines or so of your “dump” in a post, I’ll be happy to look at it for you.

–rlparker


#6

I don’t think that’s the problem, because the exported data comes from the phpmyadmin on Dreamhost. I’m just creating a backup of my forum. So I don’t think version number is a problem. Thanks, though.

@riparker, here’s a couple lines from the SQL dump. If you need more, just ask :slight_smile: You’re very helpful.

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

– Host: db.zantherus.com
– Generation Time: Sep 10, 2007 at 04:21 PM
– Server version: 5.0.24
– PHP Version: 4.4.7

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;


– Database: ipbtest3

CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE ipbtest3;



– Table structure for table ibf_acp_help

CREATE TABLE IF NOT EXISTS ibf_acp_help (
id int(10) NOT NULL auto_increment,
is_setting tinyint(1) NOT NULL default ‘0’,
page_key varchar(255) default NULL,
help_title varchar(255) default NULL,
help_body text,
help_mouseover varchar(255) default NULL,
PRIMARY KEY (id),
KEY page_key (page_key)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=167 ;


– Dumping data for table ibf_acp_help


#7

As I suspected, you “mistyped” the error message in your last post - the error you just got is the result of you not completely “deleting” the “create” query from the file, and is only line 15 not line 1.

Delete the entire line:

“CHARACTER SET latin1 COLLATE latin1_swedish_ci;”

… it is only the “end” of the “create” query which you only partially deleted.

There is a great deal of useful information relating to MySQL migration on the DreamHost Wiki, and reading through that would probably be of great help to you.

You might also take this opportunity to view a basic MySQL tutorial or two - there are lots of them available by Googling. :wink:

–rlparker


#8

Thanks, I was able to import the database through ssh. It’s working now! :slight_smile: Thanks so much.


#9

That’s nice to hear; I’m really glad you got it working! :slight_smile:

–rlparker