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