Problem when defining DATA DIRECTORY

[Tore Krudtaa]

-- Using MySql 5.1.9 on Win XP:

-- Want to put each partition on it's own disk, but for now just testing
-- with putting each partition in it's own directory.

-- ********************************
-- first trying with engine=innodb
-- ********************************

CREATE TABLE part_tab
( c1 int default NULL,
c2 char (30) default NULL,
c3 date default NULL
) engine=innodb
PARTITION BY RANGE (year(c3)) (
PARTITION p0 VALUES LESS THAN (1995) DATA DIRECTORY 'c:/mysql-partition/p0',
PARTITION p1 VALUES LESS THAN (1996) DATA DIRECTORY 'c:/mysql-partition/p1',
PARTITION p2 VALUES LESS THAN MAXVALUE DATA DIRECTORY 'c:/mysql-partition/p2');

-- ---------Then I insert some test data ---------

insert into part_tab
values (1,'testing partitions','1995-01-01');
insert into part_tab
values (1,'testing partitions','1996-01-01');
insert into part_tab
values (1,'testing partitions','2000-01-01');

-- then try an explain to see what happens

EXPLAIN partitions SELECT c3
FROM part_tab
WHERE c3 < '1996-01-01'

-- which returns this:

id=1
select_type=SIMPLE
table=part_tab
partitions=p0,p1,p2

-- *****************************************
-- THEN I TRY WITH engine=myisam like this:
-- *****************************************

CREATE TABLE part_tab
( c1 int default NULL,
c2 char (30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3))
(
PARTITION p0 VALUES LESS THAN (1995)
DATA DIRECTORY = 'c:/mysql-partition/p0'
INDEX DIRECTORY = 'c:/mysql-partition/p0',
PARTITION p1 VALUES LESS THAN (1996)
DATA DIRECTORY = 'c:/mysql-partition/p1'
INDEX DIRECTORY = 'c:/mysql-partition/p1',
PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = 'c:/mysql-partition/p2'
INDEX DIRECTORY = 'c:/mysql-partition/p2'
);

-- at first the above code seemed to work,
-- I can see the table listed within the database
-- I can see the *.MYD and *.MYI files in the data directories specified
-- but if I (in phpmyadmin) click on the table then I get this returned back;

SQL-query:
SHOW KEYS FROM `part_tab` ;

MySQL said:
#1017 - Can't find file: 'part_tab' (errno: 2)

--And when I try this on the myisam table:
insert into part_tab
values (1,'testing partitions','1995-01-01');

-- I get this result:
MySQL said:
#1017 - Can't find file: 'part_tab' (errno: 2)


**************************
Questions regarding innodb:
**************************

Why is nothing stored in each directories defined by DATA DIRECTORY when using innodb?

Is this feature not supposed to work yet for innodb, or is my syntax wrong?
If this feature is to be supported in the future for innodb, when will it
be supported?

Get no error when defining the table as innodb.
The directories where created manually before I create the table with the above
statement.

**************************
Questions regarding myisam:
**************************

What did I do wrong since mysql is unable to find the file(s) that it
did create in the create table statement ?
Do I have to specify something extra in my.ini to make this work, or something else?


Also... would like to be directed to an article that demonstrates the use
of DATA DIRECTORY toghether with partitioning with working examples.



Edited 1 time(s). Last edit at 05/21/2006 05:28AM by Tore Krudtaa.