MySQL Bugs: #75703: Cannot add a primary key to an ARCHIVE table

Bug #75703	Cannot add a primary key to an ARCHIVE table
Submitted:	30 Jan 2015 15:35	Modified:	1 Feb 2015 6:28
Reporter:	Chris Worfolk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S3 (Non-critical)
Version:	5.5.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	archive

Description:
If you create a table with the ARCHIVE engine, and then try and add a primary key to it, you get an error similar to:

#1005 - Can't create table 'dbname.#sql-446_13a' (errno: -1)

In the how to repeat example I've provided, the first table using InnoDB will work fine, but the second table, using the ARCHIVE engine, will fail on adding the primary key.

How to repeat:
CREATE TABLE IF NOT EXISTS `test1` (
`id` bigint(20) NOT NULL,
  `type` varchar(50) NOT NULL,
  `timestamp` decimal(16,6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `test1` ADD PRIMARY KEY (`id`);

CREATE TABLE IF NOT EXISTS `test2` (
`id` bigint(20) NOT NULL,
  `type` varchar(50) NOT NULL,
  `timestamp` decimal(16,6) NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8;

ALTER TABLE `test2` ADD PRIMARY KEY (`id`);

http://dev.mysql.com/doc/refman/5.5/en/archive-storage-engine.html

"The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error."

. so this ALTER statement will work (verified!)

ALTER TABLE `test`.`test2`   
  CHANGE `id` `id` BIGINT(20) NOT NULL AUTO_INCREMENT, 
  ADD PRIMARY KEY (`id`);

(other syntax variants may be possible) 

-- Peter
-- not a MySQL/Oracle person

Hello Chris Worfolk,

Thank you for the report.
Imho this is not a bug, quoting from manual - "The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error" - http://dev.mysql.com/doc/refman/5.6/en/archive-storage-engine.html

This behavior is consistent across 5.1/5.5/5.6 and 5.7 builds.

FR Bug #74910 has been reported for the proper error message.

Thanks,
Umesh

Related - Bug #74910