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 Email Updates:
Status: Not a Bug Impact on me:
None 
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.