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 1610 or 240 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.
Look carefully at the error message
ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB
What is error 64 ? perror says the following:
$ perror 64
OS error code 64: Machine is not on the network
The ALTER TABLE
command is create a temp file with the new compression. However, it is trying to create a temp file but it cannot write to the directory tmpdir is mapped to.
- If tmpdir is mapped to a SAN that disconnected from the network, that error would make sense.
- If tmpdir ran out of space, the error message makes no sense but the end result would still stop
ALTER TABLE
from completing its operation.
If this problem is repeatable, you may want to try doing this change manually.
create table positions_comp like positions;
alter table positions_comp KEY_BLOCK_SIZE=8;
insert into positions_comp select * from positions;
alter table positions rename positions_old;
alter table positions_comp rename positions;
Best Answer
After checking they aren't attached to an active process,
ls -la /proc/$(pidof mysqld)/fd
, orfuser {filename}
, they can be removed withrm
.From MariaDB-10.4 onwards you shouldn't see these created on disk again ( MDEV-15584).