MySQL Forums
Forum List  »  InnoDB

mysql create table with data directory: access denied
Posted by: Brian Lee
Date: February 19, 2021 10:22PM

I have my main database on an SSD but I also want to hold semi-temporary data in a ramdisk. This is all on Ubuntu with mySQL 8.0.23. However, I'm unable to issue the following query:

mysql> create table t1 (c1 int) data directory = '/mnt/ramdisk/mysql';

ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' from storage engine

error.log shows this:

2021-02-20T03:56:12.354504Z 15 [ERROR] [MY-012592] [InnoDB] Operating system error number 13 in a file operation.
2021-02-20T03:56:12.354517Z 15 [ERROR] [MY-012595] [InnoDB] The error means mysqld does not have the access rights to the directory.
2021-02-20T03:56:12.354530Z 15 [ERROR] [MY-012126] [InnoDB] Cannot create file '/mnt/ramdisk/mysql/db/t5.ibd'

However, I can do it without the data directory option, and I can do it to /tmp.

I have the directory in innodb_directories:

mysql> show variables like 'innodb_directories';
| Variable_name | Value |
| innodb_directories | /mnt/ramdisk/mysql |

Permissions to the directory seem okay:

root@localhost:~# ls -ld /mnt/ramdisk/mysql/db
drwxr-xr-x 2 mysql mysql 40 Feb 19 10:59 /mnt/ramdisk/mysql/db

The user has "file" privileges.

/mnt/ramdisk/mysql/db is owned by mysql:mysql just like /var/lib/mysql is.

I did an strace on mysqld to see what was causing the issue and got this:

99911 openat(AT_FDCWD, "/mnt/ramdisk/mysql/db/t1.ibd", O_RDWR|O_CREAT|O_EXCL, 0640) = -1 EACCES (Permission denied)

Not knowing what all those flags are, I wrote a quick C program to do the same thing... and it worked. It's not even that the mysql user (running mysqld) can't do it, since the following worked:

sudo -u mysql <the C program>

Any pointers? Thanks...

Options: ReplyQuote

Written By
mysql create table with data directory: access denied
February 19, 2021 10:22PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.