Troubleshooting database import errors

MySQL databases can be exported into a single file, which you can then use to migrate the database to another hosting company.

Most often, this MySQL file contains a CREATE DATABASE and a USE statement. These statements are intended to automate the process of migrating the database to a new location. However, attempting to import your database to DreamHost with such a file will fail as you do not have CREATE DATABASE permissions. See this article for more information.

When you import a database with these statements via SSH or phpMyAdmin, the following error message appears:

ERROR 1044 (42000): Access denied for user 'your_user_name'@'yourhost/hostmask' to database 'newdbname'

This error message indicates that you don't have sufficient permissions to create the new database, which is caused by the CREATE DATABASE statement in the MySQL file being imported.

The only way to create a database at DreamHost is in the panel. Because of this, the CREATE DATABASE statement in your MySQL file is not needed.

The solution

To resolve this issue:

1. Open the MySQL file in a text editor (not a word processor, such as MS Word).
2. Delete the lines that begin with CREATE DATABASE and USE. For example:

   CREATE DATABASE `newdbname` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$ USE `newdbname`;

   and

   USE `newdbname`;

   • The exact text of the CREATE DATABASE statement differs depending on the database name and the character set used.
   • Make sure to delete everything from the beginning of the line through, including the semi-colon at the end of the line.
3. Once you have deleted these statements, save your edited MySQL file, and then try importing again.

If you restore a MySQL backup to a database under another user and have any 'views' in your database, you'll get an error similar to the following:

ERROR 1227 (42000) at line 694: Access denied; you need the SUPER privilege for this operation.

You must remove the DEFINER mentioned in the .sql file at the line specified in the error.