Bug #20657 wrong auto_increment value returns on "ON DUPLICATE KEY UPDATE"

• Submitted: 23 Jun 2006 12:31
• Modified: 23 Jun 2006 12:47
• Reporter: Michael Rack
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.11
• OS: GNU Linux
• CPU Architecture: Any

[23 Jun 2006 12:31] Michael Rack

Description:
Hello List, we have the following problem:

The sstructur of our table look like this:
AUTO as auto_increment, Name as unique and Telefon as standard varchar.

The auto_increment value return the wrong auto_increment number of the last inserted item.

How to repeat:

CREATE TABLE `test` (
  `AUTO` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `Name` char(25) NOT NULL,
  `Telefon` char(25) NOT NULL,
  PRIMARY KEY (`AUTO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`Name`, `Telefon`) VALUES ('Michi', '0000');
-> Affected Rows 1
-> Last InsertID 1

INSERT INTO `test` (`AUTO`, `Name`, `Telefon`) VALUES (1, 'Michi', '0001') ON DUPLICATE KEY UPDATE `Telefon`=VALUES(`Telefon`)
-> Affected Rows 2
-> Last InsertID 2

The last insertid is +1 but must be 1 and not 2 !!!

I hope there is a bugfix in the next version.

Thank you very much.

[23 Jun 2006 12:47] Guilhem Bichot

Hello!
The "affected rows 2" is normal, it helps the user know if the row was inserted or updated (here it was updated).
You'd want last_insert_id() to return 1, it's like in BUG#19243;
we are looking into this (per the documentation, last_insert_id() needn't return the id of an *updated* row, only of an *inserted* one, but I agree it can make sense to do so).
Until we know if we can change from 2 to 1, you can work around the problem
by using mysql_insert_id() (a function from the C API, which also has equivalents in Perl's DBI, PHP, ...).