Cannot remove orphaned InnoDB Temp Table - missing .frm file

Question

For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120.

Following the guids like https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/ does not work, I still get an error that the table is not known (ERROR 1051 (42S02): Unknown table 'database_name.#mysql50#sql-4593_1e9')

What makes this issue different from other questions on the removal of orphaned InnoDB temp tables is that the database server seems to have deleted the .frm file, both, from disk and from memory (running lsof | grep sql-4593_1e9 shows only the .ibd file open)

Can that be a source of the problem? If so, is there any way to recreate the .frm file of a virtually inaccessible table of unknown structure?

The only mention of it I can get in the database itself is by running:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Which outputs the following:

+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
| TABLE_ID | NAME                        | FLAG | N_COLS | SPACE   | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
|  1576128 | database_name/#sql-4593_1e9 |    1 |    118 | 1576114 | Antelope    | Compact    |             0 |
|  1576129 | database_name/#sql-4593_78d |    1 |    118 | 1576115 | Antelope    | Compact    |             0 |
|  1576130 | database_name/#sql-4593_791 |    1 |    118 | 1576116 | Antelope    | Compact    |             0 |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+

I do not feel comfortable removing the database files from disk manually, as I am not entirely sure it wouldn't lead to issues down the road. Even if the chances of tablespace id collision are near zero.

Answer 1

You have a disconnect between what you have on disk and what you have on the index internal data dictionary. MySQL 8.0 has improved how the data dictionary is handled, but up to 10.4 mariadb has not implemented it.

You cannot just drop it, because it won't be found on disk, but innodb still see it. For older versions of MySQL and MariaDB you will find several guides online on how to synchronize the data dictionary and the disk. However, this is the way I found more effective and secure:

1. Create a temporary database and move all "good" tables, views, etc. from the original table to the new one (this is very fast as it only is a rename table, as long as the new database is on the same partition)
2. Drop the now empty database (so the innodb index will drop all its remaining contents. Drop content on disk if necessary too.
3. Recreate the old database, now completely empty in the InnoDB internal dictionary and data
4. Move back the tables through rename

I have used this several times and I think it is the easiest, fastest and more secure way to synchronize the partially deleted tables.

Important, #sql-4593_1e9 is the format for temporary tables, please don't drop those unless you think they are really leftovers- there could be transactions ongoing that need those internally for queries. In other words, I would not touch this unless they appear on a new server start without any connections.