List:Internals« Previous MessageNext Message »
From:Sunny Bains Date:October 15 2007 11:58am
Subject:Re: AUTOINC + triggers question
View as plain text  
Guilhem,

Guilhem Bichot wrote:
> On Sat, Oct 13, 2007 at 08:24:04AM +0530, Sunny Bains wrote:
>> While investigating http://bugs.mysql.com/bug.php?id=31540, I noticed
>> that MySQL calls ha_innobase::get_auto_increment() with different
>> values for nb_desired_values within a multi-row insert.
> 
> Could you please post here a simple SQL testcase which demonstrates
> the gap occuring?
> 

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    |
+----+------+

The backtrace that I posted in the original message was taken while
running the above example.

Breakpoint 2, ha_innobase::get_auto_increment (this=0x8b78988, offset=1,
     increment=1, nb_desired_values=1, first_value=0x8f1b9260,
     nb_reserved_values=0x8f1b9258) at handler/ha_innodb.cc:7314

and so we reserve one value.

Subsequent calls have the following values:

Breakpoint 2, ha_innobase::get_auto_increment (this=0x8b78988, offset=1,
     increment=1, nb_desired_values=2, first_value=0x8f1b9260,
     nb_reserved_values=0x8f1b9258) at handler/ha_innodb.cc:7314

...

Breakpoint 2, ha_innobase::get_auto_increment (this=0x8b78988, offset=1,
     increment=1, nb_desired_values=2, first_value=0x8f1b9260,
     nb_reserved_values=0x8f1b9258) at handler/ha_innodb.cc:7314

...
and so we reserve two values and so on.

Will this do or are you asking for something else ?

Regards,
-sunny
Thread
AUTOINC + triggers questionSunny Bains13 Oct
  • Re: AUTOINC + triggers questionGuilhem Bichot15 Oct
    • Re: AUTOINC + triggers questionSunny Bains15 Oct
      • Re: AUTOINC + triggers questionGuilhem Bichot15 Oct
        • Re: AUTOINC + triggers questionSunny Bains15 Oct