MySQL Bugs: #31612: Trigger fired multiple times leads to gaps in auto

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	CPU Architecture:	Any
Tags:	bfsm_2007_12_06

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.

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.

The bug does not exist in versions <5.1. It has not been in any 5.1 release.

Bug #38872 seems to be duplicate of this one.

OK to push by me. (I mailed a couple of question, but the patch do not depend on the answers)

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.

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.

Pushed into 5.1.30  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)

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.

Pushed into 6.0.8-alpha  (revid:guilhem@mysql.com-20081006140659-0ktkpe0glfgs6am2) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)

Noted in 6.0.8 changelog.

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)

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)

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)