MySQL Bugs: #45976: InnoDB lost files for temporary tables on graceful shutdown

Bug #45976	InnoDB lost files for temporary tables on graceful shutdown
Submitted:	6 Jul 2009 16:04	Modified:	6 Feb 2012 22:07
Reporter:	Nicklas Westerlund (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.35, 5.1 bzr	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
This bug is quite similar to
http://bugs.mysql.com/bug.php?id=41609

However, this does not just occur on crashes, but also on each restart afterwards. (In fact, I have not recorded any crashes on this machine)

In fact, this bug: http://bugs.mysql.com/bug.php?id=20867 is pretty much exactly what I'm seeing, but it says it's fixed in 5.1.15. However, I think this has re-surfaced (or at least on solaris), as that's what I'm seeing. Considering that, I think that something is going on that was fixed earlier.

This machine also uses innodb_file_per_table.

On startup (normal startup, not during crash recovery), I get the following:

090705 19:21:47 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
090705 19:21:47 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './db_tmp/#sql4fb8_21146_16.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

And of course the .ibd files isn't there. These are temporary tables.

Taking a look at the table monitor, it does confirm a few orphaned tables, without any .ibd files, such as:

090706 15:21:06 InnoDB: error: space object of table tmp/#sql2cc8_15842_4,
InnoDB: space id 5561 did not exist in memory. Retrying an open.
090706 15:21:06 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
090706 15:21:06 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sql2cc8_15842_4.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
090706 15:21:06 InnoDB: cannot calculate statistics for table tmp/#sql2cc8_15842_4
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
090706 15:21:06 InnoDB: cannot calculate statistics for table tmp/#sql2cc8_15842_4
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
--------------------------------------
TABLE: name tmp/#sql2cc8_15842_4, id 0 6022, columns 4, indexes 1, appr.rows 0
COLUMNS: uid: DATA_VARCHAR prtype 524559 len 255; dri: DATA_SYS prtype 256 len 6; dti: DATA_SYS prtype 257 len 6;
INDEX: name GEN_CLUST_INDEX, id 0 10772, fields 0/4, uniq 1, type 1
root page 3, appr.key vals 100, leaf pages 0, size pages 0
FIELDS: dri dti DB_ROLL_PTR uid

How to repeat:
This did not occur with 5.0.74, but came after an upgrade to 5.1.35.
I guess repeating can be done by crashing the server, although this occurs during normal shutdown/start cycles as well.

Suggested fix:
none for now

Thank you for the report.

Verified as described: bug #20867 was not fixed correctly. Only Solaris now is affected.

How to repeat:

$ ./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --port=33051 --socket=/tmp/mysql_ssmirnova.sock -O thread_stack=1280000 --log-error --innodb_file_per_table

$ mysql
create temporary table t1(f1 int);
insert into t1 values(1);

In other connection kill -9 mysqld

Error log now full of text following forever:

090707 22:06:14  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
090707 22:06:14  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sql42a4_1_2.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
090707 22:06:14  InnoDB: Started; log sequence number 0 49478

On Linux this message printed once, then after next restart InnoDB does not try to open temporary table.

In version 5.0 everything works correctly.

We had a similar case with a different client on SunOS 5.10, using MySQL 5.1.33.

5.1.34 and 5.1.36 linux/max osx same problem.

I should have added this, I did a test on my issue on this to temporarily get rid of the messages, though on mine it was:

091117 13:44:23  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sql5628_18_2b.ibd'!

So not sure why the tmps are all different, must the the tmpdir setting?

I don't really see a convenient way around this. Though if you want to periodically clear this out I tested that one could simply:

create database tmp;
drop database tmp;

This worked for me without having to recreate any tables,copying frm files, dump,etcetc. The problem is the server cleans up the tmp directory but not the InnoDB data dictionary, so when it restarts it has stuff referenced to some temporary tables in a tmp database.

Then restart mysqld and the messages are clear until you restart the next time.

Thank you for the tip on creating and dropping tmp. It worked for me too,

Added to changelog for 5.1.62, 5.5.22, 5.6.5:

Server startup could produce an error for temporary tables using the
InnoDB storage engine, if the path in the $TMPDIR variable ended with
a / character. The error log would look like:

120202 19:21:26  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
120202 19:21:26  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './t/#sql7750_1_0.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.

The workaround for the problem was to create a similar
temporary table again, copy its .frm file to tmpdir under the name
mentioned in the error message (for example, #sql123.frm) and restart
mysqld with tmpdir set to its normal value without a trailing slash,
for example /var/tmp. On startup, Mysql would see the .frm file
and issue DROP TABLE for the orphaned temporary table.