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.