Error: There is a table space for table xxx. Please OPEN the tablespace before IMPORT

Question

I am new to MySQL and I get a rather interesting error that I cannot find any help through google and search on the stack.

I run the local MySQL 5.6.10 server on MacOS 10.8.3 and manage my database using the basic Navicat tools for MySQL.

The error I get is that after starting and managing my database it just works fine for several days / weeks, something causes (it looks incomplete) to delete some of the tables that I created using queries from Navicat.

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

When I try to CREATE a table, for example. The "temp" that was previously there displays the following error message:

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

However, if I try to delete a table or try to drop the table space for this table using

 DROP TABLE temp; ALTER TABLE temp DISCARD TABLESPACE; 

The following error messages appear:

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

So this means that I am advised to drop the table space, but when I try to do this, the table does not exist. Is it possible that there is some remainder of this table elsewhere where the DISCARD request is not checked? And does anyone have an idea that can cause all this - completely by accident, as it seems?

As I said, I am new to this issue and almost do not know. I suspect rebooting my laptop, i.e. Rebooting my local MySQL server, or perhaps user permissions, may be relevant to this, but I'm just hypothesizing here.

Answer 1

A little late, but as a rule, I saw how this problem occurs when you get the "tablespace full" error when starting in the "innodb_file_per_table" mode. Without going into details (more details here ), the table space of the database server is determined by the innodb_data_file_path parameter and is pretty small by default. Even done more, the "table space is full" can still arise with large requests and such (it stores a lot of non-standard "things", rollback of logs, caches, etc.).

In any case, I found that if you look in the OS directory where the files are stored behind the table, / var / lib / mysql by default in OSX, / usr / local / var / mysql with homebrew iirc, you can find the lost tablename file .ibd without the usual tablename.frm file. If you move this .ibd file to a safe, temporary location (just to be safe), this should fix the problem.

 $ ls /var/lib/mysql table1.frm table1.idb table2.frm table2.ibd table3.idb <- problem table, no table3.frm table4.frm table4.idb $ mkdir /tmp/mysql_orphans $ mv /var/lib/mysql/table3.ibd /tmp/mysql_orphans/ 

Beware, however, be sure to have a problem initially, for example. long working request, locked table, etc ..... Otherwise, you just end up with another orphan .ibd file when you try a second time.

Answer 2

Xampp and Mamp Users

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

Answer 3

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

I agree with what DangerDave said, and therefore I am providing an answer for WAMP users .

Note. . First of all, you need to go to the folder .. \ WAMP \ Bin \ MySQL \ MySQL [your version of MySQL] \ .. p>

Now you will see the folders of all your databases

• Double-click the database folder that has the affected table to open it.
• There should not be a [Your offending MySQL table name].frm , instead there should be a [Your offending MySQL table name].ibd
• Delete [Your offending MySQL table name].ibd
• Then remove it from the trash.
• Then run your MySQL query in the database and you are done

Answer 4

If after uninstalling you create .idb again, read this answer.

This is how it works with me. I had a .idb file without it corresponding to .frm , and whenever I delete the .idb file, the database recreates it again. and I found the solution on one line in the MySQL documentation (the table space does not exist part)

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

2- Release DROP TABLE for the original table. This should successfully delete the table, and InnoDB should print a warning in the error log that the .ibd file is missing.

I copied another .frm table .frm and named it as my missing table, then made the usual request to delete the table and voila, it worked, and the table was deleted normally!

I have a XAMPP system on windows MariaDB v 10.1.8

Answer 5

In my case, the only solution was:

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

Answer 6

This is exactly what I did in mariadb 10.2.16 on fedora, when I had a table that showed 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 

mileage and errors may vary, but I assume that the main one is

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

working with a delete table does not work as well as changing a 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' does not exist 

creating a table also fails like this:

 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 

To fix this, first I did

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

then in the directory / var / lib / mysql / database_name I did the following as a root user, confirming the rewrite of innodb_table.ibd, which caused us problems

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

then in mysql console I executed a successful delete command for 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 now all the squares, and I can recreate one table ...

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

UPDATE: I was going to add in

 restorecon -Rv /var/lib/mysql/database_name 

Team

after copying the database to get all selinux contexts as they should be, even if we remove them from the database almost immediately, but as an alternative, you can simply add the --archive or -a option for 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 the next, which I think is better, and this preserves the Selinux context that is set for the already created table.

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

I replaced the above long list of commands with a shorter list that can be shortened with *

