InnoDB Data Dictionary Troubleshooting
Contents
Can't Open File
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.
Could not find a valid tablespace file
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/%';
Removing Orphan Intermediate Tables
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 will be 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.frm
- Drop the table, for example:
DROP TABLE `#mysql50##sql-ib87-856498050`;