mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'

Question

I run a daily backup mysqldump backup of the production database (mysql version 5.1.66):

mysqldump --user=username --password=secret -C -e --create-options --hex-blob --net_buffer_length=5000 databasename > file 

I also do a daily restore of that database on my development machine (mysql version 5.6.12)

mysql --user=username --password=secret databasename < file 

I get the error: ERROR 1813 (HY000) at line 25: Tablespace for table 'databasename.tablename' exists. Please DISCARD the tablespace before IMPORT.

My reading indicates this is because the mysql innodb database requires the command:

 ALTER TABLE tbl_name DISCARD TABLESPACE; 

to be run before the table is dropped -- it seems that dropping the table isn't sufficient to get rid of its indexes. (my development server uses the innodb_file_per_table option)

I don't want to use 'replace' option because i could potentially have data in my development database that was deleted on the production database.

btw after the error the tables are not readable, but restarting mysqld fixes it.

So the question is, is there any mysql dump option that will help fix this issue, or is there another way to import the data that will prevent the error?

thanks in advance for reading.

Answer 1

Sounds like you have a tablename.ibd but no tablename.frm.

To check:

1. cd to your mysql data directory then the database name.

   cd /var/lib/mysql/database_name

2. Search for the table name that is giving the error.

   ls tablename.*

   You should see two files:

    tablename.ibd tablename.frm 

   But I'm guessing you don't and only see

   tablename.ibd

To fix you have a few options:

1. Add the follow to mysqldump, which will cause the database to be dropped, cleaning up data directory, before restore.

   --add-drop-database

2. Copy the tablename.frm from prod over to dev and then issue a delete table statement.

Also:

• No need to use net_buffer_length=5000 when you're dumping to a file on localhost.
• Other backup solutions - Percona Xtrabackup

Answer 2

I found the easiest way to skip this problem was to manually edit phpmyadmin database dump and edit/change the table that had problems to something else than INNODB. I changed the problem table to ENGINE=MyISAM and voila. Import worked.

CREATE TABLE IF NOT EXISTS `home3_acymailing_tag` (     `tagid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,     `name` varchar(250) NOT NULL,     `userid` int(10) unsigned DEFAULT NULL,     PRIMARY KEY (`tagid`),     KEY `useridindex` (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;