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»;
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.
MYSQL: ERROR 1049 (42000): Unknown database ‘mysql’
ERROR 1812 (HY000) at line 14534: InnoDB: A general tablespace named `mysql` cannot be found.
#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci’
Collation is the assembly of written information into a standard order
dump.sql file in Notepad and hit CTRL+H to find and replace the string “utf8mb4_0900_ai_ci” and replaced it with “utf8mb4_general_ci“
834MB emeditor
7 string were replaced in 0.5seconds 800Mb file.
not responded by notepad or wordpad
upto 500MB 50MB notepad
Notepad ++ to large to open
or sqldumpsplitter
.idb file missing
[Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `king`.`wp_b3vaaca4kz_options` because the .ibd file is missing
Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.
* Drop the table from the mysql server
* Create a table with similar structure
* alter table discard tablespace
* Move the cfg and ibd files into the data directory(that you have copied from another server)
* Alter table import tablespace
Tablespace is missing for table `king`.`wp_b3vaaca…
king.wp_b3vaaca4kz_afap_logs
repair
error
Corrupt
how to repair corrupt innodb table in mysql?
no idb & db folder in datadirectory
mysqlcheck -c -u root -p –all-databases
king.wp_b3vaaca4kz_redirection_logs
Warning : InnoDB: Tablespace is missing for table king/wp_b3vaaca4kz_redirection_logs.
Warning : InnoDB: Tablespace is missing for table king/wp_b3vaaca4kz_afap_logs.
Error : Tablespace is missing for table `king`.`wp_b3vaaca4kz_afap_logs`.
error : Corrupt
mysql.service: Main process exited, code=exited, status=1/FAILURE
/var/log/mysql
/var/lib/mysql
Cannot load from mysql.proxies_priv. The table is probably corrupted
apparmor=”DENIED” operation=”open” profile=”/usr/sbin/mysqld” name=”/etc/ssl/openssl.cnf”
nano /etc/apparmor.d/usr.sbin.mysqld
/etc/mysql/** r,
/etc/ssl/ r,
/etc/ssl/** r,
1. apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld
systemctl restart apparmor.service
systemctl stop apparmor.service
service mysql start
SHOW VARIABLES LIKE ‘%ssl%’;
he have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.
Check the status of our current connection to confirm:
* s
1. mysqldump –all-databases –add-drop-database –add-drop-table > databases.sql
GRANT ALL PRIVILEGES ON
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘tolkien’@’%’;
mysql> GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’localhost’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’;
MySQL How do you restore tablespace?
This is not new information but I have not covered it much so addressing it now for those that need it.
If you lose your ibd files… you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?
Here is a simple example to recover tablespace.
mysql> Create database demo;
mysql> use demo;
mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Now we store some data…
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
+----+---------------------+
2 rows in set (0.00 sec)
OK now lets break it..
# systemctl stop mysqld
# cd /var/lib/mysql/demo/
# ls -ltr
total 80
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd
# mv demotable.ibd /tmp/
# systemctl start mysqld
# mysql demo
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demotable |
+----------------+
1 row in set (0.00 sec)
mysql> desc demotable;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.01 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.
Broken and lost tablespace… Now we can recover it..
demo]# cp /tmp/demotable.ibd .
mysql> ALTER TABLE demotable DISCARD TABLESPACE;
demo]# cp /tmp/demotable.ibd .
demo]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd
demo]# chown mysql:mysql demotable.ibd
demo]# mysql demo
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it
mysql> REPAIR TABLE demotable;
+----------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+---------------------------------------------------------+
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |
+----------------+--------+----------+---------------------------------------------------------+
Notice now we also got another error.. This is usually tied to space available to tmpdir, and repair doesn’t work for .ibd anyway.
mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+
# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/
# systemctl restart mysqld
# mysql demo
OK used the mysql-files directory just for example.
Now we can try again.
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.61 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)
mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
| 3 | 2019-07-12 23:56:08 |
+----+---------------------+
OK worked.
Now, this is all nice and simple if you just have one table. But what about 100s…
Automate it, of course, and use your information_schema to help.
Make a few more copies for test.
mysql> create table demotable1 like demotable;
Query OK, 0 rows affected (0.51 sec)
mysql> create table demotable2 like demotable;
Query OK, 0 rows affected (1.04 sec)
mysql> create table demotable3 like demotable;
Query OK, 0 rows affected (0.74 sec)
mysql> create table demotable4 like demotable;
Query OK, 0 rows affected (2.21 sec)
break them all..
demo]# mv *.ibd /tmp/
Now using your information_schema.tables table, you can build out all the commands you will need.
# vi build_discard.sql
# cat build_discard.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';
# vi build_import.sql
# cat build_import.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';
# mysql -N < build_import.sql > import_tablespace.sql
# mysql -N < build_discard.sql | mysql demo
demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo < import_tablespace.sql
# mysql demo
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO demotable1 (id) VALUES (NULL);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO demotable2 (id) VALUES (NULL);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO demotable3 (id) VALUES (NULL);
^[[AQuery OK, 1 row affected (0.37 sec)
mysql> INSERT INTO demotable4 (id) VALUES (NULL);
Query OK, 1 row affected (0.12 sec)
And it worked.
Это сообщение от из MySQL Performance Blog.
Несколько лет назад Ив Трюдо и Александр Кузьминский писали сообщения о различных способах восстановления потерянных файлов .ibd:
- Подключение потерянных .ibd файлов
- Мелочи восстановления или как восстановить потерянный файл ibdata1
Сегодня я хочу показать вам, как это сделать проще и быстрее. В моем примере я восстановлю файл payment.ibd (таблица платежей) из Sakila DB на сервере с MySQL 5.5 (но с помощью MySQL 5.6 и песочницы ).
В моем случае ОС CentOS. Поэтому мне нужно было:
- установить mysqlsandbox (см. инструкции там)
- скачать последнюю версию Percona Server 5.6:
wget http://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/release-5.6.14-62.0/483/binary/linux/i686/Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Создать песочницу:
make_sandbox Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Попробуй это:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select @@versionG" *************************** 1. row *************************** @@version: 5.6.14-rel62.0
Оно работает!
Проверьте datadir и включена ли опция innodb_file_per_table (это требование):
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like 'datadir'" +---------------+---------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------+ | datadir | /home/mixa/sandboxes/msb_5_6_14/data/ | +---------------+---------------------------------------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like '%per_table'" +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+
Если он не включен, то вам нужно включить его
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "SET GLOBAL innodb_file_per_table=1"
Создать пустую таблицу платежей на песочнице 5.6
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 test < payment_table.sql
Payment_table.sql — файл с выпиской «SHOW CREATE TABLE» для таблицы платежей. Структура таблицы должна быть одинаковой.
cat payment_table.sql CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`) ) ENGINE=InnoDB
Стоп песочница
./sandboxes/msb_5_6_14/stop
Замените файл .ibd (в моем случае его правильная копия находится в моем домашнем каталоге)
cp ~/payment.ibd ~/sandboxes/msb_5_6_14/data/test/ -f
Убедитесь, что разрешения для .ibd файла в порядке
sudo chmod 660 ~/sandboxes/msb_5_6_14/data/test/payment.ibd sudo chown : ~/sandboxes/msb_5_6_14/data/test/payment.ibd
Начать песочницу
./sandboxes/msb_5_6_14/start
В настоящее время, если вы попытаетесь выбрать что-то из таблицы, вы получите ошибку:
Выбрать из таблицы
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 1146 (42S02) at line 1: Table 'test.payment' doesn't exist
Журнал ошибок
2013-11-02 14:36:34 b7eff990 InnoDB: Error: table 'test/payment' InnoDB: in InnoDB data dictionary has tablespace id 7, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name test/payment and id 10, though. Have InnoDB: you deleted or moved .ibd files? ... ... 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Failed to find tablespace for table '"test"."payment"' in the cache. Attempting to load the tablespace with space id 7. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: In file './test/payment.ibd', tablespace id and flags are 10 and 0, but in the InnoDB data dictionary they are 7 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/payment'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: cannot calculate statistics for table "test"."payment" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
Как это исправить? В 5.6 управление табличным пространством очень улучшено, поэтому нужно только «ALTER TABLE .. DISCARD TABLESPACE» и «ALTER TABLE .. IMPORT TABLESPACE».
Пожалуйста, проверьте также ограничения: Ограничения копирования табличных пространств
Посмотрите на пример:
Отменить табличное пространство
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment discard tablespace; show warnings;" +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1812 | InnoDB: Tablespace is missing for table 'test/payment' | | Warning | 1812 | InnoDB: Tablespace is missing for table 'payment' | +---------+------+--------------------------------------------------------+
Импорт табличного пространства
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment import tablespace; show warnings" +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/payment.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
Вот и все, данные восстановлены, таблица выплат доступна на 5.6 песочнице.
Теперь проверьте, есть ли данные в таблице платежей в песочнице:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment"
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select * from test.payment limit 1G"
*************************** 1. row ***************************
payment_id: 1
customer_id: 1
staff_id: 1
rental_id: 76
amount: 2.99
payment_date: 0000-00-09 03:49:32
last_update: 2028-02-08 12:32:35
Существует.
Так что выкинь его из песочницы и восстанови на 5.5:
Дамп из 5.6
mysqldump -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 --add-drop-table test payment > ~/payment_dump.sql
Восстановить до 5.5
mysql -u user -p < ~/payment_dump.sql
Проверьте, существуют ли данные на 5.5
mysql -u root -e "select * from test.payment limit 3;" +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | 1 | 1 | 1 | 76 | 2.99 | 0000-00-09 03:49:32 | 2028-02-08 12:32:35 | | 2 | 1 | 1 | 573 | 0.99 | 0000-00-09 03:49:32 | 0000-00-00 00:00:00 | | 3 | 1 | 1 | 1185 | 5.99 | 0000-00-09 03:49:37 | 0000-00-00 00:00:00 | +------------+-------------+----------+-----------+--------+---------------------+---------------------+
Во время моей работы с этим делом я попал в ситуацию, в которой оплата в дроп-таблице на 5.5 была невозможна, потому что payment.idb там не был корректным — поэтому сервер каждый раз падал, когда я пытался получить доступ к этой таблице. Для устранения этой проблемы:
— остановка сервера
— гт .ibd файл
— Запуск сервера
— удаление таблицы , как правило , с помощью DROP TABLE команды