temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

Question

I executed some alter statements on my master server, which have been successfully executed on master (now there is no temp table on master) and all alter also have been replicated on slave server successfully. But still I am getting temp table on slave (#sql-7a87_230c32.ibd and #sql-7a87_230c32.frm).

Now I need help to know below points:

1. When alter has been successfully done then why this temp table still exist and not deleted.

2. Now how can I delete this temp table and what will be its impact.

3. May I read this temp file or know by any command that for which table this temp table was created.

Note: If I delete it from its directory path then mysql will throw error message related with it and also I am not aware what will be its impact.

I will be very thankful for any quick help.

---

2nd Update

1.. When alter has been successfully done then why this temp table still exist and not deleted.

After reading #sql-7a87_230c32.frm whith the help of your post, now it is clear that it was my first alter table (I altered 6 tables one by one).

Below are some facts which will help to know exact reason-

1. sql-7a87_230c32.frm file creation time is "2014-06-09 01:49:00" and its .ibd file #sql-7a87_230c32.frm time is "2014-06-09 03:34:00" and its size is 3.4 GB while its main file size is 6.8 GB

2. After checking server logs it came to know that slave was being shutdown at "2014-06-09 03:33:05" (I have to check if it was done by system admin team even they were aware about this db activity or itself) and restarted when alter was executing on slave.

3. This table creation time on slave is showing as "2014-06-09 04:43:32".

After analyzing above facts it seems first time alter statement on this table was executing near about "2014-06-09 01:49:00" but without copying completely (as temp .ibd table size is less than its original size) it was rolled back when server was gone shutdown near about at "2014-06-09 03:33:05" but system did not remove these temp tables. Now again when slave gone up it was started replication again same point and now altered table successfully, which was completed by "2014-06-09 04:43:32".

But as per my understanding in this case temp tables should be remove along with rolled back but in this case it was not done. Please help me to clear it.

Answer 1

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.

Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.

Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.

Don't worry about the temp table name being used again. Look at the name of the temp table

#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in ₁₆10 or ₂40 which 1,099,511,627,776. I think you will be OK in this.

Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.

However, you could probably rename the .frm and use a utility to read the table structure.

Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?

EPILOGUE

Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.

Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow

UPDATE 2014-06-10 08:36 EDT

It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

• Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
• Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
• Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).

Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.