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!

The error message is your hint to what the problem is! 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.

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

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 :)

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: 97YES Sign Up NOW or More Codes here

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.

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

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 :) 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`

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.

--rlparker

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

That's nice to hear; I'm really glad you got it working!

--rlparker