Troubleshoot InnoDB issues in MariaDB Server. Find solutions and best practices for common problems, ensuring your InnoDB-based applications run smoothly and efficiently.
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.
Error Codes
This page is licensed: CC BY-SA / Gnu FDL
If InnoDB returns something like the following error:
ERROR 1016: Can't open file: 'x.ibd'. (errno: 1)it may be that an orphan .frm file exists. Something like the following may also appear in the error log:
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?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:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'dbname/%';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 ALTER TABLE ... ALGORITHM=INPLACE operation. They are listed in the INFORMATION_SCHEMA.INNODB_SYS_TABLES 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:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';When innodb_file_per_table 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:
mv #sql-36ab_2.frm #sql-ib87-856498050.frmDrop the table, for example:
DROP TABLE `#mysql50##sql-ib87-856498050`;This page is licensed: CC BY-SA / Gnu FDL
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 MariaDB 10.2.7, mode 0 was the only mode permitting changes to the data. From MariaDB 10.2.7, write transactions are permitted with mode 3 or less.
To recover the tables, you can execute SELECTs to dump data, and DROP TABLE (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).
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 undo logs when starting. Any DDL log for InnoDB tables are essentially ignored by InnoDB, but the server will start up
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
(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.
0
The default mode while InnoDB is running normally. Until MariaDB 10.2.7, it was the only mode permitting changes to the data. From MariaDB 10.2.7, 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 MariaDB 10.2.7, 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
(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.
Note also that XtraDB (<= MariaDB 10.2.6) by default will crash the server when it detects corrupted data in a single-table tablespace. This behaviour can be changed - see the innodb_corrupt_table_action 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 mariadb-dump. You can verify any other issues with any tables by running mariadb-check --all-databases.
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 "DROP DATABASE 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 ("CREATE DATABASE yourdatabase"), and then import your most recent dumps: "mysql < mydatabasedump.sql"
This page is licensed: CC BY-SA / Gnu FDL