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 3:33	Modified:	21 Oct 2010 7:29
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0, 5.1	OS:	Linux (linux)
Assigned to:	Vasil Dimov	CPU Architecture:	Any
Tags:	assertion, crash, innodb, temporary table, tmpfs

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.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

The mysql log file directed me here just now.

It happened on 5.5.6-rc on fc14 and running as a slave to another 5.5.6-rc fc14 machine, which is in turn a slave to a remote 5.1.47 fc12 over a compressed ssh tunnel.

Perhaps it is just a notice, but it doesn't say that it retried without O_DIRECT. Has this regressed into 5.5.6rc?

Also appeared to create a temp table in innodb format instead of the usual myisam.

slave 1:
101014 12:53:15  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sqlbfc_2_0.ibd: CREATE: Invalid argument, continuing anyway
101014 12:53:15  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
101014 12:53:15  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sqlbfc_2_0.ibd: OPEN: Invalid argument, continuing anyway
101014 12:53:15  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662

slave2
101014 12:53:15  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sql7d1f_3f3_3.ibd: CREATE: Invalid argument, continuing anyway
101014 12:53:15  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
101014 12:53:15  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sql7d1f_3f3_3.ibd: OPEN: Invalid argument, continuing anyway
101014 12:53:15  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662

Hello Andrew,

Yes, in 5.5 the default storage engine has been changed to InnoDB, so now when you do "CREATE TABLE t (a INT);" or "CREATE TEMPORARY TABLE t (a INT);" it will be an InnoDB table (of course you can append engine=myisam if you want it to be myisam table instead).

The message says that InnoDB failed to set O_DIRECT on the already opened file and that it continues anyway. There is nothing to retry without O_DIRECT.

The message is harmless but it tells you that your setting innodb_flush_method=O_DIRECT is not being used for that particular table.

To prevent the message from appearing either create your temp tables in myisam format or remove O_DIRECT from innodb_flush_method or use non-tmpfs filesystem for temp tables.

Thank you!

Come on now, suggesting to remote O_DIRECT from a configuration cannot be serious. It's used for a reason: performance.

InnoDB might work out on that a device does not support O_DIRECT, and thus not report this warning in the errorlog. Having irrelevant errors in the errorlog can hide other problems through information overload.

I assert that this is still a bug and it should be fixed.
thanks.

Arjen,

"To prevent the message from appearing either create your temp tables in myisam format or remove O_DIRECT from innodb_flush_method or use non-tmpfs filesystem for temp tables."

That said any of the 3 alone would silence the message:

* create your temp tables in myisam format
* remove O_DIRECT from innodb_flush_method
* use non-tmpfs filesystem for temp tables

The message is there for a reason - InnoDB cannot fulfill a request from the user - the user has requested to use O_DIRECT, but it cannot be used. Even if InnoDB checks that the filesystem does not support O_DIRECT, then a similar message _must_ be printed: "Filesystem XYZ does not support O_DIRECT".

I hope you agree that in such cases the user must be notified somehow. Otherwise he may think that O_DIRECT is being used while it is actually not being used.

The original bug is about MySQL _crashing_ and it has been fixed, thus I will close this bug report.

Thanks!

Then add an option to suppress this silly message so an intelligent DBA can put her temporary files on a filesystem that does not support O_DIRECT, but still get all the benefits of O_DIRECT on her filesytems that do.

I do not see the harm in the user seeing ONE of these messages in the log, but seeing hundreds or even thousands of them is not useful.

Or, add a default_temp_storage_engine option to force temporary tables to be in myisam format.  It is unreasonable to expect to change an entire application to get rid of useless messages.

Just found this bug through the error log. MySQL 5.5.7rc did not crash, but restarted. I believe this is not an expected behaviour.

101207  2:57:52  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sql394e_281_0.ibd: CREATE: Invalid argument, continuing anyway
101207  2:57:52  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
101207  2:57:52  InnoDB: Failed to set O_DIRECT on file /dev/shm/#sql394e_281_0.ibd: OPEN: Invalid argument, continuing anyway
101207  2:57:52  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
101207  3:08:00  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/#sql394e_281_0.ibd
InnoDB: File operation call: 'aio read'.
InnoDB: Cannot continue operation.
101207 03:08:01 mysqld_safe Number of processes running now: 0
101207 03:08:01 mysqld_safe mysqld restarted

5.5.12... caused a server restart/crash recovery.  Still not really fixed, IMHO.

110714 16:42:01  InnoDB: Failed to set O_DIRECT on file /data01/ramdisk2G/#sqlf4
a_2c1445_0.ibd: CREATE: Invalid argument, continuing anyway
110714 16:42:01  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Li
nux on tmpfs, see MySQL Bug#26662
110714 16:42:01  InnoDB: Failed to set O_DIRECT on file /data01/ramdisk2G/#sqlf4
a_2c1445_0.ibd: OPEN: Invalid argument, continuing anyway
110714 16:42:01  InnoDB: O_DIRECT is known to result in 'Invalid argument' on Li
nux on tmpfs, see MySQL Bug#26662
110714 16:42:01  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.5/en/operating-system-error-codes.html
InnoDB: File name /data01/ramdisk2G/#sqlf4a_2c1445_0.ibd
InnoDB: File operation call: 'aio write'.

For those who experience similar problem in 5.5, see bug 58421 (fixed in 5.5.21)

Totally disagree with the comment by "[21 Oct 2010 7:29] Vasil Dimov". I use in-memory tmp file system and O_DIRECT for performance reasons. And I do not want to switch temp tables to by MySAM either.

Having that warning as repeated thousands of times does not help. It shoudl be issued once per FS.  Please fix it.

Hi Dima,

I filed this bug to snooze the messages : 

http://bugs.mysql.com/bug.php?id=70298

Best regards,

Arnaud

Does this also affect ramfs?

Thanks