Bug #26316 Triggers create duplicate entries on auto-increment columns
Submitted: 13 Feb 2007 12:01 Modified: 4 Aug 2010 17:51
Reporter: Jan Roos Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.36-BK, 5.0.32, 5.0.38BK, 5.1.17BK OS:Any (*)
Assigned to: Sunny Bains CPU Architecture:Any
Tags: autoinc, bfsm_2007_03_01, trigger, v6
Triage: Triaged: D2 (Serious) / R4 (High) / E3 (Medium)

[13 Feb 2007 12:01] Jan Roos
Description:
When multiple triggers insert into same table, they generate duplicate entry error on auto-increment column.

I get an error something like this: 
Error: Duplicate entry '2167777' for key 1

How to repeat:
CREATE TABLE Logs( id INT PRIMARY KEY auto_increment ) engine=INNODB;
CREATE TABLE Table1( id int PRIMARY KEY auto_increment, data TEXT ) engine=INNODB;
CREATE TABLE Table2( id int PRIMARY KEY auto_increment, data TEXT ) engine=INNODB;

DELIMITER $$

CREATE TRIGGER log_Table1 AFTER INSERT ON Table1 FOR EACH ROW
BEGIN
INSERT INTO Logs() VALUES();
END $$

CREATE TRIGGER log_Table2 AFTER INSERT ON Table2 FOR EACH ROW
BEGIN
INSERT INTO Logs() VALUES();
END $$

DELIMITER ;

Now insert into tables Table1 and Table2 __at_the_same_time_from_different connections__:

INSERT INTO Table1 ( data ) SELECT Id FROM some_table_with_much_rows;

INSERT INTO Table2 ( data ) SELECT Id FROM some_table_with_much_rows;

some_table_with_much_rows should be a table with enough colums so that you can launch the second insert before the first finishes.

From one connection I get
"Error: Duplicate entry '<no>' for key 1"

<no> is id from table Logs. 

Suggested fix:
The triggers should lock the table "Logs" or something.

I expect both inserts to complete without errors.
[13 Feb 2007 12:04] Jan Roos
I am trying to implement a data modification log with triggers. This is unavoidably necessary for our solution.
[13 Feb 2007 15:39] Valeriy Kravchuk
Thank you for a bug report. Verified with 5.0.36-BK on Linux. I had created table t1 as follows:

