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 13:01	Modified:	4 Mar 2008 11:27
Reporter:	Jan Roos
Status:	In review
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.0.32, 5.0.38BK, 5.1.17BK	OS:	Any (*)
Assigned to:	Sunny Bains	Target Version:
Tags:	v6, bfsm_2007_03_01, trigger, autoinc
Triage:	Triaged: D2 (Serious) / R4 (High) / E3 (Medium)

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)