CODEWITHSUNDEEP
mysqlwindows-server-2008-r2
SammuelMiranda
SammuelMiranda

Reputation: 460

MySql (Windows Server) - Create Tables in Specific folder

I'm trying to create a table on MySql specifing DATA and INDEX DIRECTORY commands. The problem is that my server is a Windows plataform.

When I try:

CREATE TABLE db.tb (tb_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) DATA DIRECTORY 'd:\Abc';

It gives me an error.

Error Code: 1210. Incorrect arguments to DATA DIRECTORY

Upvotes: 1

Views: 1962

Answers (1)

VMai
VMai

Reputation: 10346

Your statement should be either

CREATE TABLE db.tb (
    tb_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) 
ENGINE=InnoDB
DATA DIRECTORY='d:/Abc';

or

CREATE TABLE db.tb (
    tb_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) 
ENGINE=InnoDB
DATA DIRECTORY='d:\\Abc';

because to write a backslash in a string you've got to double it and you were missing the = too as Syed Ali Taqi mentioned.

You should check that the innodb_file_per_table configuration option is enabled:

SELECT  @@innodb_file_per_table; -- should return 1

Documentation

Note:

In the case of InnoDB INDEX DIRECTORY will not be needed, because the .idb file contains both, data and indexes, see manual:

Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd extension, inside the database directory. This file contains the table data and any indexes for the table. File-per-table mode, controlled by the innodb_file_per_table option, affects many aspects of InnoDB storage usage and performance, and is enabled by default in MySQL 5.6.7 and higher.

Upvotes: 2

Related Questions