Troubleshoot InnoDB issues in MariaDB Server. Find solutions and best practices for common problems, ensuring your InnoDB-based applications run smoothly and efficiently.
Learn how to resolve inconsistencies between the InnoDB internal data dictionary and the file system, such as orphan .frm or .ibd files.
If InnoDB returns something like the following error:
it may be that an orphan .frm file exists. Something like the following may also appear in the error log:
If this is the case, as the text describes, delete the orphan .frm file on the filesystem.
In this case the table definition, the .frm file, is missing and the InnoDB dictionary expects it to be there. To remove the InnoDB dictionary entry, the existence of the file needs to faked and then dropped. The existence of the file is faked by creating the tablename.frm and potential the database directory if it is missing. Then a DROP TABLE or DROP DATABASE can be executed which will remove the InnoDB dictionary entry.
Use the query to identify potentially other tablespaces that are known but missing with:
An orphan intermediate table may prevent you from dropping the tablespace even if it is otherwise empty, and generally takes up unnecessary space.
It may come about if MariaDB exits in the middle of an operation. They are listed in the table, and always start with an #sql-ib prefix. The accompanying .frm file also begins with #sql-, but has a different name.
To identify orphan tables, run:
When is set, the #sql-*.ibd file will also be visible in the database directory.
To remove an orphan intermediate table:
Rename the #sql-*.frm file (in the database directory) to match the base name of the orphan intermediate table, for example:
Drop the table, for example:
This page is licensed: CC BY-SA / Gnu FDL
A starting point for diagnosing InnoDB issues, recommending checks on error logs, deadlocks, and table integrity using various tools.
As with most errors, first take a look at the contents of the MariaDB error log. If dealing with a deadlock, setting the innodb_print_all_deadlocks option (off by default) will output details of all deadlocks to the error log.
It can also help to enable the various InnoDB Monitors relating to the problem you are experiencing. There are four types: the standard InnoDB monitor, the InnoDB Lock Monitor, InnoDB Tablespace Monitor and the InnoDB Table Monitor.
Running CHECK TABLE will help determine whether there are errors in the table.
For problems with the InnoDB Data Dictionary, see InnoDB Data Dictionary Troubleshooting.
This page is licensed: CC BY-SA / Gnu FDL
ERROR 1016: Can't open file: 'x.ibd'. (errno: 1)InnoDB: Cannot find table test/x from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?Understand the different `innodb_force_recovery` levels, which allow you to start the server in read-only modes to recover data after a crash.
The InnoDB recovery mode is a mode used for recovering from emergency situations. You should ensure you have a backup of your database before making changes in case you need to restore it. The innodb_force_recovery server system variable sets the recovery mode. A mode of 0 is normal use, while the higher the mode, the more stringent the restrictions. Higher modes incorporate all limitations of the lower modes.
The recovery mode should never be set to a value other than zero except in an emergency situation.
Please note that recovery mode does not repair corruption. The corrupted files remain corrupted regardless of recovery mode. The sole purpose of recovery mode is to allow read access to the data, if at all possible.
Generally, it is best to start with a recovery mode of 1, and increase in single increments if needs be. With a recovery mode < 4, only corrupted pages should be lost. With 4, secondary indexes could be corrupted. With 5, results could be inconsistent and secondary indexes could be corrupted (even if they were not with 4). A value of 6 leaves pages in an obsolete state, which might cause more corruption.
Until , mode 0 was the only mode permitting changes to the data. From , write transactions are permitted with mode 3 or less.
To recover the tables, you can execute to dump data, and (when write transactions are permitted) to remove corrupted tables.
The following modes are available:
Recovery mode behaviour differs between versions (server/storage/innobase/include/srv0srv.h).
Note also that XtraDB (<= ) by default will crash the server when it detects corrupted data in a single-table tablespace. This behaviour can be changed - see the system variable.
Try to set innodb_force_recovery to 1 and start mariadb. If that fails, try a value of "2". If a value of 2 works, then there is a chance the only corruption you have experienced is within the innodb "undo logs". If that gets mariadb started, you should be able to dump your database with . You can verify any other issues with any tables by running .
If you were able to successfully dump your databases, or had previously known good backups, drop your database(s) from the mariadb command line like " yourdatabase". Stop mariadb. Go to /var/lib/mysql (or whereever your mysql data directory is located) and "rm -i ib*". Start mariadb, create the database(s) you dropped (" yourdatabase"), and then import your most recent dumps: "mysql < mydatabasedump.sql"
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'dbname/%';SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';mv #sql-36ab_2.frm #sql-ib87-856498050.frmDROP TABLE `#mysql50##sql-ib87-856498050`;6
(SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.
0
The default mode while InnoDB is running normally. Write transactions are permitted with innodb_force_recovery<=4.
1
(SRV_FORCE_IGNORE_CORRUPT) allows the server to keep running even if corrupt pages are detected. It does so by making redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. Any redo log for affected files or pages are skipped. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.
2
(SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge. No purge are performed, so the undo logs will keep growing.
3
(SRV_FORCE_NO_TRX_UNDO) does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions. Will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.
4
(SRV_FORCE_NO_IBUF_MERGE) The same as 3.
5
0
The default mode while InnoDB is running normally. Until , it was the only mode permitting changes to the data. From , write transactions are permitted with innodb_force_recovery<=3.
1
(SRV_FORCE_IGNORE_CORRUPT) allows the server to keep running even if corrupt pages are detected. It does so by making redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. Any redo log for affected files or pages are skipped. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.
2
(SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge. No purge are performed, so the undo logs will keep growing.
3
(SRV_FORCE_NO_TRX_UNDO) does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions. Starting with , will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.
4
(SRV_FORCE_NO_IBUF_MERGE) does not calculate tables statistics and prevents insert buffer merges.
5
0
The default mode while InnoDB is running normally. Write transactions are permitted with innodb_force_recovery<=4.
1
(SRV_FORCE_IGNORE_CORRUPT) allows the server to keep running even if corrupt pages are detected. It does so by making redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. Any redo log for affected files or pages are skipped. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.
2
(SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge. No purge are performed, so the undo logs will keep growing.
3
(SRV_FORCE_NO_TRX_UNDO) does not roll back DML transactions after the crash recovery. Does not affect rollback of currently active DML transactions. Will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.
4
(SRV_FORCE_NO_DDL_UNDO) does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions. Will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.
5
(SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the when starting. Any DDL log for InnoDB tables are essentially ignored by InnoDB, but the server will start up
(SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the undo logs when starting.
6
(SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.
(SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the undo logs when starting.
6
(SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.