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

[24 May 2003 7:09] Guilhem Bichot
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    |
+----+------+
[19 Jun 2003 1:25] Artem V. Ryabov
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 |
+-----+---+
[30 Jun 2003 15:35] Brian Aker
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).
[1 Jul 2003 5:50] Guilhem Bichot
*Could* be bug 136 which is fixed in 4.0.12.
Bug 490 (this thread's bug) was introduced in 4.0.13.
[1 Jul 2003 6:14] Guilhem Bichot
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