Bug #27950 Duplicate entry error in auto-inc after mysqld restart

• Submitted: 19 Apr 2007 10:34
• Modified: 20 Jun 2010 0:49
• Reporter: kenichi yonekawa
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.37, 4.1.13, 5.1
• OS: Other (Windows / Linux)
• Assigned to: Sunny Bains
• CPU Architecture: Any
• Tags: auto_increment, innodb

[19 Apr 2007 10:34] kenichi yonekawa

Description:
Duplicate entry error occurs when the following sequence after mysqld restart(reinitialized auto_increment counter).
 
 1. delete(multiple rows)  
 2. insert(first insert after mysqld restart) 
 3. rollback
 4. insert(error!)

After this, All INSERT to the table returns an error until mysqld stopped and restarted.

How to repeat:
use test;
create table duptest(c1 bigint auto_increment primary key)engine=innodb;
insert into duptest values(1),(2),(3),(4),(5);
commit;

mysqld restart

use test;
set autocommit=0;
delete from duptest;
insert into duptest values(null);
rollback;
insert into duptest values(null);
->ERROR 1062 (23000): Duplicate entry '2' for key 1

[19 Apr 2007 11:08] Sveta Smirnova

Thank you for the report.

Verified as described.

[19 Apr 2007 22:12] Heikki Tuuri

Hmm... the reason probably is that InnoDB's auto-inc counter is only initialized at the INSERT after the DELETE FROM, and thus the counter gets the value 1. It does not take into account that the ROLLBACK will change the situation!

[19 Apr 2007 22:21] Heikki Tuuri

Hmm... in the code that initializes the auto-inc counter in ha_innodb.cc, we currently in 5.0 do a consistent read to determine the maximum value. That consistent read should also see rows that were delete-marked by the CURRENT transaction. That would solve the problem. I need to check if there is some easy mechanism to perform that consistent read in that way.

[24 May 2007 14:18] Heikki Tuuri

Assign this to Sunny.

[28 May 2007 12:25] Heikki Tuuri

A simpler solution is to init the counter also at a DELETE from an auto-inc table.

[28 May 2007 12:25] Heikki Tuuri

A long-term solution is persistence of the auto-inc counter.

[25 Jul 2007 20:49] Guilhem Bichot

Sunny: should the counter also be initialized by the first UPDATE, the first LOAD DATA INFILE?

[25 Jul 2007 22:33] Sunny Bains

Guilhem,

The LOAD DATA goes via ha_innobase:write_row() and that should work already.
The bug fix for http://bugs.mysql.com/bug.php?id=28781 should take care
of initializing the AUTOINC from ha_innobase::update_row().

Regards,
-sunny

[29 Aug 2007 18:41] Timothy Smith

This has been fixed in MySQL 5.1 (5.1.22) as part of the changes for Bug #16979.  It has not been fixed in MySQL 5.0 or earlier.

Docs team, please set this bug back to "Verified", because a fix for 5.0 would require a separate implementation.

[4 Sep 2007 17:12] Bugs System

Pushed into 5.1.23-beta

[13 Sep 2007 2:32] Trent Lloyd

Setting status to verified as not fixed in 5.0

[8 Nov 2007 13:28] Heikki Tuuri

We will not fix this in 5.0, because the fix is quite long and could endanger the stability of 5.0, and because the behavior of 5.0 should not be changed.

[5 May 2010 15:01] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 2:58] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 5:53] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:22] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:50] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 22:57] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 11:53] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:31] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:19] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)