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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply