Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

Question

I am running a local server of MySQL 5.6.10 on MacOS 10.8.3 and manage my database via Navicat essentials for MySQL.

The error I get is that after running and managing my database just fine for a couple of days/weeks something triggers to (it appears incompletely) delete some of the tables I created using queries from within Navicat.

When I try to run queries using these tables, Navicat then warns me that the particular table does not exist. So far so good - here comes the good part:

When I try to CREATE the table, e.g. named "temp", that was previously there, I get the following error message:

Error : Tablespace for table '`database`.`temp`' exists. Please DISCARD the tablespace before IMPORT.

However, if I try to drop the table, or try to discard the tablespace for this table, using

DROP TABLE temp;
ALTER TABLE temp DISCARD TABLESPACE;

I get the following error messages:

Error : Unknown table 'database.temp'
Error : Table 'database.temp' doesn't exist

So that means that I am advised to discard the table space but when I try to do so the table does not exist. Is it possible that there is some type of remnant of this table at a different place where the DISCARD query isn't checking? And does anybody have an idea what could trigger all that - completely randomly as it seems?

I suspect that re-booting my laptop, i.e. resetting my local MySQL server, or maybe user permission rights might have to do with it, but I am just hypothesizing here.

Hydric

You can check some solutions for this kind of error. codespeaker.com/laravel-framework/… – Commines

Answer 1

A little late here but generally I've seen this problem occur when you get a 'tablespace full' error when running in a 'innodb_file_per_table' mode. Without going into too much detail (more here), the database server's tablespace is defined by the innodb_data_file_path setting and by default is rather small. Even made larger, the 'tablespace full' can still occur with larger queries and such (lots of non-table 'stuff' is stored in there, undo logs, caches, etc...).

Anyways, I found that if you look in the OS directory where the files-per-table are stored, /var/lib/mysql by default on OSX, /usr/local/var/mysql with homebrew iirc, you'll find an orphaned tablename.ibd file without it's normal companion tablename.frm file. If you move that .ibd file to a safe temporary location (just to be safe) that should fix the problem.

$ ls /var/lib/mysql

table1.frm
table1.idb
table2.frm
table2.idb
table3.idb <- problem table, no table3.frm
table4.frm
table4.idb

$ mkdir /tmp/mysql_orphans
$ mv /var/lib/mysql/table3.ibd /tmp/mysql_orphans/

One caveat though, make sure what ever is causing the problem originally, e.g. long running query, locked table, etc... has been cleared. Otherwise you just end up with another orphaned .ibd file when you try a second time.

Cimah

My MySQL-Data directory was on OS X Yosemite was stored in /usr/local/mysql/data instead of /var/lib/mysql/. Otherwise perfectly solved the problem. – Bisutun

/usr/local/var/mysql on Mavericks – Merbromin

in my case it did not work...I deleted the orphaned idb file...and when I went the recreate the table with the exact same name I got a message saying that the table already exists(for which I deleted the .idb file)...after the above action a new orphaned .idb file was created in the dir...very strange...I really do not know what to assume. – Alienist

I have the same problem as Dimitris - I had to create a dump from the database, drop the database and restore it from the dump. – Cadman

@Gerfried This worked for me so long as I stopped & started the MySQL process after deleting the file. – Gintz

@DimitrisPapageorgiou This worked for me so long as I stopped & started the MySQL process after deleting the file. – Gintz

+1 answer and +1 question - Thank you: I found this answer to work (i.e. the error not now seen) with the following modification: I had to append the name of the db (e.g. mydatabase) to the path to see the contents, like so: ls /var/lib/mysql/mydatabase. Just a point of curiousity: when I do a drop database, when I go and look at the contents of the /var/lib/mysql/ path, my database is still there with the tables - I would have thought it would have all gone. Anyway as said the solution works. Thank you. – Villegas

@Garfied solution works great without needing to restart mysql service. Also I had to remove database folder manually. Thanks – Anchorage

FLUSH TABLES might help if you still get errors, and if you get errors then the .ibd file might be re-created. – Sikh

