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.