mariadb create table data directory doesn't work

Question

I'm trying to CREATE TABLE with data directory option following here.

I'm working on MariaDB 5.5 and the command list is below...

MariaDB [test]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> create table foo(c1 int) data directory='/home/mysql';
Query OK, 0 rows affected, 1 warning (0.36 sec)

MariaDB [test]> \! ls -al /var/lib/mysql/test    
drwx------. 2 mysql mysql    47  4월 24 18:02 .
drwxr-xr-x. 8 mysql mysql  4096  4월 24 17:55 ..
-rw-rw----. 1 mysql mysql    61  4월 24 17:55 db.opt
-rw-rw----. 1 mysql mysql  8556  4월 24 18:02 foo.frm
-rw-rw----. 1 mysql mysql 98304  4월 24 18:02 foo.ibd

MariaDB [test]> show warnings;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

I found that --skip-symbolic-link is disabled and I restarted mysqld with symbolic-links=TRUE in my.cnf

MariaDB [test]> show variables like 'have_symlink';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink  | YES   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> drop table foo;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> create table foo(c1 int) data directory='/external_data_dir';
Query OK, 0 rows affected (0.16 sec)

MariaDB [test]> \! ls -al /external_data_dir
total 4
drwxr-xr-x.  2 mysql mysql    6  4월 25 13:36 .
dr-xr-xr-x. 18 root  root  4096  4월 25 13:36 ..

MariaDB [test]> \! ls -al /var/lib/mysql/test
total 212
drwx------. 2 mysql mysql    47  4월 25 13:40 .
drwxr-xr-x. 8 mysql mysql  4096  4월 25 13:33 ..
-rw-rw----. 1 mysql mysql    61  4월 24 17:55 db.opt
-rw-rw----. 1 mysql mysql  8556  4월 25 13:40 foo.frm
-rw-rw----. 1 mysql mysql 98304  4월 25 13:40 foo.ibd

I don't think it works properly. What am I missing?

Thanks in advance.

Answer 1

----- 2012-08-07 5.6.6 Milestone 9 -- Functionality Added or Changed -- InnoDB -----

InnoDB now supports the DATA DIRECTORY='directory' clause of the CREATE TABLE statement, which allows you to create InnoDB file-per-table tablespaces (.ibd files) in a location outside the MySQL data directory.

For additional information, see Specifying the Location of a Tablespace.

You are asking about MariaDB 5.5, which predates that change. Back in those old times, MariaDB 5.5 tracked MySQL 5.5 rather closely, so I don't think MariaDB had the feature in your version. Recommend you upgrade. 10.0 probably has the feature, as well as 10.1 and 10.2 and 10.3.

And keep in mind that DATA DIRECTORY can be used on CREATE TABLE but not ALTER TABLE.

I see that my Answer disagrees with MariaDB CREATE TABLE doc; I cannot say where the discrepancy is. The message "<DATA DIRECTORY> option ignored" implies that I am right.

Answer 2

The problem is most probably generated by the fact that mysql/mariadb doesn't have the rights to write to that /external_data_dir

The solution highly depends on the OS that you are using.

First of all, no matter the Linux family, make sure that the directory exists and it is owned by the user mysql:

mkdir /external_data_dir
chown -R mysql.mysql /external_data_dir

Now, if your Linux is RHEL/CentOS/Fedora you will need to check whether SELinux is enabled and if yes, you will either disable it (not recommended) or set proper security context of that directory(the recommended approach)

# check if SELinux is enabled
getenforce
# if it outputs 'Enforcing' then SELinux is enabled

# set proper SELinux context 
chcon -R -t mysqld_db_t /external_data_dir

# you could disable SELinux with the command below but it is not recommended
setenforce 0

You can read more about SELinux and MySQL/MariaDB at this link: https://blogs.oracle.com/jsmyth/selinux-and-mysql

If your Linux is Debian/Ubuntu you might need to check AppArmor settings, which is kind of SELinux equivalent. I'm not experienced with AppArmor but I can tell only that it also can either be completely disabled or properly set to allow mysql access to a non-standard dir. As a starting point you might check this link: https://blogs.oracle.com/jsmyth/apparmor-and-mysql

You will need root/sudo access in order to run all the above stuff.