Mysql – InnoDB file per tablespace

innodbmariadbMySQL

I am using following syntax to create an InnoDB table:

create table InnoDB_Test(a INT) 
DATA DIRECTORY='/var/log/storage/MySQL' 
INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;`

I want to create table which can store data over mounted partition(/var/log/storage i.e. /dev/sda6), but it is storing the data in /var/lib/mysql.

Files related to table i.e. .idb and .frm files are created there. It is not creating .isl file and neither the directory which is to be created in the directory mentioned.

But if I create the table with MyISAM engine then it is working fine. All files are created and data is getting stored in mounted partition.

I have enabled innodb_file_per_table, have_symlink global variables.

I am using 5.5.44-MariaDB MariaDB Server, CentOS 7.1

My partitions are as Follows:

/dev/sda2       6.5G  3.6G  2.6G  59% /
/dev/sda1       395M   32M  338M   9% /boot
/dev/sda6       5.3G   22M  5.0G   1% /var/log/storage`

So please tell me how to store data of InnoDB table in mounted partition?

Best Answer

Your problem is very simple

INDEX DIRECTORY is not supported for InnoDB, only MyISAM

create table InnoDB_Test(a INT) 
DATA DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;

is all you need

UPDATE 2016-04-15 07:02 EDT

You just said in your comment

Thanks Ronaldo for your answer. I try with removing INDEX DIRECTORY option but it is not working. Even if i give other directory("/root/MySQL") other than mounted one, it is still not working. It gives a warning with code 1618 " option ignored"

According to the MariaDB Documentation on CREATE TABLE

These options are ignored if the NO_DIR_IN_CREATE SQL_MODE is enabled (useful for replication slaves).

If you set your sql_mode it not have NO_DIR_IN_CREATE, you should be good to go.

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Mysql – InnoDB tables inaccessible after reboot

Look at the .frm files. They are owned by root root.

They are supposed to be owned by mysql mysql

My guess is that you copied the data rather than use MySQL techniques to import.

Perhaps you should change the ownership of the entire data folder.

Please run the following commands:

cd /var/www/xampp/mysql/data
chown -R mysql:mysql *

Then restart mysql.

As for the error messages, the InnoDB System Tablespace (ibdata1) and the Redo Log Files (ib_logfile0,ib_logfile1) are being created.

From the above messages, my guess is that you did this something like this

You had shutdown mysql
You copied /var/lib/mysql to /var/www/xampp/mysql/data
You started mysql
mysqld did not see InnoDB Infrastructure files, so they were created

If you have an all-MyISAM database (every table uses the InnoDB storage engine) you can easily get away with copying data folders. You would still have to change file ownership to mysql mysql. If there are any InnoDB tables, you must copy the entire data folder, not just a database within the data folder.

CAVEAT

Please note that running SHOW TABLES; simply walks across the current database telling you table names by looking at the .frm files without checking ownership or table validity. Once you actually perform some SQL again the table that all metadata checks against the table are done.