Answer 2

Xampp and Mamp Users

Had the same error while importing a database (after emptying it) trough MySQL. I found that i had a tablename.ibd file left while all others were deleted. I deleted it manually from mysql/data/database_name and the error was gone.

Attempt

Did this answer help people who don't use XAMPP? – Attempt

thumbs up from me ! worked well. However, allow me a slightly update to the path of the folder for me (got confused trying to find it) : /Applications/XAMPP/xamppfiles/var/mysql – Obverse

using this developed a 168 error from storage engine in linux mint, not using Xampp nor Mamp (no criticism, just informing) – Yeeyegg

works! i deleted broken one .ibd file and then table can be created again. Ubuntu 16, mariadb – Fadiman

Same goes for Docker (if docker crashes or host restarts, you might have dead date inside your sync folder that creates this error) – Peabody

Answer 3

If you get the .idb recreated again after you delete it, then read this answer.

This how it worked with me. I had the .idb file without it's corresponding .frm and whenever I delete the .idb file, the database recreate it again. and I found the solution in one line in MySQL documentation (Tablespace Does Not Exist part)

1- Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

2- Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

I copied another table .frm file and name it like my missing table, then make a normal drop table query and voila, it worked and the table is dropped normally!

my system is XAMPP on windows MariaDB v 10.1.8

Telegonus

In case this wasn't obvious to anyone else: when you have created the .frm file, and drop the table, the .idb file must be deleted. – Apoloniaapolune

Can confirm, the steps should be: 1. delete mysql/path/table_name.idb 2. add table_name.frm 3. DROP table_name – Homesteader

This worked for me. Thanks. I got this error while deleting a FK and immediatly after it, I stopped mysql. I think this my table def data corrupted. – Illstarred

remember to restart mysql after putting file then try to drop it – Downstroke

In mysql > data> mysql, there is a .frm file I Need. Can I copy this one? – Thermel

After copying the .frm file to my Folder and restarting mysql, I see the table. If I click on it, I get Incorrect format Parameter. But it worked! – Thermel

Answer 4

For WAMP [Windows 7 Ultimate x64-bit] Users:

I agree with what DangerDave said and so I'm making an answer available for WAMP Users.

Note: First of all, you have to go to your ..\WAMP\Bin\MySQL\MySQL[Your MySQL Version]\Data folder.

Now, you'll see folders of all your databases

• Double-click the folder of the database which has the offending table to open it
• There shouldn't be a file [Your offending MySQL table name].frm, instead there should be a file [Your offending MySQL table name].ibd
• Delete the [Your offending MySQL table name].ibd
• Then, delete it from the Recycle Bin too
• Then run your MySQL query on the database and you're done

Jeggar

Answer 5

In my case:

First remove tableName.ibd in your database directory from Mysql and second run:

ALTER TABLE tableName DISCARD TABLESPACE;
DROP TABLE tableName;

Fino

Thanks, In my case, I have 1) stoped the database server (service mysql stop) 2) removed idb file 3) started database server (service mysql start) Didn't run alter and drop queries – Misalliance

Your database directory in Windows is in C:\ProgramData\MySQL by default – Unofficial

No, the database directory is NOT necessarily in C:\ProgramData . If the OP used XAMPP, it's probably, by default, under where he told the XAMPP install to put the xampp folder. Mine is 'xampp' under a 'D:' directory. This is true regardless of whether you're using Windows 10, Windows 7, or whatever. – Darrickdarrill

Answer 6

This is exactly what i did in mariadb 10.2.16 on fedora when i had a table that showed exactly the same errors in the log file i suppose...

2018-07-11  9:43:58 140323764213504 [Note] InnoDB: The file './database_name/innodb_table.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. You can resolve the problem by removing the file.
2018-07-11  9:44:29 140323764213504 [Warning] InnoDB: Tablespace 'database_name/innodb_table' exists in the cache with id 2836 != 2918

your mileage and errors may vary but the main one i assume is

...already exists though the corresponding table did not exist in the InnoDB data dictionary...

with drop table not working as well as alter table...

