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 XXXXWhat 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.
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”;
Последствия необъяснимой силы добавили мне гемороя.
Проблема – сервак дома стоит, на нем писал сайт. CMS-Wordpres.
И тут в один прекрасный день, после выходных захожу, а сайт выдает 404 ошибку.
При том 404 – вордпресовская, и в админку заходит, плагины видит.
я захожу в phpmyadmin, и только таблица wp-post выдает ошибку
#1812 – Tablespace is missing for table `name_base`.`wp_posts`.
затем я нажал на кнопку Структура, и получил ответ внизу страницы:
Notice in ./libraries/sql-parser/src/Parser.php#383
Trying to get property of non-objectBacktrace
./libraries/sql-parser/src/Parser.php#336: SqlParserParser->parse()
./libraries/controllers/TableStructureController.class.php#347: SqlParserParser->__construct(boolean false)
./tbl_structure.php#48: PMAControllersTableStructureController->indexAction()
и
Notice in ./libraries/controllers/TableStructureController.class.php#352
Undefined offset: 0Backtrace
./tbl_structure.php#48: PMAControllersTableStructureController->indexAction()
в окне операции
Notice in ./tbl_operations.php#419
Undefined offset: 0Backtrace
Гугление не дало положительного результата, т.к. не силен в англ. а на русском не могу найти.
Есть идеи?
One of our database table tblpages seems to be corrupted. When I select the table, its says
show full fields from `favc`.`tblpages`
Table 'tblpages' doesn't exist
So I thought repairing it will be possible. I run a repair on the table and got this unsuccessful message.
tblpages repair Warning InnoDB: Tablespace is missing for table 'tblpages'
tblpages repair Error Table 'tblpages' doesn't exist
tblpages repair status Operation failed
I don’t have any latest copy of this table.
Please help with any advise you may have. Thanks!
asked Jan 15, 2014 at 20:56
2
“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
in my situation was: 1 – wrong permission, after hard copy/
other solutions can be found here: https://bobcares.com/blog/mysql-tablespace-is-missing-for-table/
answered Aug 3, 2019 at 15:55
in my case I could simply fix the table-space-missing problem by:
cd /var/lib/mysql/psa/
cp -a ModuleSettings.ibd.bak ModuleSettings.ibd
psa.ModuleSettings was the table which gave me «#1812 table space is missing»-erros.
Warning: It’s possible you lose some newer data.
answered Feb 6, 2017 at 20:02
3
In this error case, first check if the tablespaces i.e the files that have the table data exists in the path
- In my case i had the tablespaces exist
$ cd {path_to_mysql_installation}/mysql/data/{db_name}/ MyTable1.ibd MyTable2.ibd
- Check Permission for the files
$ ls -l -rw-r-----@ 1 vivek 884470207 114688 Apr 10 08:40 MyTable1.ibd -rw-r-----@ 1 vivek 884470207 114688 Apr 10 08:40 MyTable2.ibd
(My permissions got modified, not sure how)
- Reset the permissions to the whole data directory
$ chmod -R 755 ../../data
Now tried accessing the data from mysql console and from java application, it worked perfectly fine.
Please note i’m answering only the permission problem, for file correction or missing check out the solutions here .
Cheers!
answered Apr 15, 2021 at 8:59
VivekVivek
1112 bronze badges
In my case I could fix it with
ALTER TABLE database.tablename IMPORT TABLESPACE
Where database is the database and tablename obviously the name of the table that had the missing Tablespace.
answered Oct 21, 2021 at 9:59
1
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 XXXXWhat 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.
By Mistake one of my developer delete the data folder “TEST2” from the MYSQL Database Server. It having MySQL datafiles. On connection with TEST2 database. I tired to fetch result and getting the following error:
Error
mysql> select * from test;
ERROR 1812 (HY000): Tablespace is missing for table `test2`.`test`.
On Checking, we found some one delete the TEST2 folder of MYSQL database.
Solution
We must have backup for restore, We have taken on Sunday, deletion of TEST2 happen on Monday.
C:Program FilesMySQLMySQL Server 8.0bin>mysqldump -u root -p --all-databases --single-transaction --master-data --flush-logs > D:backupSunday.sql
Note:
–Flush-logs change the log sequence before start for incomplete recovery.
–Single-transaction means take consistent backup
–master-data Help to note the log file name shown below:
CHANGE MASTER TO MASTER_LOG_FILE='IXC1-LP48ZJ622-bin.000008', MASTER_LOG_POS=155;
Recover Steps for TEST2 database
1. Drop the test2 database by login as root user in MySQL.
Drop database TEST2;
2. Create empty new database.
Create database test2;
3. Restore the TEST2 database from the scripts.
C:Program FilesMySQLMySQL Server 8.0bin>mysql -u root -p test2 < D:backupsunday.sql
Enter password: *****
4. Restore the backup from sunday to monday by logging information.
Note: Check created date on windows machine.
mysql> SHOW BINARY LOGS;
+-----------------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +-----------------------------+-----------+-----------+ | IXC1-MYSQLSERVER-bin.000001 | 178 | No | | IXC1-MYSQLSERVER-bin.000002 | 2209 | No | | IXC1-MYSQLSERVER-bin.000003 | 15635 | No | | IXC1-MYSQLSERVER-bin.000004 | 211 | No | | IXC1-MYSQLSERVER-bin.000005 | 178 | No | +---------------------------+-----------+-------------+ 9 rows in set (0.11 sec)
5. Restore the logfiles for incomplete recovery.
C:Program FilesMySQLMySQL Server 8.0bin> mysqlbinlog "C:ProgramDataMySQLMySQL Server 8.0DataIXC1-LP48ZJ622-bin.000005" | mysql -u root -p
Enter password: *****
6. Verify the database TEST2.
