MySQL 8.0.23: Removeing huge '#sql-ib****.idb files?

Question

I had a massive mysql database crash this morning and realized it was due to the server running out of disk space. I recently upgraded mysql to version 8.0.23 last week from 5.7.

As I was looking for places to free up disk space, I found these odd files that were created last week around the time I upgraded mysql. They are found in /var/lib/mysql/[databasename]:

...
-rw-r-----  1 mysql mysql       4425 Apr 29 02:08  sitemap_589.sdi
-rw-r-----  1 mysql mysql         88 May  7 22:56  sitemap.MYD
-rw-r-----  1 mysql mysql       2048 May  8 00:29  sitemap.MYI
-rw-r-----  1 mysql mysql  281018368 Apr 29 02:07 '#sql-ib102825-1688518596.ibd'
-rw-r-----  1 mysql mysql  289406976 Apr 29 02:07 '#sql-ib102825-2026724520.ibd'
-rw-r-----  1 mysql mysql  876609536 Apr 29 02:07 '#sql-ib147-1503108124.ibd'
-rw-r-----  1 mysql mysql  801112064 Apr 29 02:07 '#sql-ib147-2007596613.ibd'
-rw-r-----  1 mysql mysql 1275068416 Apr 29 02:07 '#sql-ib147-2132787644.ibd'
-rw-r-----  1 mysql mysql  801112064 Apr 29 02:07 '#sql-ib147-2317916983.ibd'
-rw-r-----  1 mysql mysql 1149239296 Apr 29 02:07 '#sql-ib147-3216103449.ibd'
-rw-r-----  1 mysql mysql  788529152 Apr 29 02:07 '#sql-ib147-3646714586.ibd'
-rw-r-----  1 mysql mysql 1015021568 Apr 29 02:07 '#sql-ib147-366921508.ibd'
-rw-r-----  1 mysql mysql  201326592 Apr 29 02:07 '#sql-ib147-3791138395.ibd'
-rw-r-----  1 mysql mysql  775946240 Apr 29 02:07 '#sql-ib147-4049574556.ibd'
-rw-r-----  1 mysql mysql   71303168 Apr 29 02:07 '#sql-ib147-4099766703.ibd'
-rw-r-----  1 mysql mysql  289406976 Apr 29 02:07 '#sql-ib147-930611645.ibd'
-rw-r-----  1 mysql mysql   58720256 Apr 29 02:07 '#sql-ib18288-1061311775.ibd'
-rw-r-----  1 mysql mysql   58720256 Apr 29 02:07 '#sql-ib18288-1061311775.ibd'
-rw-r-----  1 mysql mysql   16777216 May  8 22:55  categories.ibd
-rw-r-----  1 mysql mysql      10523 Apr 29 02:08  details_582.sdi
-rw-r-----  1 mysql mysql    1439140 May  8 22:49  details.MYD
....

Note that these files also have single quotes around them (')#sql-ib147-366921508.ibd(')

All of my databases combined are around 10GB in size. There are about 20GB worth of these files scattered in my other databases. These file names also do not look like my regular table names (which were also in the lists - not shown here). After research, I found that these might be TABLESPACES that I don't need anymore? How do I delete these?

I tried viewing these using:

SELECT * FROM `INNODB_TABLESPACES`

I found names like:

databasename/#sql-ib147-2317916983
databasename/#sql-ib147-1503108124
databasename2/#sql-ib147-3216103449
....

Note that they are similar in name to what was found in the disk directory, but with the addition of the database name before the file: (databasename)/#sql-ib147-2317916983

When I try to use:

DROP TABLESPACE `databasename/#sql-ib147-2007596613`

I get an error like:

#3119 - InnoDB: A general tablespace name cannot contain '/'.

How should I delete these files the correct way, and if I should delete them? My sites are all working fine now, so if these are just temporary, I'm guessing they can be safely deleted.

UPDATE

I tried the solution below:

mysql> DROP TABLE #mysql50##sql-ib147-2317916983

Note: It first asks me which database to use first. So first I use:

USE databasename;

However, after I try the "DROP TABLE", I get errors like:

An expression was expected. (near "" at position 0)

I also tried it with single quotes like:

DROP TABLE '#mysql50##sql-ib147-2132787644'

but I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#mysql50##sql-ib147-2132787644'' at line 1

When I try it with these weird symbols from copying and pasting (`):

DROP TABLE `#mysql50##sql-ib147-3791138395`;

I get this error:

ERROR 1051 (42S02): Unknown table 'databasename.#mysql50##sql-ib147-3791138395'

Note that in the my "SELECT * FROM information_schema.INNODB_TABLESPACES" query, these tables show up like:

databasename/#sql-ib147-1503108124  instead of just  #sql-ib147-1503108124

Note that I'm using MySQL 8.0, so I'm not sure if the bug explained in the answer (regarding #mysql50##) applies as it looks like that bug was in 5.7. Also, could the MySQL 8.0 explain the syntax errors?

Also, I updated the list of files as the way I see them in my directory. These files have single quotes around them as shown above.

Answer 1

Do not delete the files via the file system unless you enjoy seeing messages like this from the database:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

These files are generally the result of a MySQL crash during an ALTER TABLE operation. This can leave you with an orphaned temporary table inside the InnoDB tablespace.

There are two options ahead of you:

1. Perform a data dump, drop the database, and restore
2. Drop the table spaces

For the sake of simplicity, let’s go with option two. To do so, the tablespace needs to be prefixed with octothorps:

mysql> DROP TABLE #mysql50##sql-ib147-2317916983

Be sure to keep the #mysql50# bit intact to avoid a long-standing bug 😕