MariaDB [database_name]> drop table innodb_table;
ERROR 1051 (42S02): Unknown table 'database_name.innodb_table'

MariaDB [database_name]> alter table innodb_table discard tablespace;
ERROR 1146 (42S02): Table 'database_name.innodb_table' doesn't exist

create table also fails like so:

MariaDB [database_name]> create table  innodb_table(`id` int(10) unsigned NOT NULL);
ERROR 1813 (HY000): Tablespace for table '`database_name`.`innodb_table`' exists. Please DISCARD the tablespace before IMPORT

in order to fix this, what i did was first

create table  innodb_table2(`id` int(10) unsigned NOT NULL);
Query OK, 0 rows affected (0.07 sec)

then in the /var/lib/mysql/database_name directory i did the following as root acknowledging the overwriting of innodb_table.ibd causing us issues

cp -a innodb_table2.frm innodb_table.frm
cp -a innodb_table2.ibd innodb_table.ibd
systemctl restart mariadb

then back in the mysql console i issued a successful drop command on both tables

MariaDB [database_name]> drop table innodb_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: database_name

Query OK, 0 rows affected (0.08 sec)

MariaDB [database_name]> drop table innodb_table2;
Query OK, 0 rows affected (0.25 sec)

and everything is now all square and i can recreate the one single table...

MariaDB [database_name]> create table  innodb_table (`id` int(10) unsigned NOT NULL);
Query OK, 0 rows affected (0.08 sec)

EDIT: I was going to add in a

restorecon -Rv /var/lib/mysql/database_name 

command after the copying of the database to get all selinux contexts the way they should be, even though we are deleting them from the database almost immediately, but in the alternative you could just add the --archive or -a option to the two cp commands, so yes actually the archive option shortens this:

cp innodb_table2.frm innodb_table.frm
cp innodb_table2.ibd innodb_table.ibd
chown mysql:mysql innodb_table.frm innodb_table.ibd
chmod 660 innodb_table.frm innodb_table.ibd
restorecon -Rv /var/lib/mysql/database_name
systemctl restart mariadb

to just the following which i think is better and it keeps the selinux context that is set for the already made table.

cp -a innodb_table2.frm innodb_table.frm
cp -a innodb_table2.ibd innodb_table.ibd
systemctl restart mariadb

I have replaced the above longer list of commands for the shorter list which could be shortened still with an *

Recommend

This worked well for me on CentOS MariaDB 10.2.31. I was looking for a solution that did not require restart of the MySQL service and this was it. The key is creating the the set of clean innodb_table2 files (innodb_table2.frm and innodb_table2.ibd) and placing both of them over the innodb_table files. – Boozy

This worked for me also on CentOS 10.1.48-MariaDB – Loop

My error leading to the duplication of the problem: Status: "InnoDB: Error: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 203534 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace mysql/innodb_table_stats2 which uses space ID: 203534 at filepath: ./mysql/innodb_table_stats2.ibd" – Marianomaribel

Answer 7

In my case the only work solution was:

1. CREATE TABLE bad_table ENGINE=MyISAM ...
2. rm bad_table.ibd
3. DROP TABLE bad_table

Miscreated

Worked for me! [ERROR] InnoDB: The file './dbname/tablename.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './dbname/tablename.ibd' under the 'datadir' of MySQL. – Kegan

Can't create table as Tablespace exists. – Clyburn

it's not work for me. ibd file keep appering after i want to recreate table with same engine. – Paranoid

Answer 8

Deleting/Moving tablename.ibd sure did not work for me.

How I solved it

Since I was going to delete the corrupted and non existing table, I took a backup of the other tables by going to phpmyadmin->database->export->selected tables to backup->export(as .sql).

After that I selected the database icon next to database name and then dropped it. Created a new database. Select your new database->import-> Select the file you downloaded earlier->click import. Now I have my old working tables and have the corrupted table deleted. Now I just create the table that was throwing the error.

Likely I had an earlier backup of the corrupted table.

Evangelical

Answer 9

Solution

