MySQL Bugs: #20867: InnoDB Bug - create temporary table+crash => mysqld needs to clean up

Bug #20867	InnoDB Bug - create temporary table+crash => mysqld needs to clean up
Submitted:	5 Jul 2006 14:14	Modified:	16 Jan 2007 5:23
Reporter:	Jeff C	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.12-BK, 5.1.11, 5.0.25-BK, 4.1-bk	OS:	Linux (RHEL4)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	bfsm_2006_12_07, innodb, Q1, temporary tables

Description:
After creating a temporary table...:

create temporary table whatever (id int) engine=innodb;

<mysql crashes> (5.1.11 ... rare but it happens)

You're left with an innodb warning about the temp table being stuck in the data dictionary but you cannot clean it out. I've tried every suggested method. I think this is a bug as the temporary table on restart should be cleaned out automatically.

Hopefully someone figures out how to clean the data dictionary without reloading all InnoDB tables. From this point on, I won't use engine=InnoDB on create temporary..

tmpdir=/var/tmp

host.err log snip:

060704 5:42:27 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.
060704 5:42:27 InnoDB: Error: trying to open a table, but could not

InnoDB: open the tablespace file './tmp/@0023sql14dc_1607_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.
InnoDB: Please refer to
InnoDB: [dev.mysql.com]
InnoDB: for how to resolve the issue.

Files Exist:

root@new:/var/tmp:#: ls -la "@0023sql14dc_1607_0.ibd"
-rw-r----- 1 mysql mysql 114688 Jul 4 06:28 @0023sql14dc_1607_0.ibd
root@new:/var/tmp:#: ls -la "@0023sql14dc_1607_0.frm"
-rw-r----- 1 mysql mysql 8678 Jul 4 06:27 @0023sql14dc_1607_0.frm
root@new:/var/tmp:#:

InnoDB Table Monitor Output:

060704 6:01:40 InnoDB: error: space object of table tmp/@0023sql14dc_1607_0,
InnoDB: space id 3901 did not exist in memory. Retrying an open.
060704 6:01:40 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
060704 6:01:40 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/@0023sql14dc_1607_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.
InnoDB: Please refer to
InnoDB: [dev.mysql.com]
InnoDB: for how to resolve the issue.
060704 6:01:40 InnoDB: cannot calculate statistics for table tmp/@0023sql14dc_1607_0
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [dev.mysql.com]
060704 6:01:40 InnoDB: cannot calculate statistics for table tmp/@0023sql14dc_1607_0
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [dev.mysql.com]

The files exist... Not sure how to clean this InnoDB data dict problem without reloading InnoDB fresh.. (Would take 2-3 days).

How to repeat:
create temporary table whatever (id int primary key) engine=innodb;

get mysql to crash

On reload, you'll see the error log reporting tables that don't exist and you can't get rid of them. The files remain on the disk tho.

Thank you for a problem report. Please, send you my.cnf content. Do you have innodb_file_per_table set?

Yes I do... 

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 1100M
innodb_additional_mem_pool_size = 25M
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_status_file

With innodb_file_per_table I was able to repeat the results described. Both with 5.1.12-BK:

060723 12:22:56 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060723 12:22:56 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 49499.
InnoDB: Doing recovery: scanned up to log sequence number 0 49499
060723 12:22:56 InnoDB: Error: table 'tmp/@0023sql8fd_2_0'
InnoDB: in InnoDB data dictionary has tablespace id 1,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
InnoDB: for how to resolve the issue.
060723 12:22:56 InnoDB: Started; log sequence number 0 49499
060723 12:22:56 [Note] /home/openxs/dbs/5.1/libexec/mysqld: ready for connection
s.
Version: '5.1.12-beta-debug' socket: '/tmp/mysql.sock' port: 3306 Source dist
ribution
060723 12:22:56 [Note] SCHEDULER: Manager thread booting
060723 12:22:56 [Note] SCHEDULER: Loaded 0 events
060723 12:22:56 [Note] SCHEDULER: Suspending operations

and with 5.0.25-BK:

060723 12:19:16 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060723 12:19:16 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1197926267.
InnoDB: Doing recovery: scanned up to log sequence number 0 1197926267
060723 12:19:16 InnoDB: Error: table 'tmp/#sql871_1_0'
InnoDB: in InnoDB data dictionary has tablespace id 1,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
InnoDB: for how to resolve the issue.
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 1720, file name mysql-bin.001
InnoDB: Last MySQL binlog file position 0 924, file name ./suse-bin.000003
060723 12:19:16 InnoDB: Started; log sequence number 0 1197926267
060723 12:19:16 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connection
s.
Version: '5.0.25' socket: '/tmp/mysql.sock' port: 3306 Source distribution

