MySQL Bugs: #79200: InnoDB: "data directory" option of create table fails with pwrite() OS error 22

Bug #79200	InnoDB: "data directory" option of create table fails with pwrite() OS error 22
Submitted:	10 Nov 2015 8:58	Modified:	12 Feb 2016 15:38
Reporter:	Frank Ullrich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6.26 and 5.6.27	OS:	Debian (Jessie 64 bit)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
From version 5.6.26 onwards InnoDB fails to create a table with explicit 'data directory' option: "ERROR 1030 (HY000): Got error -1 from storage engine".
Error Log shows:
"2015-11-10 09:32:34 7f6ae11777002015-11-10 09:32:34 23283 [ERROR] InnoDB: Failure of system call pwrite(). Operating system error number is 22.
InnoDB: Error number 22 means 'Invalid argument'."
The directory for the DB in the given data directory will be created but not the table file.
This bug is not there in earlier versions. I tested 5.6.20, 5.6.23, 5.6.24 and 5.6.25.
But it is also present in 5.6.27 (latest version).
I use these Linux Binaries: Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive.
There is very serius because we use dedicated disc layouts with separation of myisam and innodb file, logs etc. and need to put some tables in specific, suitable places aside from other files (SSDs and the like). Workaround like symlinks would be clumsy and error-prone.

How to repeat:
Install MySQL 5.6.26 or 5.6.27 from tarball (Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive).
Provide directory /opt/db/mysql/port/5606/innodb owned by and read/write enabled for 'mysql' user.
Use innodb_file_per_table=ON.
In the mysql client issue:
create table ful (a int) engine=innodb DATA DIRECTORY='/opt/db/mysql/port/5606/innodb';

Suggested fix:
Remove Bug in InnoDB's pwrite() system call that was introduced in 5.6.26.

5.6.24:
mysql [localhost] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.24    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t1 (x int) engine=innodb data directory='/tmp';
Query OK, 0 rows affected (0.20 sec)

5.6.27:
mysql [localhost] {msandbox} ((none)) > select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 5.6.27    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+

mysql [localhost] {msandbox} (test) > create table t2 (x int) engine=innodb data directory='/tmp';
Query OK, 0 rows affected (0.13 sec)

[arhimed@gedora ~]$ ls -la /tmp/test
total 192
drwxrwx---  2 arhimed arhimed    80 Nov 10 12:12 .
drwxrwxrwt 14 root    root      360 Nov 10 12:13 ..
-rw-rw----  1 arhimed arhimed 98304 Nov 10 12:10 t1.ibd
-rw-rw----  1 arhimed arhimed 98304 Nov 10 12:12 t2.ibd

[arhimed@gedora ~]$ ls -la sandboxes/msb_5_6_24/data/test/t1.* sandboxes/msb_5_6_27/data/test/t*
-rw-rw---- 1 arhimed arhimed 8554 Nov 10 12:10 sandboxes/msb_5_6_24/data/test/t1.frm
-rw-rw---- 1 arhimed arhimed   16 Nov 10 12:10 sandboxes/msb_5_6_24/data/test/t1.isl
-rw-rw---- 1 arhimed arhimed 8554 Nov 10 12:12 sandboxes/msb_5_6_27/data/test/t2.frm
-rw-rw---- 1 arhimed arhimed   16 Nov 10 12:12 sandboxes/msb_5_6_27/data/test/t2.isl

[arhimed@gedora ~]$ cat sandboxes/msb_5_6_24/data/test/t1.isl
/tmp/test/t1.ibd[arhimed@gedora ~]$
[arhimed@gedora ~]$ cat sandboxes/msb_5_6_27/data/test/t2.isl
/tmp/test/t2.ibd[arhimed@gedora ~]$
[arhimed@gedora ~]$

Hi Frank,

Thanks for submitting the bug but I can't reproduce the problem. As you can see from my test 5.6.27 behaves exactly like 5.6.24, the table data file (ibd) is properly created in the data dictionary/dbname, isl file is properly created in @@datadir/dbname

> I use these Linux Binaries: 
> Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive.

That is exactly the same binary I used for testing

