Bug #79151 error -1 from storage engine while creating table with non-existing datadir
Submitted: 6 Nov 2015 9:10 Modified: 5 Jul 2016 8:59
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.6.27, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[6 Nov 2015 9:10] Shahriyar Rzayev
Description:
Hi dear all,
There is another cryptic error message while trying to create tablespace outside default data directory:

mysql [localhost] {msandbox} (test) > create table t1(id int not null) data directory='/home/datadir';
ERROR 1030 (HY000): Got error -1 from storage engine

From error log:

2015-11-06 12:01:30 7f563ce21700  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.

In fact there is no such directory:

sh@percona-XPS-15:~$ ls /home/datadir
ls: cannot access /home/datadir: No such file or directory

How to repeat:
See description

Suggested fix:
Adding more informative error message
[6 Nov 2015 11:06] Umesh Shastry
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[6 Nov 2015 11:07] Umesh Shastry
// 5.6.27 

[root@cluster-repo ~]# mysql -uroot test
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 13
Server version: 5.6.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 t11(id int not null) data directory='/home/datadir';
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)

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

mysql>

// Exatrct from error log

Version: '5.6.27'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2015-10-09 19:43:37 7f0be5e81700  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.
mysql>
[26 Feb 2016 8:11] AA A
Has there been a fix? We're facing this on 5.6.28-0ubuntu0.14.04.1-log (Ubuntu)
[18 Mar 2016 7:39] Shahriyar Rzayev
With MySQL 5.7.11 there is a different, still unclear error:

mysql> create table t8(id int) data directory='/home/kkk';
ERROR 1030 (HY000): Got error 168 from storage engine

2016-03-18T07:38:39.895337Z 5 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2016-03-18T07:38:39.895354Z 5 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory
[5 Jul 2016 8:59] Shahriyar Rzayev
Same with partitioned tables with own tablespace:

mysql [localhost] {msandbox} ((none)) > CREATE TABLE `employees` (
    ->   `id` int(11) NOT NULL,
    ->   `store_id` int(11) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY RANGE (store_id)
    -> (PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = '/opt/p0' ENGINE = InnoDB,
    ->  PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = '/opt/p1' ENGINE = InnoDB,
    ->  PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = '/opt/p2' ENGINE = InnoDB,
    ->  PARTITION p3 VALUES LESS THAN (21) ENGINE = InnoDB) */
    -> ;
ERROR 1030 (HY000): Got error -1 from storage engine
[7 Nov 2016 16:32] OCA Admin
Contribution submitted via Github - Bug #79151 - error -1 from storage engine while creating table with non-existing 
(*) Contribution by Elshad Aghazade (Github elshadaghazade, mysql-server/pull/108#issuecomment-258644823): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_92425970.txt (text/plain), 1016 bytes.