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 9:14]
Sophie Lvy
[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