Bug #62568 Partition options not supported by storage engine are silently ignored
Submitted: 28 Sep 2011 21:54 Modified: 29 Sep 2011 3:19
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.12, 5.5.17 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D4 (Minor)

[28 Sep 2011 21:54] Hartmut Holzgraefe
Description:
E.g. InnoDB does not support DATA_DIR/INDEX_DIR, but CREATE TABLE does not raise any warnings about this unsupported combination.

How to repeat:
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `adate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY LIST (YEAR(adate))
(PARTITION p1999 VALUES IN (1995,1999,2003) DATA DIRECTORY = '/tmp' INDEX DIRECTORY = '/tmp' ENGINE = InnoDB,
 PARTITION p2000 VALUES IN (1996,2000,2004) DATA DIRECTORY = '/tmp' INDEX DIRECTORY = '/tmp',
 PARTITION p2001 VALUES IN (1997,2001,2005) DATA DIRECTORY = '/tmp' INDEX DIRECTORY = '/tmp',
 PARTITION p2002 VALUES IN (1998,2002,2006) DATA DIRECTORY = '/tmp' INDEX DIRECTORY = '/tmp');

SHOW WARNINGS;

Suggested fix:
Throw warnings when unsupported features/feature combinations are used
[29 Sep 2011 3:19] Valeriy Kravchuk
Thank you for the problem report. Verified with 5.5.17 on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.31 sec)

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `adate` date DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> PARTITION BY LIST (YEAR(adate))
    -> (PARTITION p1999 VALUES IN (1995,1999,2003) DATA DIRECTORY = '/tmp' INDEX DIRECTORY =
    -> '/tmp' ENGINE = InnoDB,
    ->  PARTITION p2000 VALUES IN (1996,2000,2004) DATA DIRECTORY = '/tmp' INDEX DIRECTORY =
    -> '/tmp',
    ->  PARTITION p2001 VALUES IN (1997,2001,2005) DATA DIRECTORY = '/tmp' INDEX DIRECTORY =
    -> '/tmp',
    ->  PARTITION p2002 VALUES IN (1998,2002,2006) DATA DIRECTORY = '/tmp' INDEX DIRECTORY =
    -> '/tmp');
Query OK, 0 rows affected (0.44 sec)

mysql> 
mysql> show warnings\G
Empty set (0.02 sec)

Surely nothing is created in /tmp :)