MySQL Bugs: #490: Accidental introduction of a bug with auto

Bug #490	Accidental introduction of a bug with auto_increment and INSERT SELECT
Submitted:	24 May 2003 7:09	Modified:	1 Jul 2003 6:14
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Any (all)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Due to
ChangeSet@1.1416.107.3, 2003-03-16 16:28:30+02:00,
Write binlog before commit when doing INSERT ... SELECT,

we write to the binlog before setting thd->last_insert_id, which
results in wrong SET INSERT_ID in the master's binlog.
Affects only 4.0.13, not 4.0.12. All table types (despite what
the original bug report on mysql@lists said) are affected.

How to repeat:
  CREATE TABLE source (
     a VARCHAR(50)
  );
 
  INSERT INTO source
  (a)
  VALUES
  ('1'), ('2');
 
  CREATE TABLE dest (
     id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
     b VARCHAR(50)
  );
 
  INSERT INTO dest (b)
  SELECT a
    FROM source;

Then in table 'dest' on master we have
+----+------+
| id | b    |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
and on slave:
+----+------+
| id | b    |
+----+------+
|  2 | 1    |
|  3 | 2    |
+----+------+

I have this problem too.
v 4.0.13. not InnoDB but MyISAM, Linux.
example:

create table _test1 select 'a' as c;
insert _test1 values ('b');
create table _test2 
 (Seq SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY key (Seq)) 

master:
select * from _test2;
+-----+---+
| Seq | c |
+-----+---+
|   1 | a |
|   2 | b |
+-----+---+

slave:
+-----+---+
| Seq | c |
+-----+---+
|   2 | a |
|   3 | b |
+-----+---+

I saw this very same issue with 4.0.9 with Innodb. I know that Slashdot had to upgrade one of the slave's this weekend to get around this (they upgraded to 4.0.12).

*Could* be bug 136 which is fixed in 4.0.12.
Bug 490 (this thread's bug) was introduced in 4.0.13.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html