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 16:14	Modified:	16 Jan 2007 6:23
Reporter:	Jeff C
Status:	Closed
Category:	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:	Sergey Gluhov	Target Version:
Tags:	temporary tables, innodb, bfsm_2006_12_07, Q1

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.