MySQL Bugs: #16775: DROP TABLE of subpartitioned table can fail if default engine used

Bug #16775	DROP TABLE of subpartitioned table can fail if default engine used
Submitted:	25 Jan 2006 12:04	Modified:	16 Feb 2006 14:17
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1	OS:
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
There will be strange effects if somebody creates a table with
- subpartitions getting storage engine <A> assigned
- partitions getting no storage engine assigned
- storage engine <A> for subpartitions differs from default
   storage engine <B> for the session.
Example:
SET SESSION storage_engine='MEMORY';
CREATE TABLE  t1 ( f_int1 int(11) default NULL )  ENGINE = MEMORY 
DEFAULT CHARSET=latin1
PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1)
(PARTITION part1 VALUES LESS THAN (1000)  ENGINE = MEMORY 
                  (SUBPARTITION subpart11  ENGINE = MEMORY ));
CREATE TABLE  t2 ( f_int1 int(11) default NULL )  ENGINE = MEMORY
DEFAULT CHARSET=latin1
PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1)
(PARTITION part1 VALUES LESS THAN (1000)
                  (SUBPARTITION subpart11  ENGINE = MEMORY ));
CREATE TABLE  t3 ( f_int1 int(11) default NULL )  ENGINE = MEMORY
DEFAULT CHARSET=latin1
PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1)
(PARTITION part1 VALUES LESS THAN (1000)
                  (SUBPARTITION subpart11  ENGINE = MEMORY ));
# session default storage engine = MEMORY
# partition has no storage engine assigned,
# subpartition storage engine = MEMORY assigned,
var/master-data/test/t2.frm
var/master-data/test/t2.par
DROP TABLE t2;
        <---- no problem
SET SESSION storage_engine='MyISAM';
# session default storage engine = MyISAM
# partition has no storage engine assigned,
# subpartition storage engine = MEMORY assigned
var/master-data/test/t3.frm
var/master-data/test/t3.par
DROP TABLE t3;
ERROR HY000: Error on delete of './test/t3#P#part1#SP#subpart11.MYI' (Errcode: 2)
         <---- So it looks like the DROP "thinks" the table must
                  have some properties like tables with partitions
                  having ENGINE = <current session default engine>
                  assigned.
                  But the table is different, which causes that the DROP fails.
# session default storage engine = MyISAM
# partition storage engine = MEMORY assigned
# subpartition storage engine = MEMORY assigned
var/master-data/test/t1.frm
var/master-data/test/t1.par
DROP TABLE t1;
          <---- So it looks like there is no influence of the current session default engine
                  and the DROP is successful.
                  I assume this comes from the fact, that a storage engine for the
                  partition was assigned within the CREATE TABLE statement.

I believe that the failing DROP TABLE t2 is only one example of the most
probably dramatic failures which will appear, when somebody creates a
subpartitioned table without storage engine for partitions assigned.
I did not check commands which alter the partitioning scheme, add/drop/alter
PRIMARY KEYs or UNIQUE INDEXes, but I fear there will be heavy problems.
That's the reason why I set Priority to 1 and Showstopper.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         Version 5.1 last ChangeSet@1.2076, 2006-01-23

How to repeat:
Please execute the statements above or use my attached 
testscript ml012.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml012.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml012

test script

Attachment: ml012.test (application/test, text), 1.56 KiB.

my test protocol

Attachment: ml012.reject (application/octet-stream, text), 1.52 KiB.

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/2337

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/2534

Fixed in 5.1.7.

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.1.7 changelog; closed.