mysql - 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