Mysql – “Error in query (1030): Got error 168 from storage engine” when creating table

MySQLmysql-5.7

I'm trying to create a table on a separate directory, like this:

CREATE TABLE `abcdef` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/';

But it returns the error:

Error in query (1030): Got error 168 from storage engine

In the log it says:

2018-09-17T09:43:22.412636Z 2 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-17T09:43:22.412686Z 2 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

The directory /media/sdc/mysql/ exists and I ran the following commands already:

chown -R mysql:mysql /media/sdc/mysql
chmod -R 777 /media/sdc/mysql

I'm using MySQL 5.7.

I have another server running MySQL 5.6 where I created the same table with no issue. In fact I'm replicating it on a new upgraded server.

So why can't MySQL use /media/sdc/mysql?

Best Answer

apparmor (and selinux) provide additional access controls beyond permissions that can cause these permission denied (13) errors. This occurs generally on systems where data or log files differ from the default.

With apparmor you would add to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:

/media/sdc/mysql/ r,
/media/sdc/mysql/** rwk,

And then reload the apparmor service:

service apparmor reload

If it was selinux:

semanage fcontext -a -t mysqld_db_t "/media/sdc/mysql(/.*)?"
restorecon -Rv /media/sdc/mysql
Related Question