MySQL Bugs: #26662: mysqld assertion when creating temporary (InnoDB) table on a tmpfs filesystem

Bug #26662	mysqld assertion when creating temporary (InnoDB) table on a tmpfs filesystem
Submitted:	27 Feb 2007 4:33	Modified:	30 Apr 2007 16:42
Reporter:	Arjen Lentz
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.0, 5.1	OS:	Linux (linux)
Assigned to:	Vasil Dimov	Target Version:
Tags:	assertion, temporary table, innodb, tmpfs, crash

Description:
mysqld crashes when trying to create an (explicit) temporary table with InnoDB
(one-file-per-table setting) if the target filesystem is tmpfs.
It gets an error 22 (invalid argument)

The problem *may* be limited to InnoDB only.
Either way, this should be allowed/possible since tmpfs is a valid choice to make: tmpfs
can use any available memory before going to disk (in swap space).

See report below

mysql> create temporary table bleh like account;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: dbname

ERROR 2013 (HY000): Lost connection to MySQL server during query

Error log shows:
070226 18:18:36 InnoDB: Started; log sequence number 14 546641512
070226 18:18:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.34-enterprise-gpl' socket: '/mysql-logs/mysql.sock' port: 3306 MySQL
Enterprise Server (GPL)

070226 18:19:21 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means 'Invalid argument'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/mysqltmp/#sql1924_1_0.ibd
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
070226 18:19:21 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...
070226 18:19:24 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 14 546641723.
InnoDB: Doing recovery: scanned up to log sequence number 14 546641723
InnoDB: Last MySQL binlog file position 0 99480169, file name
/mysql-logs/univac-bin-all.000002
070226 18:19:24 InnoDB: Started; log sequence number 14 546641723
070226 18:19:24 InnoDB: Error: table `mysqltmp/#sql1924_1_0` does not exist in the InnoDB
internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
070226 18:19:24 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.34-enterprise-gpl' socket: '/mysql-logs/mysql.sock' port: 3306 MySQL
Enterprise Server (GPL)

How to repeat:
Create a tmpfs:
  mount -t tmpfs -o size=200m none /tmpfs

Relevant my.cnf settings:
  default_table_type = INNODB
  innodb_file_per_table
  tmpdir = /tmpfs/mysqltmp

mysql> CREATE TEMPORARY TABLE ...

Thank you for the report.

Bug is not repeatable on virtual 32-bit Ubuntu. Please provide output of SHOW TABLE STATUS
account;

Sveta, another possible relevant setting is
  innodb_flush_method = O_DIRECT

Sinisa reckons the assert fail may be due to that - the OS open() call might report
O_DIRECT to be the invalid parameter for tmpfs.

In that case, a possible fix could be to catch this specific error, and try again without
O_DIRECT - for a smart fix, anything with the same path could be handled that way in the
future. InnoDB could keep track of the flag separately for tmpdir and datadir, there
should not be any other paths.

Thank you for the additional comment.

Verified as described with option "innodb_flush_method = O_DIRECT"

Verified using empty table and 5.0.34 server.

Hmm... the retry fix of http://bugs.mysql.com/bug.php?id=9709 could also test a
non-O_DIRECT open if O_DIRECT fails.

Heikki, if it is related then the open call should immediately be re-tried without
O_DIRECT, not sleep one second first.

Grab

Fix for this bug has been committed to the 5.0 and 5.1 InnoDB svn trees.

Generally it calls open(2) without O_DIRECT and then tries to set it using fcntl(2), this
way an error caused by O_DIRECT not being supported can easily be distinguished by other
errors and skipped.

Additionally, on Solaris directio() is called instead of fcntl(). Thus
"innodb_flush_method = O_DIRECT" should now work on Solaris too.

Patch queued into 5.0- and 5.1-maint, will be merged up to top repos as soon as possible.

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
currently reads:

If O_DIRECT is specified (available on some GNU/Linux versions), InnoDB uses O_DIRECT to
open the data files

it should be changed to something like:

If O_DIRECT is specified (available on some GNU/Linux versions, FreeBSD and Solaris),
InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files

Pushed into 5.0.42

Pushed into 5.1.18-beta

A note has been added to the 5.0.42 and 5.1.18 changelogs. 

The documentation has also been updated to show the change in open() method.