Cannot create external database table with the DATA DIRECTORY clause

Question

I would like to create a database table in an external file outside of the standard data directory.

The following works:

create table eh_extern (c1 integer) engine=innodb data directory = '/tmp';

I created a test subdirectory of my home directory and set its permissions to 777 and its owner to mysql:mysql

Directory permissions are:

drwxrwxrwx 2 mysql mysql 4096 Nov 17 09:55 test

The following does not work:

create table eh_extern (c1 integer) engine=innodb data directory = '/home/tx-team/test'; The error is:

Can't create table eh.eh_extern(errno: 168 "Unknown (generic) error from engine")

The error in the syslog reads:

`Nov 17 09:56:39 txteam-PD14RI mariadbd[1149]: 2023-11-17 9:56:39 75 [ERROR] InnoDB: Operating system error number 13 in a file operation.

Nov 17 09:56:39 txteam-PD14RI mariadbd[1149]: 2023-11-17 9:56:39 75 [ERROR] InnoDB: The error means mariadbd does not have the access rights to the directory. `

What can I do to prevent the error?

OS is: Linux 5.15.0-88-generic (Ubuntu), Server is: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Answer 1

Systemd started services don't have access to /home directories.

systemctl edit mariadb.service and add

[Service]
ProtectHome=false

ref: KB systemd