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

[22 Jun 2006 14:16] Guilhem Bichot
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
[3 Jul 2006 8:01] Ingo Strüwing
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.
[3 Jul 2006 10:57] Ingo Strüwing
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.
[7 Jul 2006 15:54] Calvin Sun
A similar problem about SET INSERT_ID in bug#20830.
[15 Sep 2006 17:43] 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/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.
[16 Sep 2006 12:12] Guilhem Bichot
comments for this bugfix and BUG#20830 sent by mail
[18 Sep 2006 12:07] Guilhem Bichot
comments sent by email
[20 Sep 2006 19:41] 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/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.
[26 Sep 2006 15:44] 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/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.
[29 Sep 2006 11:25] 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/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.
[29 Sep 2006 17:23] Ingo Strüwing
Pushed to 5.1-engines and 5.0-engines.
[3 Oct 2006 8:48] Sergey Vojtovich
Fixed in 5.0.26, 5.1.12.
[5 Oct 2006 17:13] Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.

INSERT DELAYED did not honor SET INSERT_ID or the auto_increment_*
system variables.
[28 Nov 2007 10:24] Bugs System
Pushed into 6.0.4-alpha
[28 Nov 2007 10:26] Bugs System
Pushed into 5.1.23-rc
[28 Nov 2007 10:27] Bugs System
Pushed into 5.0.54