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

Question

I am fairly new to MySQL and I am getting a pretty interesting error on which I cannot find any help via google and the stackoverflow search   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   As I said  I m new to the subject and pretty much clueless  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

User · Answer

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.

User · Answer

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

User · Answer

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  gt  drop table innodb table  ERROR 1051  42S02   Unknown table  database name innodb table   MariaDB  database name  gt  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  gt  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  gt  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  gt  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  gt  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

User · Answer

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 ibd table3 idb  lt - 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt dbname gt  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

User · Answer

In my case the only work solution was    CREATE TABLE bad table ENGINE MyISAM     rm bad table ibd DROP TABLE bad table

User · Answer

You can run the following query as a mysql root user  drop tablespace  tableName

User · Answer

Had exactly the same problem  I d brew added mysql 5 6  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 mysql 5 6 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  lt string gt --port 3306 lt  string gt  to the  plist array  but you could also specify port 3306 in your my cnf  Run brew services stop mysql 5 6 make your changes then brew services start mysql 5 6

User · Answer

The only way it worked for me was    Create a similar table Copy the  frm and  idb files of the new similar table to the name of the corrupt table  Fix permissions Restart MariaDB Drop the corrupt table

User · Answer

Here is the solution steps    backup your database  structure with drop option and data  stop mysql engine service remove the database directory manually from inside mysql data start mysql engine create new database with any name different from your corrupted database 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  rename the database to the old corrupted database restore your backup and your table will be working fine

User · Answer

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

User · Answer

In my case   First remove tableName ibd in your database directory from Mysql and second run   ALTER TABLE tableName DISCARD TABLESPACE  DROP TABLE tableName

User · Answer

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

User · Answer

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

User · Answer

I had to locate my MySQL data directory   SHOW VARIABLES WHERE Variable Name LIKE   dir   Then force remove that database   sudo rm -rf

User · Answer

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

User · Answer

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

User · Answer

Please DISCARD the tablespace before IMPORT  I got same issue solution is below    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   Again you have to create new database and import your old sql file  It will be work   Thanks

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to macos

Examples related to navicat

Examples related to tablespace