mysql 子分区指定存储路径的问题

Question

SQL语句
mysql> CREATE TABLE ts (id INT, purchased DATE) engine=myisam
    ->     PARTITION BY RANGE(YEAR(purchased))
    ->     SUBPARTITION BY HASH(TO_DAYS(purchased))
    ->     (
    ->         PARTITION p0 VALUES LESS THAN (1990)
    ->         (
    ->             SUBPARTITION s0a 
    ->                 DATA DIRECTORY = '/home/bzuo/d1' 
    ->                 INDEX DIRECTORY = '/home/bzuo/d1',
    ->             SUBPARTITION s0b 
    ->                 DATA DIRECTORY = '/home/bzuo/d2' 
    ->                 INDEX DIRECTORY = '/home/bzuo/d2'
    ->         )
    ->     ) 
    ->     ;
 
ERROR 1 (HY000): Can't create/write to file '/home/bzuo/d1/ts#P#p0#SP#s0a.MYI' (Errcode: 13)目录权限
drwxrwxrwx 2 mysql mysql       4096 Mar  4 23:33 d1
drwxrwxrwx 2 mysql mysql       4096 Mar  4 23:21 d2报错
Can't create/write to file '/home/bzuo/d1/ts#P#p0#SP#s0a.MYI' BUG URL:
http://bugs.mysql.com/bug.php?id=29322手册上说可以，官网上也说可以，但是测试不行。
有谁做成功过，出来指点一下。

Answer 1

[****]$ lsb_release -a
LSB Version:    :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description:    CentOS release 5.7 (Final)
Release:        5.7
Codename:       Final

Answer 2

借贵宝地，再问个问题mysql> explain select count(1) as TextMessageSent from SMSMT force index(idx_asset),Business where Business.ID=SMSMT.BusinessId and Protocol = "SMS" and ActualDeliveryTime >=date_format(Date_sub(NOW(),interval 7 day),'%Y-%m-%d 00:00:00') and Business.nodegroup =(select value from NodeGroup where Name = "NodeGroup");
+----+-------------+-----------+--------+---------------+------------+---------+----------------------+----------+-------------+
| id | select_type | table     | type   | possible_keys | key        | key_len | ref                  | rows     | Extra       |
+----+-------------+-----------+--------+---------------+------------+---------+----------------------+----------+-------------+
|  1 | PRIMARY     | SMSMT     | ALL    | idx_asset     | NULL       | NULL    | NULL                 | 26171428 | Using where | 
|  1 | PRIMARY     | Business  | eq_ref | PRIMARY       | PRIMARY    | 4       | ng1.SMSMT.BusinessId |        1 | Using where | 
|  2 | SUBQUERY    | NodeGroup | ref    | BusinessID    | BusinessID | 257     | const                |        2 | Using where | 
+----+-------------+-----------+--------+---------------+------------+---------+----------------------+----------+-------------+
3 rows in set (0.01 sec)
在查询里面，force index(idx_asset),强制指定了走索引idx_asset,但是实际上却还是全表扫描，这是为何？

Answer 3

估计是文件权限的问题 where Business.ID=SMSMT.BusinessId and Protocol = "SMS" 
索引如何建立的，只要上述2个条件是否用到索引

Answer 4

分区指定文件位置的问题找到了。[root@localhost ~]# lsb_release -aLSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarchDistributor ID: CentOSDescription: CentOS release 5.7 (Final)Release: 5.7Codename: Final可以
[****]$ lsb_release -aLSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarchDistributor ID: CentOSDescription: CentOS release 5.7 (Final)Release: 5.7Codename: Final 不行