MySQL Bugs: #65450: Archive AUTO_INCREMENT must not go backwards, but replication does

Bug #65450	Archive AUTO_INCREMENT must not go backwards, but replication does
Submitted:	29 May 2012 13:31	Modified:	29 May 2012 19:56
Reporter:	Andreas Faust	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S2 (Serious)
Version:	5.1.60 - 5.5.22	OS:	Linux (Debian/Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	archive, duplicate key, replication

Description:
In Replicating environment, multiple inserts on same table can happen. 

It seems to be logic, that replication log should be sequentially forward in AUTO_INCREMENT columns, but reality shows on multiple concurrent inserts one should not rely on that.

But ARCHIVE Table engine relies on exactly this behaviour.

This introduces a "Duplicate KEY" error on replicating slave, which can be circumvented by skipping the statement ( suffering the loss of data and integrity ), or by altering ARCHIVE tables on slaves not to use AUTO_INCREMENT and drop primary key.

How to repeat:
mysql> CREATE TABLE `LOG_MAIL_2012_05` (
`pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
`initiator` char(255) NOT NULL,
`client_pk` int(10) unsigned NOT NULL DEFAULT '0',
`datetime_insert` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mail_subject` char(255) NOT NULL,
`mailtype` enum('email','pdf','') NOT NULL DEFAULT 'email',
`mail_body` text,
`mail_from` char(127) NOT NULL,
`mail_to` char(127) NOT NULL,
`send_result` varchar(126) DEFAULT NULL,
 PRIMARY KEY (`pk`)
) ENGINE=ARCHIVE AUTO_INCREMENT=2000 DEFAULT CHARSET=latin1;

insert into LOG_MAIL_2012_05 VALUES(2001,'me',4,NOW(),'subject','pdf','body','from@email','to@email','Failure');
Query OK, 1 row affected (0.03 sec)

insert into LOG_MAIL_2012_05 VALUES(2002,'me',4,NOW(),'subject','pdf','body','from@email','to@email','Failure');
Query OK, 1 row affected (0.03 sec)

insert into LOG_MAIL_2012_05 VALUES(2005,'me',4,NOW(),'subject','pdf','body','from@email','to@email','Failure');
Query OK, 1 row affected (0.00 sec)

insert into LOG_MAIL_2012_05 VALUES(2004,'me',4,NOW(),'subject','pdf','body','from@email','to@email','Failure');

ERROR 1022 (23000): Can't write; duplicate key in table 'LOG_MAIL_2012_05'

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #37182

This is probably also a duplicate of #37871.