MySQL Bugs: #73512: No useful errormsg. when trying to set innodb table datadir to non-writable path

Bug #73512	No useful errormsg. when trying to set innodb table datadir to non-writable path
Submitted:	8 Aug 2014 21:35	Modified:	11 Aug 2014 8:15
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.6.20, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When trying to have MyISAM place table data files in a DATA DIRECTORY not writable to mysqld a proper error message is returned

  mysql> create table t1(id int primary key) engine=myisam data directory = "/bin";
  ERROR 1 (HY000): Can't create/write to file '/bin/t1.MYD' (Errcode: 13 - Permission denied)

With InnoDB now supporting DATA DIRECTORY in 5.6 no useful error message is produced though:

  mysql> create table t1(id int primary key) engine=innodb data directory = "/bin";
  ERROR 1030 (HY000): Got error -1 from storage engine

  mysql> show warnings;
  +-------+------+----------------------------------+
  | Level | Code | Message                          |
  +-------+------+----------------------------------+
  | Error | 1030 | Got error -1 from storage engine |
  +-------+------+----------------------------------+
  1 row in set (0.00 sec)

And when trying to use a non-writable path for per-partition DATA DIRECTORY messages become even more confusing:

  mysql> create table t1(id int primary key) engine=innodb partition by key(id) (partition p1 data directory = "/tmp/foo");
  ERROR 1030 (HY000): Got error -1 from storage engine

  mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------+
  | Level | Code | Message                                                                     |
  +-------+------+-----------------------------------------------------------------------------+
  | Error | 1030 | Got error -1 from storage engine                                            |
  | Error |    6 | Error on delete of './test/t1.par' (Errcode: 2 - No such file or directory) |
  +-------+------+-----------------------------------------------------------------------------+
  2 rows in set (0.00 sec)

Originally ran into this with 5.6.16, there I at least got 

  2014-08-08 16:16:14 7fb287f38700  InnoDB: Operating system error number 13 in a file operation.
  InnoDB: The error means mysqld does not have the access rights to
  InnoDB: the directory.

but with latest 5.6.20 I don't even get this (both versions built from source, only changed CMAKE_INSTALL_PREFIX, MYSQL_DATADIR and ENABLE_DTRACE (to off), otherwise the builds should be identical and use the same my.cnf, too ...

(PS: what is 7fb287f38700 in the message above? MySQL thread id? OS thread handle? ...?)

How to repeat:
See Description 

Suggested fix:
Pass the real reason (here: Errcode 13 - Permission denied) to the SQL layer and not just "Got error -1 from storage engine"

Hello Hartmut,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// with 5.6.21

[ushastry@cluster-repo mysql-advanced-5.6.21]$ bin/mysql -u root -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 91
Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(id int primary key) engine=myisam data directory = "/bin";
ERROR 1 (HY000): Can't create/write to file '/bin/t1.MYD' (Errcode: 13 - Permission denied)
mysql>
mysql> create table t1(id int primary key) engine=innodb data directory = "/bin";
ERROR 1030 (HY000): Got error -1 from storage engine
mysql> show warnings;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1030 | Got error -1 from storage engine |
+-------+------+----------------------------------+
1 row in set (0.00 sec)