1

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:

  1. 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).
  1. If we (re)move the /var/lib/mysql/foo dir (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!
  1. 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 '/'.

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.