CODEWITHSUNDEEP
mysql
Jeff Scott
Jeff Scott

Reputation: 326

Unusual mysql frm and idb files

I have the innodb_file_per_table option enabled so I have an .frm and an .ibd file for each table. What's unusual is there are also some files that appear to be from temporary tables on the filesystem:

/db/mysql$ ls
<snip>
 #sql2-25fe-5.frm
 #sql2-25fe-5.ibd
 #sql2-55f4-73.frm
 #sql2-55f4-73.ibd
 #sql-55f6_13b626.ibd
 #sql-6abe_e3.ibd
</snip>

One of them is rather large so I'd like to get rid of them if it's safe. I tried dropping them like regular and temporary tables but it didn't work either way.

drop table `#sql2-25fe-5`;
drop temporary table `#sql2-25fe-5`;

Any ideas? Thanks

Upvotes: 2

Views: 1940

Answers (1)

Jeff Scott
Jeff Scott

Reputation: 326

Just to clear this out:

Dumping and reloading all the tables freed up the space like I wanted it to (an innobackupex backup & restore did not work). I believe these permanent temporary tables were the result of one of two bugs which have been fixed in mysql:

InnoDB: If the server crashed at a precise moment during an ALTER TABLE operation that rebuilt the clustered index for an InnoDB table, the original table could be inaccessible afterward. An example of such an operation is ALTER TABLE ... ADD PRIMARY KEY The fix preserves the original table if the server halts during this operation. You might still need to rename the .ibd file manually to restore the original table contents: in MySQL 5.6 and higher, rename from #sql-ib$new_table_id.ibd to table_name.ibd within the database directory; prior to MySQL 5.6, the temporary file to rename is table_name#1 or #2. (Bug #14669848)

https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-30.html

or

InnoDB: For UPDATE statements in which an error occurred, it was possible for a temporary file opened during the update not to be closed. (Bug #15978766)

https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html

Upvotes: 1

Related Questions