Bug #42954 SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
Submitted: 18 Feb 2009 3:53 Modified: 5 Jul 2010 15:35
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.22, 5.1.32 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[18 Feb 2009 3:53] Elena Stepanova
Description:
SQL_MODE NO_DIR_IN_CREATE does not work for tables with subpartitions (although it works for normal tables and partitions). 

It causes replication failures, e.g. if the slave runs on the same box as the master -- slave SQL thread aborts with Can't create/write to file ... (Errcode: 17).

The manual specifically says: 'Beginning with MySQL 5.1.18, the DATA DIRECTORY and INDEX DIRECTORY  options are disallowed when the NO_DIR_IN_CREATE server SQL mode is in effect. This is true for partitions and subpartitions.'

How to repeat:
set session sql_mode='NO_DIR_IN_CREATE';
use test;
drop table if exists tssubpart;

CREATE TABLE tssubpart (id INT, purchased DATE) engine=MyISAM
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN MAXVALUE 
                DATA DIRECTORY = '/tmp/not-existing' 
                INDEX DIRECTORY = '/tmp/not-existing'
    );

# ERROR 1 (HY000): Can't create/write to file '/tmp/not-existing/tssubpart#P#p0#SP#p0sp0.MYI' (Errcode: 2)

Suggested fix:
A similar problem was reported and solved for partitions long time ago (bug#24633).
[3 Mar 2010 14:37] Mattias Jonsson
Need to copy the check from the partition level in partition_info.cc:1170 to 1205.
[4 Mar 2010 13:46] 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/102318

3369 Mattias Jonsson	2010-03-04
      Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
      
      There was no check for DATA/INDEX DIRECTORY for subpartitions
      
      Added the same check as for partitions.
     @ mysql-test/r/partition_error.result
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        Updated results
     @ mysql-test/t/partition_error.test
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        Added tests
     @ sql/partition_info.cc
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        Copied the check for DATA/INDEX DIRECTORY from partitions to
        subpartitions.
[11 Mar 2010 13:01] 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/103000

3369 Mattias Jonsson	2010-03-11
      Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
      
      There was no check for DATA/INDEX DIRECTORY for subpartitions
      
      Added the same check as for partitions.
     @ mysql-test/r/partition_error.result
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        Updated results
     @ mysql-test/t/partition_error.test
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        Added tests
     @ sql/partition_info.cc
        Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with subpartitions
        
        moved the check for DATA/INDEX DIRECTORY into a function
        and used it for both partitions as well as subpartitions.
        (Was not checked at all for subpartitions before)
[26 May 2010 8:30] Mattias Jonsson
pushed to mysql-trunk-bugfixing and mysql-next-mr-bugfixing
[15 Jun 2010 8:22] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:40] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[5 Jul 2010 15:35] Jon Stephens
Documented in the 5.5.5 and 6.0.14 changelogs as follows:

        The NO_DIR_IN_CREATE SQL mode was not enforced when defining
        subpartitions.

Also updated note about this issue in 5.1/5.5/6.0 versions of Partitioning chapter.

Closed.