mysql> create table t1 (c1 int auto_increment primary key, c2 char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(c2) values('abc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(c2) select c2 from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> insert into t1(c2) select c2 from t1;
Query OK, 262144 rows affected (1.56 sec)
Records: 262144  Duplicates: 0  Warnings: 0

Then, from the same connection:

mysql> INSERT INTO Table2 (data) SELECT c1 FROM t1;

And, from connection 2 immediately:

mysql> INSERT INTO Table1 ( data ) SELECT c1 FROM t1;
Query OK, 524288 rows affected (23.23 sec)
Records: 524288  Duplicates: 0  Warnings: 0

at the same time I've got in connection 1:

ERROR 1062 (23000): Duplicate entry '15763' for key 1
mysql> select * from Logs where id=15763;
+-------+
| id    |
+-------+
| 15763 |
+-------+
1 row in set (0.00 sec)

mysql> select count(*) from Logs;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.57 sec)

mysql> show variables like '%isolatio%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
[24 Feb 2007 19:01] Shane Bester
a testcase

Attachment: testcase.c (text/plain), 6.02 KiB.

[24 Feb 2007 19:01] Shane Bester
reported on the forums also
http://forums.mysql.com/read.php?22,115794,139560#msg-139560
[17 Apr 2007 23:43] Konstantin Osipov
When trying to repeat the bug, I get the following error in InnoDB error log:

070417 19:39:54  InnoDB: ERROR: the age of the last checkpoint is 9433927,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

Could that be the real cause of the issue? 
Please try to reproduce with increased log space.
[18 Apr 2007 8:37] Valeriy Kravchuk
Please, note that in my test case from comment dated 13 Feb 16:39 neither BLOB nor TEXT coluns were used. int + char(10) with 512K of rows should not be too much for default log space.
[25 May 2007 13:26] Alexey Botchkov
To reproduce this bug in simplest way one needs
    two InnoDB tables (A and C)
    AFTER INSERT trigger on A that inserts one record into C

start two queries in parallel
    INSERT INTO A
    INSERT INTO C

what wrong can happen
    first query calls ha_innobase::write_row for table A
    The table gets locked for auto_increment (row_lock_table_autoinc_for_mysql)
    what sets trx->auto_inc_lock.
    After that trigger is executed and ha_innobase::write_row is called
    for table C. This time row_lock_table_autoinc_for_mysql doesn't
    lock table C as trx->auto_inc_lock isn't NULL.

    at the same time the thread with the second query
    calls ha_innobase::write_row for table C. This thread should
    make a stop on row_lock_table_autoinc_for_mysql, but doesn't do that
    as first query didn't lock C table.

So that we get same values for the auto_increment field.
[28 May 2007 11:49] Heikki Tuuri
I assign this to Sunny, who is our auto-inc expert.

The problem derives from the fact that InnoDB code assumes that a single statement can only manipulate a single auto-inc table. But triggers break this.

Hmm... instead a of a single trx->auto_inc_lock, we could have an array and a count:

struct trx_struct{

...

     ulint     n_auto_inc_locks;
     lock_t*   auto_inc_locks[50]; /* I assume 50 is enough! */
...
}

But is there some simpler solution?

--Heikki
[5 Oct 2007 16:15] Sinisa Milivojevic
Heikki,

How about keeping a number of auto-inc looks on trx level, while you keep locks themselves on statement level ???
[24 Sep 2008 23:05] Jeremy Tinley
Suggest the documentation be updated for triggers and how InnoDB handles Auto Increment to document this shortcoming.  We have the same situation where we are implementing a data logging solution and have duplicate keys on auto increment triggers.
[2 Oct 2008 16:42] Gniontas Gniontas
Any update on this bug? I ran into the same issue i.e. an Insert and an Update trigger on the same table. Both tables are trying to modify the same set of tables.

Unfortunately mysql doesn't let you lock tables from within a trigger. So, I ended up locking tables from my java program when doing inserts or updates.
But even that doesn't work due to other issues.. http://myt.ag/URLWeb.aspx?email=steve%40fooworks.com&url=http%3a%2f%2fbugs.mysql.com%2f840...

I don't think even the above bugfix works right. I get error: 
"Table 'a' was not locked with LOCK TABLES".

Is there another way to work around this problem ??
[2 Oct 2008 23:22] Gniontas Gniontas
It's pretty ridiculous how this bug has managed to survive so long. It not only renders triggers useless but I seriously think mysql implementation and documentation has some serious quality issues. 

I wish I had gotten involved with this project before mysql was chosen as dbms.
It really sucks.
[5 Oct 2008 22:58] Sunny Bains
The bug should not affect simple-inserts where the autoinc_lock_mode setting is set to either "consecutive" or "interleaved". This new autoinc lock mode setting was introduced in version 5.1.22. Please see http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html for definition of "simple-inserts" and the new autoinc lock modes.
[7 Oct 2008 14:25] Jeremy Tinley
So 5.1.22 and later are unaffected?  Since 5.1 is not RC and 5.0 support is around, is there hope of backporting the fix to the 5.0 version?
[27 Feb 2009 17:56] Thomas Franke
We discover this phenoma at heavy loads on 5.0.67 (innoDB) as well.
It seems to appear in combination of "LOCK IN SHARE MODE" + "TRANSACTION" + "INSERT INTO SELECT [...]".

Is there any plan to deliver a patch for the 5.0.x-Series?
For us it is currently not a good idear to switch to 5.1.x due to reported bugs with this series. (avg 1.000 queries a second, more than 110 GB of data)

Breakting auto-inc within a DBMS without native sequences makes the whole DBMS unreliable.

Best regards
Thomas
[14 Oct 2009 14:39] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091008121819-lh7gryq2um8a5h09) (merge vers: 5.1.40) (pib:13)
[22 Oct 2009 6:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:06] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)
[18 Dec 2009 10:30] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:46] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:01] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:15] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[5 May 2010 15:10] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[26 May 2010 7:53] Sveta Smirnova
Bug #53791 was marked as duplicate of this one.
[28 May 2010 5:54] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:23] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:51] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[15 Jun 2010 8:12] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:28] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:56] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:34] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:21] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 Jun 2010 19:29] John Russell
Here is the text for the change log:

When a trigger inserts into a table containing an auto-increment column, 
an error "Error: Duplicate entry" could occur if another
insert was happening simultaneously.
[4 Aug 2010 17:50] John Russell
Corrected change log to refer to 5.1.41; clarified that this bug is for the InnoDB Plugin.
[25 Jul 2012 10:48] Ajay Giri
Me to facing the same issue where multiple triggers are trying to insert record in the same table having one auto_increment column and getting the error 'Duplicate entry '68256666' for key 1'. These triggers get executed by database operations from more than one MySQL connection i.e parallel operations.

I am using the MySQL version 5.1.47 and all my tables are using the InnoDB storage engine.
[25 Jul 2012 10:54] Ajay Giri
Sorry, the issue was noticed in the MySQL version 5.0.51a-community.
[14 Sep 2012 16:34] Alexander Rubin
This bug is still reproducible with innodb_autoinc_lock_mode = 0 in MySQL 5.1
(tested with MySQL 5.1.48, 5.1.65+). As a workaround set innodb_autoinc_lock_mode = 1 (default) or innodb_autoinc_lock_mode = 2.
[14 Sep 2012 16:34] Alexander Rubin
This bug is still reproducible with innodb_autoinc_lock_mode = 0 in MySQL 5.1
(tested with MySQL 5.1.48, 5.1.65+). As a workaround set innodb_autoinc_lock_mode = 1 (default) or innodb_autoinc_lock_mode = 2.
[3 Jul 2013 9:13] sameer joshi
I am getting the same issue in version 5.5.15
multiple triggers are trying to insert record in the same table having one auto increment column and getting the error Duplicate entry for key primary
[11 Feb 2016 11:15] Fabio Napodano
still having this issue using 5.6.26

I have booth before and after insert triggers on a table, and this causes duplicate key error on an autoincrement field sometimes