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
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:
My server DB version is on:
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.
Realised one of my server’s security policy was blocking mysqld from creating the mysql directories. Resolved
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 🙂