However, the easier option is this: restart MySQL, then do the same four steps as follows:

1) created a dummy table in the database;
2) discarded its tablespace;
3) moved the .ibd file into the database folder on the system;
4) attached the tablespace back to the table

This way, the tablespace id on the data dictionary and the file matched; thus importing the tablespace succeeded.

This can give you greater confidence in dealing with some of the InnoDB "gotcha's" during the recovery process or even file transfers.

ref

Sanyu

This is not a stand-alone answer. – Fortepiano

Answer 10

I got the same error running it on wampserver while trying to create a users table. I found a users.ibd file and after I deleted this file, I ran the migrate command again and it worked. The file on my windows machine was located in wamp/bin/mysql/mysql5.6.12/data/myproject.

Gorlovka

Answer 11

Had this issue several times. If you have a large DB and want to try avoiding backup/restore (with added missing table), try few times back and forth:

DROP TABLE my_table;

ALTER TABLE my_table DISCARD TABLESPACE;

-and-

rm my_table.ibd (orphan w/o corresponding my_table.frm) located in /var/lib/mysql/my_db/ directory

-and then-

CREATE TABLE IF NOT EXISTS my_table (...)

Politics

Answer 12

Here is the solution steps:

1. backup your database (structure with drop option and data)
2. stop mysql engine service
3. remove the database directory manually from inside mysql/data
4. start mysql engine
5. create new database with any name different from your corrupted database
6. create single table with the name of the corrupted table inside the new database (this is the secret). and it is better to create the table with exactly the same structure.
7. rename the database to the old corrupted database
8. restore your backup and your table will be working fine.

Leila

Answer 13

Had exactly the same problem; I'd brew added [email protected] (after previously having 5.5).

The brew defaults for 5.6 are innodb_file_per_table=1 whereas in 5.5 they're innodb_file_per_table=0.

Your existing ibdata1 file (the combined innodb data) will still have references to the tables you're trying to create/drop. Either change innodb_file_per_table back to 0, or delete the ibdata1 data file (this will lose you all your data, so make sure you mysqldump it first or already have an .sql dump).

The other brew [email protected] default that bit me was the lack of a port, so networking was defaulting to unix sockets, and the mysql client kept reporting:

ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

I added <string>--port=3306</string> to the .plist array, but you could also specify port=3306 in your my.cnf

Run brew services stop [email protected] make your changes then brew services start [email protected]

Berserk

Setting innodb_file_per_table to 0 and restarting, after creating alternative tables and copying their .ibd and .frm on top of my corrupt table files, then going back to innodb_file_per_table = 1 solved the issue partially (now the alternative tables make MariaDB complain about tablespace). – Marianomaribel

Answer 14

I had the same issue. I renamed the database name & import it. Then it works.

Make it simple: Just Rename the Database name. That's it.

Problem is the existing database name has some entries in your folder. That's the problem. Either you need to remove all those related entries or Just Make it simple to rename the database

Unbuckle

"Just rename the database name"... what commands did you use to do that? – Vibrations

Yeah, just create a one more database and import the DB. - use the New DB. – Unbuckle

please show the command line rather than just saying: "Make it simple: Just Rename the Database name" – Retrogression

manually you can change the database name. I tried with some other methods. but it didn't work. so, i created the new database & connect with my existing application. That's it. it's a manual process only. – Unbuckle

Answer 15

Open the directory where xampp is installed, and open this path for example:

C:/xampp/mysql/data/database_name

