Error importing SQL dump into MySQL: Unknown database / Can't create database

Question

I'm confused how to import a SQL dump file. I can't seem to import the database without creating the database first in MySQL.

This is the error displayed when database_name has not yet been created:

username = username of someone with access to the database on the original server.
database_name = name of database from the original server

$ mysql -u username -p -h localhost database_name < dumpfile.sql   
Enter password:  
ERROR 1049 (42000): Unknown database 'database_name'

If I log into MySQL as root and create the database, database_name

mysql -u root  
create database database_name;  
create user username;# same username as the user from the database I got the dump from.  
grant all privileges on database_name.* to username@"localhost" identified by 'password';  
exit mysql

then attempt to import the sql dump again:

$ mysql -u username -p database_name < dumpfile.sql  
Enter password:  
ERROR 1007 (HY000) at line 21: Can't create database 'database_name'; database exists

How am I supposed to import the SQL dumpfile?

Max Kielland · Answer 1 · 2013-12-30T14:59:43.663

52

This is a known bug at MySQL.

bug 42996
bug 40477

As you can see this has been a known issue since 2008 and they have not fixed it yet!!!

WORK AROUND
You first need to create the database to import. It doesn't need any tables. Then you can import your database.

first start your MySQL command line (apply username and password if you need to)
C:\>mysql -u user -p

Create your database and exit

mysql> DROP DATABASE database;  
mysql> CREATE DATABASE database;  
mysql> Exit

Import your selected database from the dump file
C:\>mysql -u user -p -h localhost -D database -o < dumpfile.sql

You can replace localhost with an IP or domain for any MySQL server you want to import to. The reason for the DROP command in the mysql prompt is to be sure we start with an empty and clean database.

edited Dec 30 '13 at 14:59

answered Jun 13 '10 at 23:55

Max Kielland

5,627
9
60
95

2

I appreciate the answer. It's now 2012 and it appears that the bug is still there, for I used your answer to get my wordpress databae restored to a new server (note however, your MySQL commends are missing the ending ';'. ;-) – Karl Jul 30 '12 at 21:10
2

2022 and bug still around ;) – João Pimentel Ferreira Mar 28 '22 at 15:12
this is a real pain in the ass when writing phing targets. not everybody works just on the command line :/ – clockw0rk Sep 07 '22 at 09:15

score 41 · Accepted Answer · answered May 13 '09 at 17:37

41

Open the sql file and comment out the line that tries to create the existing database.

answered May 13 '09 at 17:37

Bjorn

69,215
39
136
164

9

@BryanWheelock I think a better (industrializable) option would be not to select the database in your command, so it becomes: "mysql -u username -p -h localhost < dumpfile.sql". The database_name is an optional parameter. If your dump file contains a "create database" instruction, then it will be created. – toni07 May 15 '15 at 10:08
Just to elaborate on this a bit, the very first line in my *.sql file had this on it: CREATE DATABASE IF NOT EXISTS \`database_name\` /\*!40100 DEFAULT CHARACTER SET latin1 \*/; Changing this to (note /\* at start): /\*CREATE DATABASE IF NOT EXISTS \`database_name\` /\*!40100 DEFAULT CHARACTER SET latin1 \*/; effectively comments out the command and IMPORT should work. Just remember to be active inside the database that you want the IMPORT to insert the tables into, otherwise you might be in for a mess! – ntk4 Jun 13 '19 at 22:51

score 12 · Answer 3 · answered May 13 '09 at 17:43

12

It sounds like your database dump includes the information for creating the database. So don't give the MySQL command line a database name. It will create the new database and switch to it to do the import.

answered May 13 '09 at 17:43

Paul Tomblin

179,021
58
319
408

score 4 · Answer 4 · edited May 26 '13 at 17:19

4

If you create your database in direct admin or cpanel, you must edit your sql with notepad or notepad++ and change CREATE DATABASE command to CREATE DATABASE IF NOT EXISTS in line22

edited May 26 '13 at 17:19

Taryn

242,637
56
362
405

answered Mar 30 '13 at 13:31

MaysaM

41
1

score 0 · Answer 5 · answered Apr 12 '17 at 09:52

0

I create the database myself using the command line. Then try to import again, it works.

answered Apr 12 '17 at 09:52

wmlhust

141
1
4

Error importing SQL dump into MySQL: Unknown database / Can't create database

5 Answers5

Linked