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.
Best Answer
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:
rename table
, as long as the new database is on the same partition)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.