Select the folder (it's like a database name) of the tablespace that you want to remove, then delete the file with extension idb. Delete only the files corresponding to the tables that you want to create/remove.

Cultrate

Answer 16

Trying to drop the tablespace can give you other errors. For me, I got the following error:

DROP TABLESPACE `tablename`

Error Code: 1478. Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' 

My solution was to drop the database. This will remove any tablespaces related to it and allow you to create the tables again.

Messidor

Unfortunately that's like saying 'I have a screw, and using a hammer returns this error, so my solution was to drop a boulder on it'. The real value would be figuring out how to fix this one table without nuking the entire database. – Mezzorilievo

Doh! I was hoping this would be an alternative solution to my question (and I posted it as an answer), but I'm already halfway through the process of renaming tables/dropping the database. I kind of hate InnoDB now. – Carangid

But I nuked the database, recreated it, and still have this problem! – Rigatoni

@Rigatoni did you restart the server ? – Messidor

I think I'll ask a separate question, @Aris. In my case, it's on an Ubuntu desktop. I've restarted not only MySQL, but the whole machine, several times. Also deleted the database folder by hand with an rm -r. It's irritating, but nor showstopping. – Rigatoni

Answer 17

If you have a another server with a good version of the same table you can make a copy(table_copy), transfer the table_copy to the problem server. Then delete the problem table and rename table_copy to table.

Exemplary

Answer 18

This error occurs when you suspend some functions. Like running the query below with incorrect foreign key.

set foreign_key_checks=0

Yangyangtze

Answer 19

For me it helped just go to MYSQL DATA directory under /var/lib/mysql/{db_name} (linux) and drop {table_name}.ibd file which was the same as folder name.

University

Answer 20

Thank you #DangerDave this solved my problem on Magento 2, and this is how I did

I am on a vps

root@myvps [~]# cd /var/lib/mysql/mydatabasename/

root@myvps [~]# ls

check for tables with no .frm fie (only .idb) and delete them,

rm customer_grid_flat.ibd

System will regenerate tables after running index:reindex command

Hygienics

Answer 21

I only delete my old DB located in my localhost directly from wamp, Stop all services, Go to wamp/bin/mysql/mysql[version]/data and I found the DB with problemas, I delete it and start again wamp all services, create again your database and it is done, Now you can import your tables,

Glottis

Answer 22

The way that I found to "solve" this problem is fairly annoying, but there is a script that handles it.

Essentially, you need the ibdata1 and ib_logfile* files to go away (they contain the mappings of foreign keys, among other things). The only safe way to do this is to export all of your databases, stop mysql, remove the files, start mysql, and then import the files.

The script that helps solve this problem is https://github.com/uberhacker/shrink-ibdata1, even though the stated purpose of this script is different, it does solve the problem.

Denims

Answer 23

You can run the following query as a mysql root user

drop tablespace `tableName`

Incunabula

If it's MariaDb, as@avibrazil referenced, you CANNOT do a 'drop tablespace tablename'. See mariadb.com/kb/en/drop-tablespace ---> "The DROP TABLESPACE statement is not supported by MariaDB." – Darrickdarrill

Post is about MySQL 5.6 not MariaDB. – Sakai

Answer 24

The only way it worked for me was:

1. Create a similar table
2. Copy the .frm and .idb files of the new similar table to the name of the corrupt table.
3. Fix permissions
4. Restart MariaDB
5. Drop the corrupt table

Became

Answer 25

In case of Homebrew, the directory for data files is /usr/local/var/mysql

to see which my.cnf file is used here are the search locations from my environment /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

So to clean up this error I did the following

mysqladmin -u root shutdown
rm /usr/local/var/mysql/<dbname>/problemtablename.ibd

Note: in my case i didnt care about the data as it is dev setup, probably happened because i restored my laptop with timemachine

Peppy

Answer 26

if you have this problem and you don't have another option change the engine to any other engine like 'myisam', then try to create the table.

disclaimer: it is not the valid answer as you may have foreign key constraints which will not be supported by another storage engine. Every storage engine has their own specialty to store and access data, these points also to be taken in account.

Cockneyfy

Answer 27

Please DISCARD the tablespace before IMPORT

I got same issue solution is below

1. First you have to drop your database name. if your database is not deleting you have flow me. For Windows system your directory will be C:/xampp/mysql/data/yourdabasefolder remove "yourdabasefolder"

2. Again you have to create new database and import your old sql file. It will be work

Thanks

Heartless

Answer 28

I had to locate my MySQL data directory:

SHOW VARIABLES WHERE Variable_Name LIKE "%dir"

Then force remove that database:

sudo rm -rf

Dual