Bug #37871 ALTER TABLE ENGINE=ARCHIVE fails on tables with auto_increment field
Submitted: 4 Jul 2008 13:42 Modified: 4 Mar 2016 9:34
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6, 5.7 OS:Linux
Assigned to:
Triage: Triaged: D3 (Medium)

[4 Jul 2008 13:42] Hartmut Holzgraefe
Description:
ALTER TABLE ENGINE=ARCHIVE fails on tables with auto_increment field unless the source table is empty. Source engine doesn't matter (tested with myisam and innodb). 

How to repeat:
CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT) ENGINE=myisam;
INSERT INTO t1 VALUES(1);
ALTER TABLE t1 ENGINE=archive;

=> ERROR 1022 (23000): Can't write; duplicate key in table '#sql-1b51_1'
[4 Jul 2008 14:11] Hartmut Holzgraefe
Workaround: 

CREATE TABLE copy LIKE original; 
ALTER TABLE copy ENGINE=archive;
INSERT INTO copy SELECT * FROM original;
DROP TABLE original;
RENAME TABLE copy TO original;
[4 Jul 2008 14:12] Hartmut Holzgraefe
Might be related to bug #37182
[6 Jan 2012 4:08] Kjeld Flarup
Improved workaround. The select should be ordered by the auto increment field, else we still may run into the bug

CREATE TABLE copy LIKE original; 
ALTER TABLE copy ENGINE=archive;
INSERT INTO copy SELECT * FROM original  ORDER BY id;
DROP TABLE original;
RENAME TABLE copy TO original;
[13 Feb 2013 1:26] J RS
This is likely a duplicate of #37182 AND #65450.

+1 vote for elevating this since it seems to be making a lot of people uncomfortable. 

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 in #37182: when you do a table conversion, relax the autoincrement restriction.
[4 Jul 2013 15:20] Hartmut Holzgraefe
Still an issue with 5.1.70, no problem with 5.5.30 though ... so either close or mark as "won't fix" for 5.1?
[26 Nov 2013 0:29] Perry Harrington
Simplest workaround is the following:

ALTER TABLE t1 AUTO_INCREMENT=0 ENGINE=ARCHIVE;

That pre-sets the AUTO_INCREMENT value before the table schema is duplicated, getting around the fact that AUTO_INCREMENT is monotonic and ARCHIVE enforces monotonic keys.
[26 Nov 2013 8:27] Hartmut Holzgraefe
Looks as if I was wrong about 5.5.30 ...

Still reproducible in 5.5.33 and 5.6.14
[4 Mar 2016 9:34] Hartmut Holzgraefe
... and also still in 5.7.9