Here at Bobcares, our Support Engineers monitor & maintain servers of web hosts, digital marketers and other online businesses.
A vast majority of these servers use MySQL as the database server, and a common error seen in them is:
1812 Tablespace is missing for table XXXX
What is MySQL error “Tablespace is missing for table”?
MySQL stores all data in files with the extensions .ibd (for InnoDB tables) and .MYD (for MyISAM tables).
These files are the “space” in which table data is stored. Unsurprisingly, these files are referred to as “tablespaces”.
We’ve seen many cases where these files go missing. Usually it happens due to errors in server migrations, disk issues or even administration errors.
When MySQL is unable to access a file to query a table, it shows the error:
1812 Tablespace is missing for table XXXX
How to fix error “1812 Tablespace is missing for table XXXX”
There are broadly three ways in which this error can happen:
- Table files have the wrong ownership/permissions
- The table file is misplaced
- The data file is corrupted or deleted
When we see this error, we take a quick look at the database folder for the table’s data file (with the extension .ibd).
If it is present there, we check the permissions and fix it if it’s wrong. In many cases we’ve found this to be the issue.
However, if the file is not present there, we’ll then look for backups or try to get it from the source server (in case of migrations).
Now, we’ve seen two ways in which backups are stored:
- .ibd files – This is the original format of InnoDB files. This is easy to restore.
- .sql dump files – This is the database stored as SQL queries. These are harder to restore.
If the backups are stored as .ibd files, we copy the table files into the database folder and set the right permissions. Everything usually works fine from that point.
On the other hand, if it’s in the SQL format, we drop the current database, and restore the full database from backup. This method will create fresh entries in the System tables, and build proper linkages.
Special case : Fixing corrupted system table
Loosely related to this error is a situation when the table files are present in the database folder, but MySQL refuses to see it.
This happens when the system table is restored from a backup that doesn’t contain the table information, or the system table space is corrupted in some way.
The best way to solve this is to restore the database with an SQL dump.
However, if there’s no SQL dump, we’ve to create the database and import the database files (aka tablespace).
The steps loosely follow this sequence:
- Take a backup of all
.ibdand.frmfiles. - Create the database and tables using the SQL queries from the web app installation script.
- Delete the newly created files using the DISCARD statement. Eg.
ALTER TABLE newdb.table1 DISCARD TABLESPACE; - Then copy all the
.ibdand.frmfiles from backup to the database folder, and assignmysql:mysqlownership. - Ask MySQL to accept the new files using the IMPORT statement. Eg.
ALTER TABLE newdb.table1 IMPORT TABLESPACE;
This should get the database back online.
Of course, there could complications in table creation and import. If you need help getting this done right, click here to talk to our MySQL experts. We are online 24/7 and can help you in a few minutes.
Conclusion
“1812 Tablespace is missing for table” is a common error in MySQL servers that usually comes up after a server migration, hard disk error, or a new app setup. Today we’ve seen why this error happens and what do here at Bobcares to fix it.
This saved my life. Thank you very much. As an extra note, if you have FK Constraints you will need to turn them off before running the queries and turn it back on afterwards.
Thank you Elene. 🙂
What should be turned off to avoid ‘FK Constraints’ errors?
Hello Bobby,
Toggling the value of ‘foreign_key_checks’ should help to adjust foreign key constraint checks in MySQL.
Thank you so much for sharing this info. It was very helpful to me.
I got lost in step 4.
could explain better how to do.
Hello Renato,
Step 4 involves using the ‘cp’ command for copying and ‘chown’ command for setting the ownership. If you need further help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).
really you saved my life.
I restored all tables and data with this tutorial.
but I used an extra tool to generate table schema from “.frm” files.
https://dbsake.readthedocs.io/en/latest/commands/frmdump.html
hope helps
You say we must “assign mysql:mysql ownership”, How do I do that?
Thanks
You can use the command:
chown mysql:mysqlIf you need help, chat with a sysadmin. Chat button at right bottom.
Me ajudou de mais, muito obrigado
Hi, i’ve found this post, it’s amazing!! I’ve recover my tables, but now i’ve this problem on datetime (look link), can you help me?
thanks
Hello Luca,
We have to check the server settings to find the reason for the error in the image link. We’ll be happy to talk to you on chat (click on the icon at right-bottom).
Thank you very much for the help. This really helps
How can I delete table data from file (I assume it’s on .ibd file) without corrupt the tablespace?
I need to do it that way because mysql server stopped due to no space on disk.
Thanks in advance
If you want to avoid data loss or downtime, you must not delete a .ibd file. Your data and indexes are stored in these .ibd files.
Deleting such a file will, by definition, result in data loss, and it is also likely to result in downtime. Mysqldump can be used to create a backup. Y
ou can delete data after backing up the table associated with the .ibd file. TRUNCATE TABLE is the quickest way to accomplish this. This will clear the table of all rows and reset the auto-increment id. Because there is no rollback, ensure that you have backed up any data that you want to keep.
Or else you can purchase additional space in the database.
We’ll be happy to talk to you on chat if you have any further queries(click on the icon at right-bottom).
Thanks for this, it was a lifesaver.
I want to thank BobCares for posting the solution for Special case : Fixing corrupted system table. Armed with this procedure and some additional research on how to use MySQL Workbench, I was able to recover seven critical tables that I needed for a website under development that went sideways.
Thanks man
I did it by importing production server data into my local MySQL after my disk was damaged. out of 500 tables, only 4 of them failed to attach which is still ok.
The errors were:
Cannot reset LSNs in table “ : Data structure corruption
Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.
The second one seems to have a solution but I did not find any
Hi I am not a database administrator. 3 days ago my local xampp database server crashed. With the project I’ve been working on for 3 months and thousands of lines. this article helped me a lot. I am so happy that I came across this article today. I did the queries very fast with MySQL-Front. I reached the table structure with “Recovery Toolbox for MySql”.
Lots of love from Turkey. Happy new year too.
Hello Ali,
We’re glad you were able to fix the issue by following the instructions in this article.
I have everything fine, like I have .ibd file in my folder, permission is set and ownership is also mysql. But when I check the tablespace in mysql it gives NULL for both FILE_ID and FILE_TYPE in information_schema.FILES table.
I have tried everything but unable to solve the issue.
Hi Shahbaz,
Our Experts can help you with the issue, we’ll be happy to talk to you on chat (click on the icon at right-bottom).
Hi,
I’ve gone through all the process to restore tables but at the last part when I run ALTER TABLE database.table IMPORT TABLESPACE, I get the MySQL error #2006 – MySQL has gone away
Hi,
Please contact our support team via live chat(click on the icon at right-bottom).
Hi
Okay a big error, my hosting provider’s support team told me to do it and I didnt think twice, I did not know what the command does actually
[root@server sarva1fy_sarvada]# pwd
/var/lib/mysql/sarva1fy_sarvada
[root@server sarva1fy_sarvada]# du -csh * | grep G
1.9G log_url.ibd
2.8G log_url_info.ibd
2.9G log_visitor_info.ibd
1.2G report_event.ibd
1.7G report_viewed_product_index.ibd
13G total
I was facing some disk full issue, so to clean up these ibd files I was told to run
rm -rf report_viewed_product_index.ibd
and the same for other ibd files. Instead of TRUNCATE, they made me delete the files
Now my website is down with error
SQLSTATE[HY000]: General error: 1812 Tablespace is missing for table `sarva1fy_sarvada`.`log_visitor_info`
I dont have a recent database backup. These files should the be the standard ibd files for Magento, right? Can you help?
It seems that removing the InnoDB (.ibd) files without proper precautions has caused data loss and corruption in your database. The error message you’re seeing indicates that the tablespace is missing for the log_visitor_info table, which means that the corresponding InnoDB tablespace files have been deleted.
Unfortunately, if you don’t have a recent database backup, it may be challenging to recover the deleted data.
We’ll be happy to talk to you on chat if you want us to further check on the issue. (click on the icon at right-bottom).