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

[25 Jan 2006 12:04] Matthias Leich
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
[25 Jan 2006 12:05] Matthias Leich
test script

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

[25 Jan 2006 12:06] Matthias Leich
my test protocol

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

[8 Feb 2006 20: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/2337
[13 Feb 2006 18:15] 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/2534
[16 Feb 2006 0:42] Jim Winstead
Fixed in 5.1.7.
[16 Feb 2006 14:17] 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 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.