Bug #43141 innodb_data_home_dir does not affect for tablespace files
Submitted: 24 Feb 2009 11:45 Modified: 24 Feb 2009 14:36
Reporter: henri pelkonen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:MySQL-server-community-5.1.30-0 OS:Linux (2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: innodb_data_home_dir, Tablespace

[24 Feb 2009 11:45] henri pelkonen
Description:

Separated tablespace files are not located in 'innodb_data_home_dir'.

Documentation says following:

innodb_data_home_dir

The common part of the directory path for all InnoDB data files. The default value is the MySQL data directory. If you specify the value as an empty string, you can use absolute file paths in innodb_data_file_path. 

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_data_home_dir

How to repeat:

[root@kalmari /]# egrep -v "^#" /etc/my.cnf |grep inno
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 3G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_data_home_dir = /ibd
innodb_file_per_table
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[root@kalmari /]#
[root@kalmari /]#
[root@kalmari /]# /etc/init.d/mysql start
Starting MySQL..........................................................................................          [  OK  ]
[root@kalmari /]#
[root@kalmari /]#
[root@kalmari /]#
[root@kalmari /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like '%inno%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 16777216               |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 3221225472             |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            | /ibd                   |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 2                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 120                    |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 268435456              |
| innodb_log_files_in_group       | 3                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 16                     |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+
34 rows in set (0.00 sec)

mysql> create database ibd_testi;
Query OK, 1 row affected (0.00 sec)

mysql> use ibd_testi;
Database changed
mysql> CREATE TABLE `TEST_TBL_ONE` (
    ->   `NUMBER` tinyint(3) unsigned NOT NULL,
    ->   `NAME` varchar(20) NOT NULL,
    ->   `TYPE` smallint(5) unsigned NOT NULL ,
    ->   PRIMARY KEY (`NUMBER`)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE (NUMBER)(
    -> PARTITION `100` VALUES LESS THAN (100) ENGINE=InnoDB,
    -> PARTITION `200` VALUES LESS THAN (200) ENGINE=InnoDB,
    -> PARTITION `maxvalue` VALUES LESS THAN MAXVALUE ENGINE = InnoDB
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> quit
Bye
[root@kalmari /]# date
Tue Feb 24 13:31:00 EET 2009
[root@kalmari /]# ls -lart /ibd/
total 10268
drwxr-xr-x 26 root  root      4096 Feb 24 13:06 ..
drwxr-xr-x  2 mysql mysql     4096 Feb 24 13:21 .
-rw-rw----  1 mysql mysql 10485760 Feb 24 13:31 ibdata1
[root@kalmari /]# ls -lart /var/lib/mysql/ibd_testi/
total 332
-rw-rw---- 1 mysql mysql    65 Feb 24 13:30 db.opt
drwxr-xr-x 6 mysql mysql  4096 Feb 24 13:30 ..
-rw-rw---- 1 mysql mysql    40 Feb 24 13:30 TEST_TBL_ONE.par
-rw-rw---- 1 mysql mysql  8670 Feb 24 13:30 TEST_TBL_ONE.frm
drwx------ 2 mysql mysql  4096 Feb 24 13:30 .
-rw-rw---- 1 mysql mysql 98304 Feb 24 13:31 TEST_TBL_ONE#P#maxvalue.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 24 13:31 TEST_TBL_ONE#P#200.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 24 13:31 TEST_TBL_ONE#P#100.ibd

[root@kalmari /]# rpm -qi MySQL-server-community-5.1.30-0.rhel5
Name        : MySQL-server-community       Relocations: (not relocatable)
Version     : 5.1.30                            Vendor: Sun Microsystems, Inc.
Release     : 0.rhel5                       Build Date: Tue 18 Nov 2008 05:34:21 AM EET
Install Date: Tue 30 Dec 2008 04:01:11 PM EET      Build Host: frigg35
Group       : Applications/Databases        Source RPM: MySQL-community-5.1.30-0.rhel5.src.rpm
Size        : 44842685                         License: Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.  All rights reserved.  Use is subject to license terms.  Under GPL license as shown in the Description field.
Signature   : DSA/SHA1, Fri 21 Nov 2008 04:44:32 PM EET, Key ID 8c718d3b5072e1f5
Packager    : Sun Microsystems, Inc. Product Engineering Team <build@mysql.com>
URL         : http://www.mysql.com/
Summary     : @COMMENT@ for Red Hat Enterprise Linux 5

Suggested fix:

Fix to work as described in documentation. Needed when innodb datafiles are stored for external disc rack, innodb_file_per_table is in use and files are created daily  etc.
[24 Feb 2009 14:36] Sveta Smirnova
Thank you for the report.

But according to http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html:

With multiple tablespaces enabled, InnoDB  stores each newly created table into its own tbl_name.ibd  file in the database directory where the table belongs. 

So this is not a bug.
[9 May 2009 3:13] Ben Krug
It's true that it follows described behavior of innodb_file_per_table, but it still seems to not follow the described behavior of innodb_data_home_dir.  Perhaps they are contradictory in this case.

It would be nice if database subdirectories were created under the innodb_data_home_dir for InnoDB tables when using innodb_file_per_table, and those were used.