The ARCHIVE Storage Engine – does it do what you expect?

Sometimes there is a need for keeping large amounts of old, rarely used data without investing too much on expensive storage. Very often such data doesn’t need to be updated anymore, or the intent is to leave it untouched. I sometimes wonder what I should really suggest to our Support customers.

For this purpose, the archive storage engine, added in MySQL 4.1.3, seems perfect as it provides excellent compression and the only DML statement it does allow is INSERT. However, does it really work as you would expect?

First of all, it has some serious limitations. Apart from lack of support for DELETE, REPLACE and UPDATE statements (which may be acceptable for some needs), another one is that it does not allow you to have indexes, although you can have an auto_increment column being either a unique or non-unique index. So usually straightforward converting your tables to archive engine will not be possible. See the list of features for reference.

But unfortunately, it does not always work as the manual says, within it’s described limitations. See the following very simple examples.

Problem I

Does the archive storage engine really ensure uniqueness for a primary or unique key?

That is really bad – a column being a primary key effectively allows duplicates! And another case exposing the same problem:

So even a simple optimize table command does break it completely. After we realize that such operation made our data bad, we won’t be able to easily go back to different engine without sacrificing uniqueness first:

There were already bug reports related to auto_increment feature being broken, but I have filed a new, more specific bug report about this problem.

Problem II

Are we always able to alter a table to use the archive storage engine, even if it is theoretically using supported table definition? Auto increment column issue again…

We have the same c table using archive. We can change it’s engine to something different:

But in some cases, we can’t set it back to archive!

There is an old bug report about that.

Problem III

And yet another weirdness around auto_increment values. It seems normal that databases allow us to insert explicit values into auto_increment columns, even lower then last inserted maximum, and all other engines – MyISAM, Memory and InnoDB do that:

But it’s not the case for Archive engine:

This undocumented behavior was reported here.


The archive storage engine provides a very good compression and is available in all MySQL variants out of the box. However it does have serious limitations as well as works unreliable and not as expected in some cases.

Related articles worth mentioning here:

In my