Change table from InnoDB to ARCHIVE in MySQL

I try change my table from InnoDB to ARCHIVE with SQL command:

ALTER TABLE data_obook ENGINE = ARCHIVE;

But it always show me only error:

#1005 - Can't create table 'coincity.#sql-1350_8a' (errno: -1)

My structure:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `data_obook` (
  `market_id` int(6) NOT NULL,
  `curr_id` int(4) NOT NULL,
  `book_id` bigint(20) NOT NULL,
  `type` varchar(5) NOT NULL,
  `price` float NOT NULL,
  `amount` double NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`market_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Can somebody help me with solution? Thank you.

Since the ARCHIVE Storage Engine does not support indexes of any kind, your main problem is the presence of the PRIMARY KEY. Simply drop the PRIMARY KEY. Then, convert the Storage Engine.

There are two approaches to this

APPROACH #1

ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;

APPROACH #2

CREATE TABLE date_obook_new LIKE date_obook;
ALTER TABLE date_obook_new DROP PRIMARY KEY;
ALTER TABLE date_obook_new ENGINE=ARCHIVE;
INSERT INTO date_obook_new SELECT * FROM date_obook;
ALTER TABLE date_obook RENAME date_obook_old;
ALTER TABLE date_obook_new RENAME date_obook;
DROP TABLE date_obook_old;

GIVE IT A TRY !!!

SPECIAL NOTE

If anyone has a PRIMARY KEY with the AUTO_INCREMENT attribute, you must remove the AUTO_INCREMENT attribute first. Then, remove the PRIMARY KEY, Then, convert the Storage Engine. For example, if market_id was defined as int(6) NOT NULL AUTO_INCREMENT, do the following:

ALTER TABLE date_obook MODIFY market_id INT NOT NULL;
ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;

StackExchange Database Administrators Q#72997, answer score: 3