MySQL Bugs: #1608: Innodb leaks temporary tables on crash

Bug #1608	Innodb leaks temporary tables on crash
Submitted:	20 Oct 2003 7:10	Modified:	20 May 2009 7:16
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
If there are some Innodb temporary tables opened, and MySQL is crashes (killed) 
This temporary table is not dropped on restart.

In case of normal shutdown temporary tables are dropped correctly.

How to repeat:
create temporary table tbl(i int) type=innodb;
insert into tbl values(1);

run: killall -9 mysqld 

start MySQL

create table innodb_table_monitor(i int) type=innodb;

You'll see the table with #sql prefix matching the temporary table orphaned in printout.

The same happens in case of crash during "alter table"

Suggested fix:
According to Heikki this is MySQL level problem - it shall clean up temporary tables on crash recovery from all table handlers.  Note we shall be careful not to delete any valuable data this way.

Note there is a chance for crash during table rename in ALTER TABLE, we need to make sure we'll not leave the customer without both tables in cleanup.

It is InnoDB issue. The storage engine should remove temporary tables during crash recovery - because after a crash MySQL may not know what temporary tables exist in the handler.

Hi!

This is a complex issue. What is important is that if mysqld crashes during the table rename phase of an ALTER TABLE, we should NOT automatically remove both associated temporary tables. A possible solution: somehow mark the original table when it is renamed. After a crash, only drop unmarked temporary tables.

Regards,

Heikki

I saw something in InnoDB code that makes me believe it was fixed meanwhile
Thus closing.
(Heikki, feel free to reopen if I'm wrong)

Hi!

This is not fixed. And, as I explained earlier, maybe best not to fix this at all, since automatic deletion of temp tables may in some bug scenarios lead to people losing their precious data!

Regards,

Heikki

Heikki,  

I do not understand your claim.  We're speaking about CREATE TEMPORARY TABLE.
These tables by definition should be dropped when server is restarted or connection is closed.

In which particular case do you consider data loss possible ? 

There is other case when we have MySQL Temporary table created for ALTER TABLE resolving. In such case there is really the small chance to be in situation when old table is already gone but new table is not renamed. 

These cases at least could be handled differently.

The situation with ALTER TABLE is even worse as this statement is not ACID due to frm operations.  ALTER TABLE part of this bug can perhaps be fixed at the same point as it is make to be ACID.

Peter,

please test if MySQL still leaks

CREATE TEMPORARY TABLE abc (...) TYPE=InnoDB;

tables on a crash, and update this bug report accordingly. I thought you were talking about the temp tables in an ALTER TABLE.

Thank you,

Heikki

I've just checked this using 4.0.21 (BK)  and this problem is still on its place.
After "killall -9 mysqld" I see temporary table space is not reclaimed and I see apropriate .frm file in  <datadir>/test directory.

I also checked it with 4.1.4 (BK) and it has exactly the same behavior leaking the table.

So this bug is not fixed.

I can not repeat this with 5.1.34. No *.frm file left and innodb_table_monitor does not show orphaned table after crashing server and restart.