We had a MySQL 5.7.x instance serving a production schema foo, and the server itself was migrated to 8.0.21 on 13 Sep 2020.
Recently we migrated data from foo to a new schema bar. After migration we managed to drop the old schema foo (via a simple drop database foo;) but the /var/lib/mysql/foo data-dir was left behind with a single file that had been created on the migration date itself (probably a temporary table strayed during a server restart):
ls -l /var/lib/mysql/foo/
total 5898252
drwxr-x--- 2 mysql mysql 4096 Jan 30 06:29 ./
drwxrwxr-x 12 root syslog 4096 Jan 30 06:44 ../
-rw-r----- 1 mysql mysql 6039797760 Sep 13 08:11 #sql-ib353-258599554.ibd
along with an ERROR log in MySQL error.log:
2021-01-30T06:29:25.239271Z 154 [Warning] [MY-011078] [Server] Problem while dropping database. Can't remove database directory (Error dropping database (can't rmdir './foo', errno: 39 - Directory not empty)). Please remove it manually.
We want to reclaim the 6+ GB space used by this stray file, however we cannot figure out the right way to "safely discard" it:
- We cannot drop or recreate
foo, e.g. to try dropping it again, treating as a temporary table (not even sure if it's applicable in 8.x), due to the offending directory:
mysql> drop database foo;
ERROR 3681 (HY000): Schema 'foo' does not exist, but schema directory './foo/' was found. This must be resolved manually (e.g. by moving the schema directory to another location).
mysql> create database foo;
ERROR 3678 (HY000): Schema directory './foo' already exists. This must be resolved manually (e.g. by moving the schema directory to another location).
- If we (re)move the
/var/lib/mysql/foodir (containing the file) to another location (glad we didn't just delete it!), MySQL spits out a warning at startup (yet continues to run):
2021-01-30T06:45:05.187534Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 369, name 'foo/#sql-ib353-258599554', file './foo/#sql-ib353-258599554.ibd' is missing!
- We tried to find the tablespace and drop it (in the hope that it will discard the file properly), which also failed:
mysql> select * from information_schema.files where file_name like '%foo/%';
+---------+--------------------------------+------------+--------------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | LOGFILE_GROUP_NAME | LOGFILE_GROUP_NUMBER | ENGINE | FULLTEXT_KEYS | DELETED_ROWS | UPDATE_COUNT | FREE_EXTENTS | TOTAL_EXTENTS | EXTENT_SIZE | INITIAL_SIZE | MAXIMUM_SIZE | AUTOEXTEND_SIZE | CREATION_TIME | LAST_UPDATE_TIME | LAST_ACCESS_TIME | RECOVER_TIME | TRANSACTION_COUNTER | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | STATUS | EXTRA |
+---------+--------------------------------+------------+--------------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
| 369 | ./foo/#sql-ib353-258599554.ibd | TABLESPACE | foo/#sql-ib353-258599554 | | NULL | NULL | NULL | NULL | InnoDB | NULL | NULL | NULL | 0 | 0 | 1048576 | 0 | NULL | 1048576 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NORMAL | NULL |
+---------+--------------------------------+------------+--------------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
mysql> drop tablespace `foo/#sql-ib353-258599554`;
ERROR 3119 (42000): InnoDB: A general tablespace name cannot contain '/'.
mysql> drop undo tablespace `foo/#sql-ib353-258599554`;
ERROR 3119 (42000): InnoDB: A general tablespace name cannot contain '/'.
- MySQL's guide on troubleshooting InnoDB data dictionary didn't offer any helpful hints for my scenario. (The 5.7 reference does mention about discarding intermediate tables but those instructions no longer seem applicable - in addition to the fact that my parent schema
foois already gone.) - This SO post 'remove orphan temporary table in MySQL innodb' seemed similar, but I have already dropped the corresponding schema. (Besides, having to dump and reimport seems a bit extreme, and I was hoping that 8.x would offer a better way to resolve my situation.)
- Mine sounds somewhat similar to the DBA question 'discard tablespace of temporary innodb table in MySQL', but a bit more complicated as my temporary file has been created during 5.7.x days - whereas I am currently upgraded to 8.0.x.
Any insight on how to get rid of this stray schema dir and file, without residual errors and impact on our other production schemas? Maybe by simply dropping some stale records in information_schema?
