Bug #24633 SQL MODE "NO_DIR_IN_CREATE" does not work with partitioned tables
Submitted: 27 Nov 2006 21:54 Modified: 11 Apr 2007 3:21
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.14 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: partitions, server, SQL_MODE

[27 Nov 2006 21:54] Giuseppe Maxia 
Description:
The NO_DIR_IN_CREATE directive should skip the INDEX DIRECTORY and DATA DIRECTORY clauses in CREATE TABLE statements.
It works fine for normal tables, but it fails on partitioned tables.

For example:

create table t1 (i int )
index directory='/not/existing'
data directory='/not/existing'
Query OK, 0 rows affected

create table t2 (i int )
partition by range (i)
(
    partition p01 values less than (1000)
    data directory='/not/existing'
    index directory='/not/existing'
)
ERROR 1 (HY000) at line 10: Can't create/write to file '/not/existing/t2#P#p01.MYI' (Errcode: 2)

How to repeat:
--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (i int )
index directory='/not/existing'
data directory='/not/existing';

create table t2 (i int )
partition by range (i)
(
    partition p01 values less than (1000)
    data directory='/not/exiting'
    index directory='/not/exiting'
) ;

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

Suggested fix:
In replication, there is no remedy other than making sure to have the same paths on master and slaves.

With SQL dumps, the offending clauses can be filtered off with a Perl one-liner

perl -pe '$c=chr(39);s/(?:DATA|INDEX)\s+DIRECTORY\s*=\s*([$c"])[^\1]+\1//i' < dumpfile | mysql
[10 Mar 2007 21:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21681

ChangeSet@1.2477, 2007-03-11 01:17:41+04:00, holyfoot@mysql.com +3 -0
  Bug #24633 SQL MODE "NO_DIR_IN_CREATE" does not work with
  partitioned tables"
  We have to ignore 'data directory' and 'index directory' parameters
  if NO_DIR_IN_CREATE set.
[29 Mar 2007 14:42] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23312

ChangeSet@1.2478, 2007-03-29 18:42:27+05:00, holyfoot@mysql.com +3 -0
  bug #24633 (sql_mode NO_DIR_IN_CREATE doesn't work with partitioned tables)
  
  additional patch to fix SHOW CREATE behaviour
[9 Apr 2007 12:42] Bugs System 
Pushed into 5.1.18-beta
[11 Apr 2007 3:21] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.18 changelog.

Also noted in Partitioning chapter and CREATE TABLE sections of 5.1 Manual.