MySQL Bugs: #58421: InnoDB temporary table crash when tmpdir on Linux tmpfs

Bug #58421	InnoDB temporary table crash when tmpdir on Linux tmpfs
Submitted:	23 Nov 2010 16:37	Modified:	6 Feb 2012 20:44
Reporter:	Mark Robson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.7	OS:	Linux (Centos 5.5 x86_64)
Assigned to:	Inaam Rana	CPU Architecture:	Any
Tags:	linux tmpfs aio

Description:
With asynch IO enabled under Linux, which it now is by default, putting tmpdir in a tmpfs crashes on innodb temporary tables because aio is not supported on tmpfs in some kernel versions, including the ones shipped with Centos 5.5

Example error log:

101123  2:10:59  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.1/en/operating-system-error-codes.html
InnoDB: File name /dev/shm/#sqlc99_2_0.ibd
InnoDB: File operation call: 'aio write'.
InnoDB: Cannot continue operation.

I am reporting this from an original report by Ronald Bradford here:

http://ronaldbradford.com/blog/unexpected-mysqld-crashing-in-5-5-2010-11-22/

How to repeat:
On Centos 5.5

1. Set tmpdir to a tmpfs, for example /dev/shm
2. Restart mysql as required
3. Create an innodb temporary table e.g. 

create temporary table t1(id int) engine=InnoDB;

Expected result:

* Temporary table created

Actual result:

* Crash (see log above)

Suggested fix:
InnoDB could check at startup whether aio is supported on tmpdir as well as datadir and use it only if it is on both, or use it selectively depending on which, if any, it is available on.

More recent Linux kernels support aio on tmpfs, but these are not a realistic option for most users (of supported distributions)

There was another report about same recently. Somebody replied that you will need to use Kernel > some specific version to use TEMPFS asychroneously like this.

Thank you for the report.

This is duplicate of bug #58282

Workaround: use option innodb_use_native_aio

This is not a duplicate of Bug #58282, it's related.

This bug is about fixing the server, the other bug is about a (partial) workaround for mtr. That will of course not fix the problem for a server not started via mtr.

For what it is worth, I have not seen this bug on my laptop, even though I always run InnoDB on tmpfs.

Suggested fix: When InnoDB opens a file, it should post an aio operation to read something from the file. If it fails, attempt synchronous I/O and set a flag in the fil_space_t object so that synchronous I/O will be used for this file in the future.

Any fix for this also needs to undo the workaround in Bug #58282 (just delete the added lines), otherwise it won't get tested when run with mtr.

I have experienced these crashes in both Centos 5.5 and RHEL 5.6. Turning off innodb_use_native_aio fixed it in both cases.

Added to changelog for 5.5.21 / 5.6.5:

The server could crash when creating an InnoDB temporary table under
Linux, if the $TMPDIR setting points to a tmpfs filesystem and
innodb_use_native_aio is enabled, as it is by default in MySQL 5.5.4
and higher. The entry in the error log looked like:

101123 2:10:59 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means Invalid argument.

The crash occurred because asynchronous I/O is not supported on tmpfs in some Linux kernel
versions. The workaround was to turn off the innodb_use_native_aio setting or use a different temporary directory. The fix causes InnoDB to turn off the innodb_use_native_aio setting automatically if it detects that the temporary file directory does not support asynchronous I/O.