MySQL Bugs: #37182: Dumped Archive table with AUTO_INCRMENET PRIMARY KEY won't restore from dump

Bug #37182	Dumped Archive table with AUTO_INCRMENET PRIMARY KEY won't restore from dump
Submitted:	4 Jun 2008 5:50	Modified:	4 Jun 2008 21:26
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S3 (Non-critical)
Version:	5.1.24, 5.1.30	OS:	Any (RH Linux 64, WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	archive engine, mysqldump, primary key

Description:
Dumping (with mysqldump) an archive table with a primary key, hen trying to reload from dump (e.g. fro replication) results with 
ERROR 1022 (23000) ...: Can;t write; duplicate key in table 'XXX'

I have an ARCHIVE table, with an auto-increment primary key column.
When dumping the table (only as of 5.1.24), the table is dumped along with the "AUTO_INCREMENT=NNN" table option.
But then, when I try to read the table from dump, even on a newly created schema, not lines are inserted to the table, because the primary key values in the dumped table are LOWER than the auto_increment specified in the dump.

How to repeat:
# Create this table and add values:

USE test;
CREATE TABLE `ar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE;

INSERT INTO ar VALUES(NULL);
INSERT INTO ar VALUES(NULL);
INSERT INTO ar VALUES(NULL);
INSERT INTO ar VALUES(NULL);
INSERT INTO ar VALUES(NULL);

# Verify that all is well:
mysql> SELECT * FROM ar;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

# Dump the table:
mysqldump -u root -p test ar > /tmp/ar.txt

# view the dump:
...
DROP TABLE IF EXISTS `ar`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
...
# Note the AUTO_INCREMENT=5 table option

#Try to import the dump:
mysql -u root -p test < /tmp/ar.txt
Enter password: ****
ERROR 1022 (23000) at line 37: Can't write; duplicate key in table 'ar'

Suggested fix:
Until bug is resolved:
Manually edit dump file, set AUTO_INCREMENT=0 instead of whatever number is in there. 
Now loading the table succeeds.

Thank you for the report.

Verified as described.

a neater testcase, showing that inserting lower values in auto_increment field is not possible:

drop table if exists `t1`;
create table `t1` (`a` int primary key auto_increment)engine=archive;
insert into `t1`(`a`) values (2);
insert into `t1`(`a`) values (1);

That is really annoying and deserves higher severity as it only allows to load data into archive tables with index column when the data is ordered by this column. Otherwise the load fails.

My specific problem: I have several big archive tables (85 GB in total) and have upgraded from 5.0 to 5.1 because I want to have an index column in them. Now I can't simply ALTER TABLE because it fails, nor can I dump the data and load it again. As workaroud I can "INSERT INTO new_archive_table SELECT * FROM old_archive_table ORDER BY id" but it will take hours bacause of "ORDER BY".

I am teaching a MySQL Performance Tuning class in Santa Clara this week, and we ran into this bug, as a group, in class.  Now 10 more customers are alarmed by this gotcha.  I agree that the severity should be higher!  Use the index to check for duplicate key issues, like everyone else does--don't get too "clever" and presume a duplicate key just because the autoincrement is higher than the value on insert and ARCHIVE doesn't support DELETE.

The problem here is that Archive does not support indexes.

But to allow auto_increment primary key, Archive ensures that only higher values are inserted, which is effectively enforcing the primary key (although not storing it as a key/index and not really looking for duplicate keys).

The solution could be to change the AUTO_INCREMENT to allow it to only be a non unique KEY. That way it would allow lower values.

Another solution for this could be to change the dump program to first just create the table without auto_increment, and then alter the table to the correct AUTO_INCREMENT value (but that would need a fix in mysql_alter_table too, to avoid full table copy and only update the table metadata.)

See also Bug#37871 (ALTER TABLE ENGINE=ARCHIVE fails on tables with auto_increment field).

I concur that the severity of this bug should be increased as it effectively means that, in most use cases, primary key indices cannot be used in the archive engine.

It should either be fixed or the feature dropped entirely.
I know that it is only archive storage so shouldn't be used for querying too often but still...

+1 for increasing severity. This makes the archive engine much less useful for special case archival situations, i.e. when a table is usually another engine and then gets converted to archive.

Bug #65450 was marked as duplicate of this one.

+1 for elevating. 

Not being able to convert tables with AUTO_INCREMENT is a significant problem. What's the point of this engine if you can't do that? 

I think it's pretty clever to use the last value of the autoincrement that's stored in the metadata to implement auto_increment in this engine type (without having any indexes or real keys) but this is an important use case that is clearly not functioning as you would expect it to by the spec. 

I think Mattias Jonsson figured it out back in 2009: when you do a table conversion, relax the autoincrement restriction.

This is likely a duplicate of #37871 AND #65450.

it's still happening in 2013..

needs to be fixed.

still happening in 2015

Confirmed (2015.06), please increase priority.

Confirmed (2017.06), on latests MySQL 5.1, 5.5, 5.6 and 5.7.
Please increase priority.

Still effects MySQL 5.6.22 (and presumably 5.7, though I haven't tested).

I personally consider it a major flaw if its ever possible to have a valid database state that cannot be dumped and then restored.  This means any backups will fail to be restored.

Affects MySQL 5.7 exports as well.
This bug is really annoying.

Any news about a fix?