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...
Subject
Views
Written By
Posted
mysql create table with data directory: access denied
2074
February 19, 2021 10:22PM
547
February 19, 2021 10:24PM
550
February 19, 2021 11:56PM
555
February 20, 2021 06:33AM
610
February 21, 2021 08:20PM
491
July 30, 2021 08:36PM
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.