MySQL Bugs: #70298: [ERROR] InnoDB: Failed to set O

Bug #70298	[ERROR] InnoDB: Failed to set O_DIRECT on file
Submitted:	11 Sep 2013 12:12	Modified:	27 Sep 2013 13:01
Reporter:	Arnaud Adant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating an InnoDB temporary table if tmpdir mounted on tmpds, this message is printed to the error log :

2013-09-11 13:39:55 22656 [ERROR] InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
2013-09-11 13:39:55 22656 [ERROR] InnoDB: Failed to set O_DIRECT on file /export/home/aadant/tmpdir/#sql5880_1fa3_4.ibd: OPEN: Invalid argument, continuing anyway

Yes, the table is created :

mysql> drop temporary table tmp;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp(id int) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tmp(id) values(1);
Query OK, 1 row affected (0.03 sec)

ls  /export/home/aadant/tmpdir -al
total 120
drwxrwxrwt  2 root   root      80 Sep 11 13:22 .
drwxr-xr-x 21 aadant root    4096 Sep 11 13:10 ..
-rw-rw----  1 aadant common  8556 Sep 11 13:22 #sql5880_1fa3_0.frm
-rw-rw----  1 aadant common 98304 Sep 11 13:22 #sql5880_1fa3_0.ibd 

How to repeat:
1. create a tmpfs mount point :

sudo  mount -t tmpfs -o size=128M tmpfs /export/home/aadant/tmpdir

2. use this directory as tmpdir

tmpdir = ....

3. 

use test;
create temporary table tmp(id int) engine=InnoDB;
select * from tmp;

Suggested fix:
InnoDB should not print any message when writing a temporary table in tmpdir
and when O_DIRECT is set.

[25 Sep 2013 5:05] Daniel T Price

Noted in 5.6.15 changelog.

Setting the "O_DIRECT" flag on a file on "tmpfs" on some operating
systems would result in an error printed to the error log. Creating
multiple temporary tables on "tmpfs" would cause the error to be printed
repeatedly. The error message has been changed to a warning that is only
printed once when running "CREATE TABLE" multiple times.

5.6$ bzr log -r 5469
------------------------------------------------------------
revno: 5469
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.6
timestamp: Mon 2013-09-23 21:44:36 +0530
message:
  Bug #17441867 [ERROR] INNODB: FAILED TO SET O_DIRECT ON FILE
  
  PROBLEM
  -------
  
  Setting the O_DIRECT flag on file on tmpfs fails 
  and error messages are printed in error logs.
  The customer was creating lot of temporary tables 
  in tmpfs and because of which error log was 
  flooded with these error messages. We however 
  ignore these errors and continue to create the
  tables.These error messages were causing problem 
  with the their monitoring system.
  
  FIX
  ---
  
  The fix is to downgrade the error with a warning
  and printing this warning only once in the error
  log.

Agree on downgrade to warning.

And, if there would ever be talk about dropping the warning altogether, I would disagree - this warning is handy for QA to establish what happened exactly and thereby to ensure 100% reproducible testcases.