Table already exists error when trying to import sql file

Question

I am trying to upload a backup sql file through phpMyAdmin.

I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.

I get the following error message.

#1050 - Table '`db`.`t`' already exists 

Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?

I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.

I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?

Thanks in advance.

Edit : Added SQL code.

This is just a sample, the code is created by phpMyAdmin's export function

-- phpMyAdmin SQL Dump
-- version 4.1.4
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 03, 2014 at 07:24 PM
-- Server version: 5.6.15-log
-- PHP Version: 5.4.14
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `mbfour`
--
-- --------------------------------------------------------
--
-- Table structure for table `cars`
--
CREATE TABLE IF NOT EXISTS `cars` (
 `car_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `type` varchar(200) NOT NULL,
 `status` varchar(20) NOT NULL,
 `capacity` varchar(5) NOT NULL,
 PRIMARY KEY (`car_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `cars`
--
INSERT INTO `cars` (`car_id`, `type`, `status`, `capacity`) VALUES
(1, 'automatic', 'built', '4L'),
(2, 'automatic', 'in-production', '2L'),
(3, 'automatic', 'built', '2L'),
(4, 'automatic', 'in-production', '4L');
....
....

Much more sql data but seems pointless to post as it is all formatted the same.

• ek9: show sql code. please
• cecilli0n: @edvias.me added some sql from the import file.
• cecilli0n: I managed to fix it, it seemed creating the new blank db to import into with the same name as the db in the import file caused some issues, I don't know why. Choosing any other name for the new blank db seemed to work. Random.

Answer 1

this is a very old question, but maybe my answer will help someone. instead of using phpmyadmin try using mysqldump.

i'm not sure of what the technical difference is between exporting from phpmyadmin and using mysqldump, but mysqldump gives the desired behavior whereas phpmyadmin always gives a table already exists error when trying to import again.

so from the command line, but NOT in mysql you can do some variation of this: mysqldump -u root -p -h 127.0.0.1 dbname1 > /path/to/my/sql/file.sql

then import to a different version of your database using mysql like so: mysql -u root -p -h 127.0.0.1 dbname2 < /path/to/my/sql/file.sql

your hostname and username may vary obviously and if you are using xampp you will need to navigate to your lampp binaries /opt/lamp/bin and run it as ./mysqldump.

i use this to update a database i keep offline and do not have to mess around with renaming things. it simply overwrites everything and the existing database is now full of the new information with all the tables preserved.