Table partitioning on external directory hitting "can't create table" error

Question

I am trying to partition my tables by RANGE with the following command:

ALTER TABLE  main_table
    PARTITION BY RANGE (TO_DAYS(date_val))(
     PARTITION old_db VALUES Less Than (TO_DAYS('2021-01-01'))
       DATA DIRECTORY = "/target/directory/",
     PARTITION new_db VALUES Less Than MAXVALUE
    );

I am able to do so with the same command and data table on my laptop but I hit this error on the server: ERROR 1005 (HY000): Can't create table test.main_table (errno: 168 "Unknown (generic) error from engine")

I sudo chown the external directory to "mysql" and restarted the mysql service and I am still getting this error.

Anybody have any ideas on how to solve this? Thanks!

This is the docs I am following: https://mariadb.com/kb/en/partitions-files/

My server DB version is on: 10.3.31-MariaDB

Answer 1

Realised one of my server's security policy was blocking mysqld from creating the mysql directories. Resolved

Update: OS version: Linux RHEL 7.4. I realised it was SELinux which was blocking the mysqld after each system reboot. I followed this post to test it out and configure SELinux to 'whitelist' mysqld - https://blogs.oracle.com/mysql/selinux-and-mysql-v2