Description:
Using the "DATA DIRECTORY" option on a partitioned table may cause a conversion error when changing to a different storage engine, if the source table was not MyISAM or ARCHIVE.
The documentation is vague about the applicability of the "DATA|INDEX DIRECTORY" option. In practice, it applies to both MyISAM and Archive engines, but other storage engines accept the option without complaining.
The trouble happens when converting the table to a storage engine that actually supports this option.
For example, defining a non-existant date directory for a InnoDB table
will create the table. The only complain happens if the data directory difinition does not start with a '/'.
Given a system where the data directory is '/usr/local/mysql/data' and the paths '/usr/local/mysql/data/test/x0' and '/usr/local/mysql/data/test/x1' actually exist, a Innodb partitioned table referring to '/usr/local/mysql/data/test/x1' will be converted to MyISAM without errors.
If the data directory option refers to '/not_existing/usr/local/mysql/data/test/x1', then the conversion will fail.
--------------
select version()
--------------
+-------------------+
| version() |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.00 sec)
--------------
create schema if not exists test
--------------
Query OK, 0 rows affected, 1 warning (0.00 sec)
Note (Code 1007): Can't create database 'test'; database exists
--------------
drop table if exists t1, t2, t3
--------------
Query OK, 0 rows affected, 1 warning (0.07 sec)
Note (Code 1051): Unknown table 't3'
--------------
create table t1 (id int not null primary key) engine = innodb partition by range (id)
(
partition p0 values less than (1000)
data directory = '/not_existing/usr/local/mysql/data/test/',
partition p1 values less than (2000)
data directory = '/not_existing/usr/local/mysql/data/test/',
partition p2 values less than (MAXVALUE)
)
--------------
Query OK, 0 rows affected (0.35 sec)
--------------
create table t2 (id int not null primary key) engine = innodb partition by range (id)
(
partition p0 values less than (1000)
data directory = '/usr/local/mysql/data/test/x0',
partition p1 values less than (2000)
data directory = '/usr/local/mysql/data/test/x1',
partition p2 values less than (MAXVALUE)
)
--------------
Query OK, 0 rows affected (0.38 sec)
--------------
show tables
--------------
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
--------------
show create table t1
--------------
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000) DATA DIRECTORY = '/not_existing/usr/local/mysql/data/test/' ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/not_existing/usr/local/mysql/data/test/' ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)
--------------
show create table t2
--------------
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000) DATA DIRECTORY = '/usr/local/mysql/data/test/x0' ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/usr/local/mysql/data/test/x1' ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
--------------
alter table t2 engine=archive
--------------
ERROR 1005 (HY000) at line 30: Can't create table 'test.#sql-4a4c_17' (errno: 1)
--------------
alter table t1 engine=myisam
--------------
ERROR 1 (HY000) at line 31: Can't create/write to file '/not_existing/usr/local/mysql/data/test/#sql-4a4c_17#P#p0.MYD' (Errcode: 2)
How to repeat:
select version();
create schema if not exists test;
use test;
drop table if exists t1, t2, t3;
create table t1 (id int not null primary key) engine = innodb partition by range (id)
(
partition p0 values less than (1000)
data directory = '/not_existing/usr/local/mysql/data/test/',
partition p1 values less than (2000)
data directory = '/not_existing/usr/local/mysql/data/test/',
partition p2 values less than (MAXVALUE)
)
;
create table t2 (id int not null primary key) engine = innodb partition by range (id)
(
partition p0 values less than (1000)
data directory = '/usr/local/mysql/data/test/x0',
partition p1 values less than (2000)
data directory = '/usr/local/mysql/data/test/x1',
partition p2 values less than (MAXVALUE)
)
;
show tables;
show create table t1\G
show create table t2\G
alter table t2 engine=archive;
alter table t1 engine=myisam;
show create table t1\G
show create table t2\G
Suggested fix:
Storage engines that don't use the 'DATA|INDEX DIRECTORY' option should not accept it when the table is created.
Check for DATA|INDEX DIRECTORY names should be more robust than just looking for the first character.
Description: Using the "DATA DIRECTORY" option on a partitioned table may cause a conversion error when changing to a different storage engine, if the source table was not MyISAM or ARCHIVE. The documentation is vague about the applicability of the "DATA|INDEX DIRECTORY" option. In practice, it applies to both MyISAM and Archive engines, but other storage engines accept the option without complaining. The trouble happens when converting the table to a storage engine that actually supports this option. For example, defining a non-existant date directory for a InnoDB table will create the table. The only complain happens if the data directory difinition does not start with a '/'. Given a system where the data directory is '/usr/local/mysql/data' and the paths '/usr/local/mysql/data/test/x0' and '/usr/local/mysql/data/test/x1' actually exist, a Innodb partitioned table referring to '/usr/local/mysql/data/test/x1' will be converted to MyISAM without errors. If the data directory option refers to '/not_existing/usr/local/mysql/data/test/x1', then the conversion will fail. -------------- select version() -------------- +-------------------+ | version() | +-------------------+ | 5.1.12-beta-debug | +-------------------+ 1 row in set (0.00 sec) -------------- create schema if not exists test -------------- Query OK, 0 rows affected, 1 warning (0.00 sec) Note (Code 1007): Can't create database 'test'; database exists -------------- drop table if exists t1, t2, t3 -------------- Query OK, 0 rows affected, 1 warning (0.07 sec) Note (Code 1051): Unknown table 't3' -------------- create table t1 (id int not null primary key) engine = innodb partition by range (id) ( partition p0 values less than (1000) data directory = '/not_existing/usr/local/mysql/data/test/', partition p1 values less than (2000) data directory = '/not_existing/usr/local/mysql/data/test/', partition p2 values less than (MAXVALUE) ) -------------- Query OK, 0 rows affected (0.35 sec) -------------- create table t2 (id int not null primary key) engine = innodb partition by range (id) ( partition p0 values less than (1000) data directory = '/usr/local/mysql/data/test/x0', partition p1 values less than (2000) data directory = '/usr/local/mysql/data/test/x1', partition p2 values less than (MAXVALUE) ) -------------- Query OK, 0 rows affected (0.38 sec) -------------- show tables -------------- +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) -------------- show create table t1 -------------- *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000) DATA DIRECTORY = '/not_existing/usr/local/mysql/data/test/' ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/not_existing/usr/local/mysql/data/test/' ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.01 sec) -------------- show create table t2 -------------- *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000) DATA DIRECTORY = '/usr/local/mysql/data/test/x0' ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/usr/local/mysql/data/test/x1' ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) -------------- alter table t2 engine=archive -------------- ERROR 1005 (HY000) at line 30: Can't create table 'test.#sql-4a4c_17' (errno: 1) -------------- alter table t1 engine=myisam -------------- ERROR 1 (HY000) at line 31: Can't create/write to file '/not_existing/usr/local/mysql/data/test/#sql-4a4c_17#P#p0.MYD' (Errcode: 2) How to repeat: select version(); create schema if not exists test; use test; drop table if exists t1, t2, t3; create table t1 (id int not null primary key) engine = innodb partition by range (id) ( partition p0 values less than (1000) data directory = '/not_existing/usr/local/mysql/data/test/', partition p1 values less than (2000) data directory = '/not_existing/usr/local/mysql/data/test/', partition p2 values less than (MAXVALUE) ) ; create table t2 (id int not null primary key) engine = innodb partition by range (id) ( partition p0 values less than (1000) data directory = '/usr/local/mysql/data/test/x0', partition p1 values less than (2000) data directory = '/usr/local/mysql/data/test/x1', partition p2 values less than (MAXVALUE) ) ; show tables; show create table t1\G show create table t2\G alter table t2 engine=archive; alter table t1 engine=myisam; show create table t1\G show create table t2\G Suggested fix: Storage engines that don't use the 'DATA|INDEX DIRECTORY' option should not accept it when the table is created. Check for DATA|INDEX DIRECTORY names should be more robust than just looking for the first character.