How to fix error in Mysql “tablespace is missing for table XXXXX”

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:

1. Take a backup of all .ibd and .frm files.
2. Create the database and tables using the SQL queries from the web app installation script.
3. Delete the newly created files using the DISCARD statement. Eg. ALTER TABLE newdb.table1 DISCARD TABLESPACE;
4. Then copy all the .ibd and .frm files from backup to the database folder, and assign mysql:mysql ownership.
5. 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.