MySQL Bugs: #20627: INSERT DELAYED does not honour auto_increment

Bug #20627	INSERT DELAYED does not honour auto_increment_* variables
Submitted:	22 Jun 2006 14:16	Modified:	5 Oct 2006 17:13
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
didn't test with 5.0.
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
set @@session.auto_increment_increment=3, @@session.auto_increment_offset=1;
insert delayed into t1 values (null),(null);
inserts 1,2 into the table.
While a normal INSERT inserts 1,4 as expected

How to repeat:
see description

I doubt that this is a bug. Session variables have a meaning for one thread only. But INSERT DELAYED is executed by a backgroud thread. It doesn't see the session variables of the foregroud thread. And I don't see any attempt in the code to transfer these settings to the delayed thread. If you think this is a desirable feature, please make this a feature request. In this case please specify how the delayed thread should behave when it receives intermixed rows from multiple foreground threads with different session settings.

From an internal discussion: It is a bug. auto_increment_increment and auto_increment_offset have been introduced for one reason: so that you could have two mysqld replicating to each other (A is slave of B, B is slave of A); But why is it necessary to specify this per session? Because mysqld allows you to (SET SESSION is possible on these variables). How to fix the bug: add auto_increment_increment and auto_increment_offset members to the delayed_row class. In write_delayed() add row->auto_increment_* = thd.auto_increment_*; In delayed_insert::handle_inserts() add the reverse operation.

A similar problem about SET INSERT_ID in bug#20830.

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/12054

ChangeSet@1.2246, 2006-09-15 19:43:42+02:00, istruewing@chilla.local +3 -0
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  
  INSERT DELAYED ignored an explicitly set INSERT_ID and session
  specific auto_increment_* variables.
  
  The problem was that the inserts are done by a system thread,
  which does not have access to the session variables of the user
  thread.
  
  On a proposal of Guilhem I fixed it so that the variables are
  copied to the data structure for every delayed row. The system
  thread sets its session variables from these values.

comments for this bugfix and BUG#20830 sent by mail

comments sent by email

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/12264

ChangeSet@1.2246, 2006-09-20 11:05:11+02:00, istruewing@chilla.local +3 -0
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  
  INSERT DELAYED ignored an explicitly set INSERT_ID and session
  specific auto_increment_* variables.
  
  The problem was that the inserts are done by a system thread,
  which does not have access to the session variables of the user
  thread.
  
  On a proposal of Guilhem I fixed it so that the variables are
  copied to the data structure for every delayed row. The system
  thread sets its session variables from these values.

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/12548

ChangeSet@1.2338, 2006-09-26 17:43:06+02:00, istruewing@chilla.local +3 -0
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  Merge from 5.0.
  Changed auto_increment handling to the 5.1 pattern.
  Added start-of-new-statement detection for releasing the
  auto_increment values. This aligns the behaviour of delayed
  inserts to non-delayed inserts.

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/12831

ChangeSet@1.2338, 2006-09-29 13:23:33+02:00, istruewing@chilla.local +2 -0
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  Merge from 5.0.
  Changed auto_increment handling to the 5.1 pattern.

Pushed to 5.1-engines and 5.0-engines.

Fixed in 5.0.26, 5.1.12.

Noted in 5.0.26, 5.1.12 changelogs.

INSERT DELAYED did not honor SET INSERT_ID or the auto_increment_*
system variables.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54