Manually importing a database is causing a "Table '' already exists" error

Symptoms

You are attempting to import a MySQL database manually, and you are getting errors regarding "Table '' already exists," whereas your newly created database is empty.

Description 

When manually importing a database, you would use a command via SSH or phpMyAdmin like this:

Command (as root):

mysql cptest_cptest1 < /backup/mysql/cptest.sql

You expect the command to put the contents of the .sql file into the 'cptest_cptest1' database.

However, you receive an error that a certain 'Table already exists'.

This can happen if the .sql files have commands in it that are changing the database location.

Workaround

Examine the .sql file with a text-based editor, and look for commands that look like this:

Example (not-working):

File: /backup/mysql/cptest.sql
Contents: 
--
-- Database: `cptest_another-database`
--
CREATE DATABASE IF NOT EXISTS `cptest_another-database` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `cptest_another-database`;

When you import the database, MySQL executes the MySQL commands, which changes which database to use.

In this case, the workaround would be to change the 'database names' inside the .sql file to match the database you want to create.

Example (fixed):

File: /backup/mysql/cptest.sql
Contents: 
--
-- Database: `cptest_cptest1`
--
CREATE DATABASE IF NOT EXISTS `cptest_cptest1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `cptest_cptest1`; 

MySQL will execute those MySQL commands and put the following data in the 'cpest_cptest1' database as intended.

We must urge caution when modifying backup .SQL files in this manner. Remember to use a text-based editor that doesn't add Windows metadata such as carriage return and a linefeed (often referred to as "CRLF"). And, keep an unmodified copy of the .SQL file someplace safe in case you need to start again.