This particular case (with TEMPORARY table) is not described in the manual, neither in

http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

nor in

http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html.

So, even it is either InnoDB bug or documentation request, at least.

Looks like the new special character escaping convention (@...) is causing problems with temporary table names that MySQL randomly generates. Maybe the name is not escaped in one place of InnoDB code, while it is escaped in another.

Assigning this to Marko Mäkelä.

This bug seems to have been introduced in InnoDB 4.1 with innodb_file_per_table. On my system, the .frm and .ibd files are created in /tmp, but InnoDB stores the database name as "tmp", causing possible problems if the user does CREATE DATABASE tmp.

If mysqld crashes before the temporary table is dropped, in MySQL 5.1 the files will remain in the temporary directory, but InnoDB will not find them during crash recovery. Previous versions of MySQL seem to properly delete the orphan #sql*.{frm,ibd} files during startup, but also there the garbage will remain in the InnoDB data dictionary.

When mysqld is started, it should drop any tables corresponding to .frm files it finds and deletes in the temporary directory. This is outside the scope of the InnoDB storage engine.

Work-around:

CREATE DATABASE tmp;
USE tmp;
CREATE TABLE whatever(a INT)engine=innodb;

# in the data directory, copy tmp/whatever.frm
# to tmp/#sql...frm (4.0 and 5.1) or tmp/@0023sql...frm

DROP DATABASE tmp;

# in mysqld 5.1, rm /tmp/@0023sql*

This is not an InnoDB bug. It should be fixed in MySQL code or addressed in the documentation.

On a related note, see Bug #21313.

Changing the category to MySQL Server, since the cleanup of temp tables after a crash is best to do in a centralized way in mysqld.

The problem is that mysql removes temporary tables at startup
(5.0, see init_serever_components()->table_cache_init()->mysql_rm_tmp_tables()),
after that InnoDB starts(5.0, see init_serever_components()->ha_init()) and
InnoDB data dictionary has a record for temporary table which does not
correspond with apropriate frm file. Suggested fix is to remove
tmp table records from InnoDB data dictionary during InnoDB intialization.
It can be fixed only in InnoDB code. It seems to me the category should
be changed to 'innodb bug'.

Based on the last comment from Sergey, I change the category
back to InnoDB. Marko, pease have a look at it again.

All,

is it possible that if an ALTER TABLE crashes at a critical moment, then 

init_server_components()->table_cache_init()->mysql_rm_tmp_tables()

removes the table under the alteration COMPLETELY? That would be a critical bug in MySQL.

My objection to automatic dropping of temporary InnoDB tables has always been that it might cause a valid table to be dropped in some case! We could do it in InnoDB if we knew that no user data can be destroyed that way.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16653

ChangeSet@1.2582, 2006-12-08 17:51:59+04:00, gluh@mysql.com +3 -0
  Fix for bug#20867 InnoDB Bug - create temporary table+crash => mysqld needs to clean up
  During tmp tables cleanup we get the handler for temporary table
  and delete table using handler method.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17203

ChangeSet@1.2585, 2006-12-20 15:27:05+04:00, gluh@mysql.com +3 -0
  Fix for bug#20867 InnoDB Bug - create temporary table+crash => mysqld needs to clean up
  2nd version
  During tmp tables cleanup we get the handler for temporary table
  and delete table using handler method.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17833

ChangeSet@1.2593, 2007-01-10 12:57:03+04:00, gluh@mysql.com +3 -0
  Fix for bug#20867 InnoDB Bug - create temporary table+crash => mysqld needs to clean up
  2nd version
  During tmp tables cleanup we get the handler for temporary table
  and delete table using handler method.

Fixed in 4.1.23, 5.0.34, 5.1.15-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in 4.1.23, 5.0.34, and 5.1.15 changelogs. Tagged with "InnoDB" since the issue arose when using InnoDB tables.

But how get lost the error-message 

* Months ago two temp-files were left after a crash
* After no timestamo change for some days i deleted them
* Results in the error message every startup

100821 17:41:06  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './dbmail/#sql2-704-271.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.

Creating a table in another database and move the files does not solve anything because in "innodb_table_monitor" yu get InnoDB: in InnoDB data dictionary has tablespace id 545, but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./dbmail/#sql2-704-271.ibd and id 690