Answer 7

Decision

However, a simpler option is this: restart MySQL, then follow 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 

Thus, the table space identifier in the data dictionary and the file are the same; thus, importing the table space was successful.

This can give you great confidence in working with some of the “flaws” InnoDB during the recovery process or even when transferring files.

link

Answer 8

I got the same error as on wampserver server when trying to create user table. I found the users.ibd file, and after I deleted this file, I ran the migrate command again and it worked. The file on my Windows computer was located in the file wamp / bin / mysql / mysql5.6.12 / data / myproject.

Answer 9

Removing / moving tablename.ibd probably didn't work for me.

How i decided this

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

After that, I selected the database icon next to the database name and then lowered it. Created a new database. Select the new database-> import-> Select the file you downloaded earlier, → click on import. Now I have old working tables and the damaged table is deleted. Now I just create a table that throws an error.

I probably had an earlier backup of the damaged table.

Answer 10

Here are the solution steps:

• database backup (structure with drop option and data)
• stop mysql service
• remove database directory manually from mysql / data
• run mysql engine
• create a new database with any name other than the damaged database
• create a separate table with the name of the damaged table inside the new database (this is a secret). and it’s better to create a table with exactly the same structure.
• rename the database to the old damaged database
• restore the backup and your table will work fine.

Answer 11

This error occurs when some functions are paused. Similar to running the query below with the wrong foreign key.

 set foreign_key_checks=0 

Answer 12

In my case:

First remove tableName.ibd from the database directory from Mysql and then run again:

 ALTER TABLE tableName DISCARD TABLESPACE; DROP TABLE tableName; 

Answer 13

Trying to reset the table space may result in 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 delete any associated table spaces and allow you to create the tables again.

Answer 14

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

Answer 15

There was this question several times. If you have a large database and want to avoid backup / restore (with the missing table added), try back and forth several times:

TABLE DROP my_table;

ALTER TABLE my_table DISCARD TABLESPACE;

th -

rm my_table.ibd (an orphan without the corresponding my_table.frm) located in the directory / var / lib / mysql / my _db /

- and then -

CREATE TABLE IF NOT EXISTING my_table (...)

Answer 16

There was the same problem; I brewed added mysql@5.6 (after previously having 5.5).

The default value for innodb_file_per_table=1 : innodb_file_per_table=1 and in 5.5 - innodb_file_per_table=0 .

In your existing ibdata1 file ( ibdata1 merged data) there will still be ibdata1 links to the tables you are trying to create / delete. Either change innodb_file_per_table to 0, or delete the ibdata1 data file ( this will lose all your data, so be sure to run mysqldump or you have a dump.sql ).

Another mysql@5.6 default, which mysql@5.6 me, is the lack of a port, so network mysql@5.6 not the default for UNIX sockets, and the mysql client continued to report:

 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 can also specify port=3306 in my.cnf

Run brew services stop mysql@5.6 make changes, then brew services start mysql@5.6

Answer 17

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

Answer 18

I only delete my old DB located in my local host directly from wamp, Stop all services, Go to wamp / bin / mysql / mysql [version] / data and I found the database with problems, I delete it and run wamp again all services, create your database again, and everything will be done, now you can import your tables,

Answer 19

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

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

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

Answer 20

The only way this worked for me was:

1. Create a similar table
2. Copy the .frm and .idb files of the new similar table into the name of the damaged table.
3. Fix permissions
4. Restart MariaDB
5. Leave the spoiled table

Answer 21

if you have this problem and you have no other option, change the engine to any other engine, such as "myisam", and then try to create a table.

disclaimer: this is not a valid answer, as you may have foreign key restrictions that will not be supported by another storage engine. Each storage engine has its own specialty for storing and accessing data, these points should also be taken into account.

Answer 22

Please OPEN the tablespace before IMPORT

I have the same solution to the problem below

1. First you need to drop the name of your database. if your database is not deleted, you drowned me. For Windows, your directory will be C: / xampp / mysql / data / yourdabasefolder delete "yourdabasefolder"

2. Again you need to create a new database and import the old sql file. It will be work

thank

Answer 23

I had to find the MySQL data directory:

SHOW VARIABLES WHERE Variable_Name LIKE "% dir"

Then force delete this database:

sudo rm -rf

Answer 24

You can run the following query as root.

 drop tablespace 'tableName' 

Answer 25

• mysql -e "select @@ datadir"

• Go to the folder

• sudo rm -rf DB_NAME