4

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.

Maximi
  • 143
  • 1
  • 3

2 Answers2

3

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;
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

The ARCHIVE engine can have an auto-increment column and for this it requires an index.

As per the docs (5.5, 5.6, 5.7):

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. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

ALTER TABLE my_table AUTO_INCREMENT=0, ENGINE=ARCHIVE;

It will fail with ERROR 1022 (23000): Can't write; duplicate key in table if you do not reset the AUTO_INCREMENT

If you try to do this when your PRIMARY KEY is not auto-increment then you will see:

mysql [localhost] {root} (test) > create table foo (id int primary key);
Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {root} (test) > alter table foo engine=archive;
ERROR 1030 (HY000): Got error -1 from storage engine
cEz
  • 176
  • 9