MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

innodbMySQLmysql-5.7tablespaces

I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk directory. I keep getting this error after running the following statement taken from mysql page directly

CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table 
     DATA DIRECTORY = '/home/egonzalez/Documentos';

ERROR 1030 (HY000): Got error 168 from storage engine

As far as I know, is a permission problem that mysql doesn't have access to the directory, but after changing permission in the directory I don't know how to proceed.

Best Answer

If you want the table created in that data directory with .ibd file, do this

chown -R mysql:mysql /home/egonzalez/Documentos

Then, run this

CREATE TABLE t2 (c1 INT PRIMARY KEY)
ENGINE=InnoDB DATA DIRECTORY = '/home/egonzalez/Documentos';

The option innodb_file_per_table is enabled by default in MySQL 5.7

The reason for the error in the first place ?

You need to run CREATE TABLESPACE like this

mysql> CREATE TABLESPACE `ts1` 
    ->     ADD DATAFILE '/home/egonzalez/Documentos/mytblspc.ibd' 
    ->     ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

Then, you could create any table into be included in the one tablespace like

CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE=InnoDB TABLESPACE ts1;
Related Question