MySQL Bugs: #26316: Triggers create duplicate entries on auto-increment columns

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

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.

I am trying to implement a data modification log with triggers. This is unavoidably necessary for our solution.

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)

a testcase

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

reported on the forums also
http://forums.mysql.com/read.php?22,115794,139560#msg-139560

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.

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.

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.

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

Heikki,

How about keeping a number of auto-inc looks on trx level, while you keep locks themselves on statement level ???

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.

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 ??

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.

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.

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?

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

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)

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)

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)

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)

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)

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)

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)

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)

Bug #53791 was marked as duplicate of this one.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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)

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.

Corrected change log to refer to 5.1.41; clarified that this bug is for the InnoDB Plugin.

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.

Sorry, the issue was noticed in the MySQL version 5.0.51a-community.

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.

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.

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

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

Above scenario expressed as MTR test to be run with:
```
for((i=0;i<3;++i));do echo "innodb-autoinc-lock-mode=$i";./mtr --mysqld=--innodb-autoinc-lock-mode=$i the.test;done
```
looks like this:
```
CREATE TABLE Logs( id INT PRIMARY KEY AUTO_INCREMENT );
CREATE TABLE Table1( id INT PRIMARY KEY AUTO_INCREMENT, data TEXT );
CREATE TABLE Table2( id INT PRIMARY KEY AUTO_INCREMENT, data TEXT );

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 ;|

CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY, c2 CHAR(10));
INSERT INTO t1(c2) VALUES ('abc');
--let $i=0
while($i<18){
        INSERT INTO t1(c2) SELECT c2 FROM t1;
        --inc $i
}

--connect (C1, localhost, root,,)
        --send INSERT INTO Table2 (data) SELECT c1 FROM t1;

--connect (C2, localhost, root,,)
        INSERT INTO Table1 ( data ) SELECT c1 FROM t1;

--connection C1
        --reap

--connection default
--disconnect C1
--disconnect C2
DROP TABLE t1;
DROP TABLE Table1;
DROP TABLE Table2;
DROP TABLE Logs;
```
I've verified that it passes on recent mysql-trunk and for Bug#34097862 fix proposal, except that for innodb-autoinc-lock-mode=0 it times out waiting for an autoincrement lock.
example durations from asan debug compile-el8:
branch: 1 -> 487 sec, 2 -> 491
trunk:1 -> 515 sec, 2 -> 509