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: | |
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
[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.