Bobcares

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

by | Jul 12, 2018

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.

 

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

27 Comments

  1. Elene

    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.

    Reply
    • Visakh S

      Thank you Elene. 🙂

      Reply
    • Bobby Zopfan

      What should be turned off to avoid ‘FK Constraints’ errors?

      Reply
      • Sijin George

        Hello Bobby,
        Toggling the value of ‘foreign_key_checks’ should help to adjust foreign key constraint checks in MySQL.

        Reply
  2. Facundo

    Thank you so much for sharing this info. It was very helpful to me.

    Reply
  3. RENATO SALVADOR

    I got lost in step 4.
    could explain better how to do.

    Reply
    • Sijin George

      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).

      Reply
  4. Wayne Wood

    You say we must “assign mysql:mysql ownership”, How do I do that?

    Thanks

    Reply
    • Visakh S

      You can use the command:
      chown mysql:mysql

      If you need help, chat with a sysadmin. Chat button at right bottom.

      Reply
  5. Felipe

    Me ajudou de mais, muito obrigado

    Reply
  6. Luca

    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

    Reply
    • Sijin George

      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).

      Reply
  7. Auwalu Hamza

    Thank you very much for the help. This really helps

    Reply
  8. Nuriel Simchi

    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

    Reply
    • Maheen Aboobakkar

      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).

      Reply
  9. Bob Nitrio

    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.

    Reply
  10. Soheil Rahsaz

    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

    Reply
  11. Ali

    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.

    Reply
    • Maheen Aboobakkar

      Hello Ali,

      We’re glad you were able to fix the issue by following the instructions in this article.

      Reply
  12. Shahbaz

    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.

    Reply
    • Hiba Razak

      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).

      Reply
  13. danis

    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

    Reply
    • Hiba Razak

      Hi,
      Please contact our support team via live chat(click on the icon at right-bottom).

      Reply
  14. Ishaan Shah

    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?

    Reply
    • Hiba Razak

      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).

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.