Bug #19786 CREATE TABLE and ALTER TABLE fails on archive table if index

• Submitted: 12 May 2006 19:58
• Modified: 23 Nov 2007 9:29
• Reporter: Guilhem Bichot
• Status: Not a Bug
• Category: MySQL Server: Archive storage engine
• Severity: S3 (Non-critical)
• Version: 5.1-bk
• OS: Linux (linux)
• Assigned to: Sergey Vojtovich
• CPU Architecture: Any

[12 May 2006 19:58] Guilhem Bichot

Description:
observed on two machines.
drop table if exists t1;
create table t1 (a int not null) engine=archive;
show create table t1;
alter table t1 add unique(a);
the alter table fails like this:
mysqltest: At line 4: query 'alter table t1 add unique(a)' failed: 1005: Can't create table 'test.#sql-58c7_1' (errno: -1)

How to repeat:
drop table if exists t1;
create table t1 (a int not null) engine=archive;
show create table t1;
alter table t1 add unique(a);

[12 May 2006 19:58] Guilhem Bichot

didn't test 5.0, only 5.1

[13 May 2006 5:03] Peter Laursen

5.0.21:
drop table if exists t1;
create table t1 (a int not null) engine=archive;
show create table t1;
alter table t1 add unique(a);
/*Error Code : 1069
Too many keys specified; max 0 keys allowed
(0 ms taken)*/
alter table `test`.`t1` add column `t` varchar (20)   NULL  after `a`
/*succeeds*/

5.1.9:
drop table if exists t1;
create table t1 (a int not null) engine=archive;
show create table t1;
alter table t1 add unique(a);
/*Error Code : 1005
Can't create table 'test.#sql-d2c_2' (errno: -1)
(60 ms taken)*/
alter table `test`.`t1` add column `t` varchar (20)   NULL  after `a`
/*succeeds*/

[13 May 2006 7:37] Guilhem Bichot

additionally, create table arc(a int not null, unique(a)) engine=archive
prints the same error message as the one I have for ALTER.

[18 Sep 2006 0:51] Brian Aker

This should work in 5.1... something in ::create() must not be able to tell that the index is ok.

[18 Sep 2006 8:41] Guilhem Bichot

Reverified with a 5.1-bk of end of last week.

[16 Nov 2007 6:27] terry tao

the archive engine only support key with auto_increment?
int ha_archive::create(const char *name, TABLE *table_arg,
                       HA_CREATE_INFO *create_info)
{
...
    for (; key_part != key_part_end; key_part++)
    {
      Field *field= key_part->field;

      if (!(field->flags & AUTO_INCREMENT_FLAG))
      {
        error= -1;
        DBUG_PRINT("ha_archive", ("Index error in creating archive table"));
        goto error;

result:
mysql> show variables like 'version';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| version       | 5.1.23-beta-debug-log |
+---------------+-----------------------+
1 row in set (0.02 sec)

mysql> create table t1 (a int not null ,key (a)) engine=archive;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: -1)
mysql> create table t1 (a int not null auto_increment,key (a)) engine=archive;
Query OK, 0 rows affected (0.14 sec)

mysql>

[23 Nov 2007 9:29] Sergey Vojtovich

This is expected behavior. An excerpt from the manual:
As of MySQL 5.1.6, the ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or non-unique index. Attempting to create an index on any other column results in an error.