For MyISAM this is bug #19107 + bug #14354. For InnoDB DATA DIRECTORY and
INDEX DIRECTORY isn't supported since there exist no such feature in InnoDB.
Rgrds Mikael
Tore Krudtaa Wrote:
-------------------------------------------------------
> -- 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.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com