InnoDB Data Dictionary Troubleshooting

Can't Open File

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.

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:

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 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:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}