mysql create table with data directory: access denied

[Brian Lee]

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...