So you get only the message in standard-errorlog away but not solve the problem really. How to get away table space id 545?

Additional comment: The Temp-Files are not under /tmp, they are in the database directory

I too am now seeing the last error reported.

Our SQL server crashed during a large alter statement and left temporary partitioned files on the disk.

After re-running the alter successfully, I attempted to remove the files and now get this error:

May  8 08:42:24 rps-reportdb02 mysqld: 120508  8:42:24  InnoDB: Operating system error number 2 in a file operation.
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: The error means the system cannot find the path specified.
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: If you are installing InnoDB, remember that you must create
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: directories yourself, InnoDB does not create them.
May  8 08:42:24 rps-reportdb02 mysqld: 120508  8:42:24  InnoDB: Error: trying to open a table, but could not
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: open the tablespace file './igrp_238/#sql-7e11_effe#P#p2012@002d01@002d30.ibd'!
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: Have you moved InnoDB .ibd files around without using the
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: It is also possible that this is a temporary table #sql...,
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: and MySQL removed the .ibd file for this.
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: Please refer to
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
May  8 08:42:24 rps-reportdb02 mysqld: InnoDB: for how to resolve the issue.

The files that have been left are:

-rw-rw---- 1 mysql          mysql              25874 May  3 10:58 #sql-7e11_effe.frm
-rw-rw---- 1 mysql          mysql                504 May  3 10:58 #sql-7e11_effe.par
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p0.ibd
-rw-rw---- 1 mysql          mysql          230686720 May  3 15:20 #sql-7e11_effe#P#p2011@002d12@002d05.ibd
-rw-rw---- 1 mysql          mysql          255852544 May  3 15:11 #sql-7e11_effe#P#p2011@002d12@002d12.ibd
-rw-rw---- 1 mysql          mysql          268435456 May  3 14:56 #sql-7e11_effe#P#p2011@002d12@002d19.ibd
-rw-rw---- 1 mysql          mysql          230686720 May  3 14:47 #sql-7e11_effe#P#p2011@002d12@002d26.ibd
-rw-rw---- 1 mysql          mysql          201326592 May  3 14:42 #sql-7e11_effe#P#p2012@002d01@002d02.ibd
-rw-rw---- 1 mysql          mysql          322961408 May  3 14:38 #sql-7e11_effe#P#p2012@002d01@002d09.ibd
-rw-rw---- 1 mysql          mysql          331350016 May  3 14:29 #sql-7e11_effe#P#p2012@002d01@002d16.ibd
-rw-rw---- 1 mysql          mysql          318767104 May  3 14:20 #sql-7e11_effe#P#p2012@002d01@002d23.ibd
-rw-rw---- 1 mysql          mysql          318767104 May  3 14:11 #sql-7e11_effe#P#p2012@002d01@002d30.ibd
-rw-rw---- 1 mysql          mysql          301989888 May  3 14:00 #sql-7e11_effe#P#p2012@002d02@002d06.ibd
-rw-rw---- 1 mysql          mysql          306184192 May  3 13:52 #sql-7e11_effe#P#p2012@002d02@002d13.ibd
-rw-rw---- 1 mysql          mysql          293601280 May  3 13:39 #sql-7e11_effe#P#p2012@002d02@002d20.ibd
-rw-rw---- 1 mysql          mysql          331350016 May  3 13:20 #sql-7e11_effe#P#p2012@002d02@002d27.ibd
-rw-rw---- 1 mysql          mysql          322961408 May  3 13:09 #sql-7e11_effe#P#p2012@002d03@002d05.ibd
-rw-rw---- 1 mysql          mysql          297795584 May  3 13:01 #sql-7e11_effe#P#p2012@002d03@002d12.ibd
-rw-rw---- 1 mysql          mysql          289406976 May  3 12:55 #sql-7e11_effe#P#p2012@002d03@002d19.ibd
-rw-rw---- 1 mysql          mysql          251658240 May  3 12:50 #sql-7e11_effe#P#p2012@002d03@002d26.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d04@002d02.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d04@002d09.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d04@002d16.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d04@002d23.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d04@002d30.ibd
-rw-rw---- 1 mysql          mysql              81920 May  3 10:58 #sql-7e11_effe#P#p2012@002d05@002d07.ibd

What is the resolution to this?

A further comment, I am running MySQL 5.5.23 on CentOS 5.8 x86_64

I have tried doing: DROP TABLE `#sql-7e11_effe` as suggested in the URL, but this does not work as it cannot find the table.