Bug #43030 Archive engine fails to create a table with partitions
Submitted: 19 Feb 2009 18:40 Modified: 12 Mar 2009 19:09
Reporter: Chuck Bell Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:6.0.10, 5.1, 6.0 bzr OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[19 Feb 2009 18:40] Chuck Bell 
Description:
The archive engine throws an error when attempting to create a table with partitions.

Note: This sequence is in the backup test in suite/backup_engines/backup_partitioning.test. This test has been disabled for the archive engine. When this bug is fixed that test needs to be enabled. See the test for details.

How to repeat:
Attempt these statements:

CREATE DATABASE ZETA;

CREATE TABLE ZETA.BETA (ID INT NOT NULL,
                        NAME CHAR(20),
                        AGE TINYINT,
                        DOB DATE,
                        SAL FLOAT,
                        PRIMARY KEY(DOB)) ENGINE=ARCHIVE 
PARTITION BY RANGE(Year(DOB))(PARTITION P0 values less than (1970),
                              PARTITION P1 values less than (1980),
                              PARTITION P2 values less than (1990),
                              PARTITION P3 values less than (2000),
                              PARTITION P4 values less than maxvalue);

DROP DATABASE ZETA;

The results (from the mysql-6.0 tree) are:

mysql> CREATE DATABASE ZETA;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE ZETA.BETA (ID INT NOT NULL,
    ->                         NAME CHAR(20),
    ->                         AGE TINYINT,
    ->                         DOB DATE,
    ->                         SAL FLOAT,
    ->                         PRIMARY KEY(DOB)) ENGINE=ARCHIVE
    -> PARTITION BY RANGE(Year(DOB))(PARTITION P0 values less than (1970),
    ->                               PARTITION P1 values less than (1980),
    ->                               PARTITION P2 values less than (1990),
    ->                               PARTITION P3 values less than (2000),
    ->                               PARTITION P4 values less than maxvalue);
ERROR 1005 (HY000): Can't create table 'zeta.beta' (errno: -1)
mysql>
mysql> DROP DATABASE ZETA;
Query OK, 0 rows affected (0.00 sec)

But if one substitutes MYISAM for ARCHIVE, we get this:

mysql> CREATE DATABASE ZETA;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE ZETA.BETA (ID INT NOT NULL,
    ->                         NAME CHAR(20),
    ->                         AGE TINYINT,
    ->                         DOB DATE,
    ->                         SAL FLOAT,
    ->                         PRIMARY KEY(DOB)) ENGINE=MYISAM
    -> PARTITION BY RANGE(Year(DOB))(PARTITION P0 values less than (1970),
    ->                               PARTITION P1 values less than (1980),
    ->                               PARTITION P2 values less than (1990),
    ->                               PARTITION P3 values less than (2000),
    ->                               PARTITION P4 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DROP DATABASE ZETA;
Query OK, 1 row affected (0.00 sec)

Suggested fix:
Unknown
[20 Feb 2009 7:25] Sveta Smirnova 
Thank you for the report.

Verified as described.
[12 Mar 2009 19:09] Mattias Jonsson 
Not a bug, Archive does not support keys.

The same error without partitioning:
mysqltest: At line 3: query 'CREATE TABLE ZETA.BETA (ID INT NOT NULL,
NAME CHAR(20),
AGE TINYINT,
DOB DATE,
SAL FLOAT,
PRIMARY KEY(DOB)) ENGINE=ARCHIVE 
' failed: 1005: Can't create table 'zeta.beta' (errno: -1)
[12 Mar 2009 19:36] 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/69080

2800 Chuck Bell	2009-03-12
      BUG#43030 : Archive engine fails to create a table for partitioning
      
      This is not a bug. Since the archive engine does not support 
      indexes, it cannot be used for partitioning.
      
      Tests annotated accordingly. No result file changes.
      modified:
        mysql-test/suite/backup_engines/t/backup_partition.test
        mysql-test/suite/backup_engines/t/backup_partitioning.test
[12 Mar 2009 19:51] 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/69081

2800 Chuck Bell	2009-03-12
      BUG#43030 : Archive engine fails to create a table with partitions
      
      This is not a bug. Since the archive engine does not support 
      indexes, it cannot be used for partitioning using indexes.
      modified:
        mysql-test/suite/backup_engines/t/backup_partition.test
        mysql-test/suite/backup_engines/t/backup_partitioning.test
[23 Apr 2009 7:19] Bugs System 
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (version source revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (merge vers: 6.0.11-alpha) (pib:6)
[2 May 2009 22:26] MySQL Verification Team 
Bug: http://bugs.mysql.com/bug.php?id=44622 marked as duplicate of this one.