Bug #31612 Trigger fired multiple times leads to gaps in auto_increment sequence
Submitted: 15 Oct 2007 14:10 Modified: 17 Oct 2008 17:16
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1-bk OS:Linux
Assigned to: Guilhem Bichot
Tags: bfsm_2007_12_06
Triage: Triaged: D2 (Serious)

[15 Oct 2007 14:10] Guilhem Bichot
Description:
Reported by Sunny Bains here http://lists.mysql.com/internals/35110
When a table 'a' has an ON INSERT trigger into an InnoDB table 'b', and 'b' has an auto_increment column, and a multi-row INSERT statement is issued against 'a',
values inserted in 'b' by the trigger exhibit gaps (they are like 1,2,4,6,8), while we would expect no gaps as the testcase is single-threaded.

How to repeat:
From the lists post, quoting Sunny:

This is running the new autoinc fix with autoinc_lock_mode = 1. Where
InnoDB allocates nb_reserved_values each time get_auto_increment()
is called via update_row(). Also for the example below to work, you
need to add the following line to ha_innobase::start_stmt():

        /* Reset the AUTOINC statement level counter for multi-row INSERTs.
        */
        trx->n_autoinc_rows = 0;

create table a (a int, val char(1)) engine=InnoDB;

create table b (
        b int auto_increment primary key,
        val char(1)) engine=InnoDB;

create trigger a_after_insert after
         insert on a for each row insert into b
         set val NEW.val;

insert into a values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
         (123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');

mysql> select * from b;
+----+------+
| b  | val  |
+----+------+
|  1 | a    |
|  2 | b    |
|  4 | c    |
|  6 | d    |
|  8 | e    |
| 10 | f    |
| 12 | g    |
+----+------+
end of quote.

Suggested fix:
I will talk with the runtime team about it.
[22 Oct 2007 19:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36078

ChangeSet@1.2596, 2007-10-22 20:52:05+02:00, guilhem@gbichot3.local +8 -0
  Fix for BUG#31612
  "Trigger fired multiple times leads to gaps in auto_increment sequence".
  Bug was never experienced by users because it occurs only with the fix
  for BUG31540 "incorrect auto_increment values used for multi-row insert
  trigger" which is not yet in the main 5.1.
  The bug was that if a trigger fired multiple times inside a top
  statement (for example top-statement is a multi-row INSERT, 
  and trigger is ON INSERT), and that trigger inserted into an auto_increment
  column, then gaps could be observed in the auto_increment sequence,
  even if there were no other users of the database (no concurrency).
  It was wrong usage of THD::auto_inc_intervals_in_cur_stmt_for_binlog.
[22 Oct 2007 19:09] Guilhem Bichot
The bug does not exist in versions <5.1. It has not been in any 5.1 release.
[18 Aug 2008 19:28] Sveta Smirnova
Bug #38872 seems to be duplicate of this one.
[2 Oct 2008 8:03] Mattias Jonsson
OK to push by me. (I mailed a couple of question, but the patch do not depend on the answers)
[7 Oct 2008 8:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55534

2689 Guilhem Bichot	2008-10-06
      Fix for BUG#31612
      "Trigger fired multiple times leads to gaps in auto_increment sequence".
      The bug was that if a trigger fired multiple times inside a top
      statement (for example top-statement is a multi-row INSERT,
      and trigger is ON INSERT), and that trigger inserted into an auto_increment
      column, then gaps could be observed in the auto_increment sequence,
      even if there were no other users of the database (no concurrency).
      It was wrong usage of THD::auto_inc_intervals_in_cur_stmt_for_binlog.
      Note that the fix changes "class handler", I'll tell the Storage Engine API team.
[7 Oct 2008 8:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55540

2689 Guilhem Bichot	2008-10-06
      Fix for BUG#31612
      "Trigger fired multiple times leads to gaps in auto_increment sequence".
      The bug was that if a trigger fired multiple times inside a top
      statement (for example top-statement is a multi-row INSERT,
      and trigger is ON INSERT), and that trigger inserted into an auto_increment
      column, then gaps could be observed in the auto_increment sequence,
      even if there were no other users of the database (no concurrency).
      It was wrong usage of THD::auto_inc_intervals_in_cur_stmt_for_binlog.
      Note that the fix changes "class handler", I'll tell the Storage Engine API team.
[9 Oct 2008 17:49] Bugs System
Pushed into 5.1.30  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)
[9 Oct 2008 18:47] Paul Dubois
Noted in 5.1.29 (not 5.1.30) changelog.

A trigger for an InnoDB table activating multiple times could lead to
AUTO_INCREMENT gaps.

Resetting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:45] Bugs System
Pushed into 6.0.8-alpha  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[17 Oct 2008 17:16] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:02] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:21] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:49] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)