Can you try some "simpler" path? Maybe there is something in the "/opt/db/mysql/port/5606/innodb" path that is not ok? missing X permission in some of the directories in the path?

kind regards
Bogdan Kecman

Hi Bogdan,
taking release 5.6.27 I tried /opt/mytmp/ as a 'simpler' path but that fails, too. This path doesn't contain symlinks, belongs the OS user mysql and is 'drwxrwx--- 2 mysql mysql'.
As before the database subdirectory (/opt/mytmp/<dbname>) has been created but not the InnoDB table file. The latter has also __not__ been created elsewhere (in the MySQL data directory or the InnoDB data home for example).

Have you compared the source code between releases 5.6.25 and 5.6.26(27) with respect to InnoDB's pwrite() call and the demanded argument(s) to rule out any code changes there as the source of this behaviour?
You did not mention which OS you used for your tests ...

Best regards,
Frank

Hi Frank,

For testing I'm using some rather old Fedora 19.
There's nothing between 5.6.26 and 5.6.27 wrt pwrite, the diff between them is rather small.

Do you have SELINUX turned on?

take care
Bogdan Kecman

Hi Bogdan,
no SELINUX.
We're on Debian Jessie (as specified).

Regards
Frank

Hi,

did you, on the same system, try both 5.6.26 and 5.6.27? 

any other low level protection other then selinux like apparmor or grsecurity or something else? this error looks like a system problem unrelated to mysql... so mysql can't write to the place you want it to write ... try /tmp as your data directory destination, it's usually whitelisted in all these protection systems

all best
Bogdan Kecman

Hi Bogdan,

ls -la /var/tmp
total 8
drwxrwxrwt  2 root root 4096 2015-11-10 09:52 .
ls -la /tmp
total 48
drwxrwxrwt 11 root root 4096 2015-11-10 14:41 .

mysql> create table ful (a int) engine=innodb  data directory='/var/tmp/';
ERROR 1030 (HY000): Got error -1 from storage engine
mysql> create table ful (a int) engine=innodb  data directory='/tmp/';
ERROR 1030 (HY000): Got error -1 from storage engine
mysql> create table ful (a int) engine=innodb  data directory='/tmp';
ERROR 1030 (HY000): Got error -1 from storage engine
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.27-log |
+------------+

If it were OS-related then please explain why 5.6.25 and 5.6.26 should behave differently on the same machine.

Regards
Frank

Hi,

> If it were OS-related then please explain 
> why 5.6.25 and 5.6.26 should behave differently 
> on the same machine.

The only thing that comes to mind is the selinux & co where the old binary is allowed to write there and new one is not.

Do you have anything in the syslog or security log (mysql log should not show anything useful here but if you see there anything other then the error and explanation what 22 is then as I said it's not useful)?

In the meantime I'll check if anyone from the team is running jessie ( I tested this on Fedora19, RHEL6.6, RHEL7, CENTOS7 and Oracle Linux6.6 and it works as expected ) so we can see if the problem is with OS

all best
Bogdan Kecman

p.s. you are using Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz from dev.mysql.com ? You downloaded binary from dev.mysql.com you did not compile it your self?

Hi,

I found it :D

innodb_flush_method = O_DIRECT

causes the bug

mysql [localhost] {msandbox} (test) > create table t3 (x int) engine=innodb data directory='/tmp';
ERROR 1030 (HY000): Got error -1 from storage engine

and yes, it works on 5.6.24 and not on 5.6.27

so a workaround until bug is solved, don't use innodb_flush_method = O_DIRECT

thanks for cooperation, this definitely is a bug
Bogdan Kecman

How to reproduce:

setup 5.6.27
configure file per table
configure innodb_flush_method = O_DIRECT
exec create table t1 (x int) engine=innodb data directory='/tmp';

Posted by developer:
 
Fixed as of the upcoming 5.6.30, 5.7.12, 5.8.0 release, and here's the changelog entry:

A CREATE TABLE ... DATA DIRECTORY operation failed to create a table
while innodb_flush_method was set to O_DIRECT. 

Thank you for the bug report.

Posted by developer:
 
Changed Version Fixed to 5.6.30. Confirmed by Developer.