Bug #53791 Auto increment fail on double insertion through triggers
Submitted: 19 May 2010 9:14 Modified: 26 May 2010 7:51
Reporter: Sophie Lvy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.77 OS:Linux (CentOS release 5.4 (Final))
Assigned to: CPU Architecture:Any
Tags: autoincrement, duplicate key, trigger

[19 May 2010 9:14] Sophie Lvy
Description:
I have 2 sources tables (and 2 triggers). When inserting inside one of these tables, a trigger add a row in a third table. But when there is a lot of insertions in both source tables, autoincrement in the third table fails and a duplicate key is generated.

How to repeat:
-- --------------------------------------------------------
-- Step 1 Create database and triggers.
-- --------------------------------------------------------

CREATE DATABASE `dev_test_bugging_mysql` ;

DROP TABLE IF EXISTS  dev_test_bugging_mysql.src_a;
DROP TABLE IF EXISTS  dev_test_bugging_mysql.src_b;
DROP TABLE IF EXISTS  dev_test_bugging_mysql.dst;

CREATE TABLE IF NOT EXISTS dev_test_bugging_mysql.`src_a` (
  `id` int(11) NOT NULL auto_increment,
  `col1` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS dev_test_bugging_mysql.`src_b` (
  `id` int(11) NOT NULL auto_increment,
  `col1` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS dev_test_bugging_mysql.`dst` (
  `id` int(11) NOT NULL auto_increment,
  `msg_from` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
DROP TRIGGER IF EXISTS  dev_test_bugging_mysql.`trg_from_a`;
DELIMITER //
CREATE TRIGGER dev_test_bugging_mysql.`trg_from_a` AFTER INSERT ON `src_a`
    FOR EACH ROW BEGIN
        INSERT INTO dev_test_bugging_mysql.dst
            SET
                msg_from = CONCAT("from src_a.id = ",NEW.id);
    END
//
DELIMITER ;

DROP TRIGGER IF EXISTS dev_test_bugging_mysql.`trg_from_b`;
DELIMITER //
CREATE TRIGGER dev_test_bugging_mysql.`trg_from_b` AFTER INSERT ON `src_b`
    FOR EACH ROW BEGIN
        INSERT INTO dev_test_bugging_mysql.dst
            SET
                msg_from = CONCAT("from src_b.id = ",NEW.id);
    END
//
DELIMITER ;
exit

-- --------------------------------------------------------
-- Step 2 : Leave mysql and open 2 consoles and execute
-- these command line in the same time.
-- --------------------------------------------------------
Console 1 :
for i in {1..1000}  ; do  mysql -uroot -pmypassword -Bse "INSERT dev_test_bugging_mysql.src_a SET col1='coucou';"; done
Console 2 :
for i in {1..1000}  ; do  mysql -uroot -pmypassword -Bse "INSERT dev_test_bugging_mysql.src_b SET col1='coucou';"; done

-- --------------------------------------------------------
-- Result :
-- --------------------------------------------------------
Each console report errors on insertion :

---- Console 1 : ----

[root@db-dev-1 ~]# for i in {1..2000}  ; do  mysql -uroot -pmypassword -Bse "INSERT dev_test_bugging_mysql.src_a SET col1='coucou';"; done
ERROR 1062 (23000) at line 1: Duplicate entry '1615' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '2673' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '3023' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '3373' for key 1
[root@db-dev-1 ~]#

---- Console 2 : ----
[root@db-dev-1 ~]# for i in {1..2000}  ; do  mysql -uroot -pmypassword -Bse "INSERT dev_test_bugging_mysql.src_b SET col1='coucou';"; done
ERROR 1062 (23000) at line 1: Duplicate entry '541' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '899' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '1257' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '1972' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '2323' for key 1
ERROR 1062 (23000) at line 1: Duplicate entry '3723' for key 1
[root@db-dev-1 ~]#

The autoincrement on dst table has obviously failed to create coherent primary key....

Suggested fix:
As temporary soluce, I have avoided the problem by setting myself the id insertion query inside triggers, but it's quite dirty...

DELIMITER //
CREATE TRIGGER dev_test_bugging_mysql.`trg_from_a` AFTER INSERT ON `src_a`
    FOR EACH ROW BEGIN
        INSERT INTO dev_test_bugging_mysql.dst
            SET
                id = (SELECT IFNULL(MAX(dst2.id),0)+1 from dst as dst2 ),
                msg_from = CONCAT("from src_a.id = ",NEW.id);
    END
//
DELIMITER ;
[19 May 2010 14:46] Valeriy Kravchuk
Thank you for the problem report. What exact version of MySQL server, 5.x.y, do you use?
[19 May 2010 15:11] Sophie Lvy
Hi, I have completed the exact version of MySQL server.
[26 May 2010 7:51] Sveta Smirnova
Thank you for the feedback.

This is duplicate of bug #26316. It was decided to not fix version 5.0 which is in Extended Support period now. Please upgrade to current GA version 5.1.47
[28 Apr 2022 14:18] Jakub Lopuszanski
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 IF NOT EXISTS `src_a` (
  `id` int(11) NOT NULL auto_increment,
  `col1` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `src_b` (
  `id` int(11) NOT NULL auto_increment,
  `col1` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `dst` (
  `id` int(11) NOT NULL auto_increment,
  `msg_from` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);
DELIMITER |;
CREATE TRIGGER `trg_from_a` AFTER INSERT ON `src_a`
    FOR EACH ROW BEGIN
        INSERT INTO dst
            SET
                msg_from = CONCAT("from src_a.id = ",NEW.id);
    END|

CREATE TRIGGER `trg_from_b` AFTER INSERT ON `src_b`
    FOR EACH ROW BEGIN
        INSERT INTO dst
            SET
                msg_from = CONCAT("from src_b.id = ",NEW.id);
    END|

CREATE PROCEDURE work_a(t INT)
  BEGIN
    DECLARE CONTINUE HANDLER FOR 1205 BEGIN END;
    WHILE 0<t DO
      SET t = t - 1;
      INSERT src_a SET col1='coucou';
    END WHILE;
  END|

CREATE PROCEDURE work_b(t INT)
  BEGIN
    DECLARE CONTINUE HANDLER FOR 1205 BEGIN END;
    WHILE 0<t DO
      SET t = t - 1;
      INSERT src_b SET col1='coucou';
    END WHILE;
  END|

DELIMITER ;|

--connect (con_a, localhost, root,,)
    --send CALL work_a(10000)

--connect (con_b, localhost, root,,)
    --send CALL work_b(10000)

--connection con_a
    --reap
--connection con_b
    --reap
--connection default
--disconnect con_a
--disconnect con_b

DROP PROCEDURE work_a;
DROP PROCEDURE work_b;
DROP TABLE src_a;
DROP TABLE src_b;
DROP TABLE dst;
```

I've verified that it passes on recent mysql-trunk and for Bug#34097862 fix proposal