mysql_insert_id and last_insert_id wrong behavior

Question

I have this table

 CREATE TABLE IF NOT EXISTS `t5` (
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `a` (`a`,`b`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1  ;

a_b is a unique key.

And I have php code like this

 $db = DBFactory::getInstance();
 $db->selectDB('test');
 $db->query("insert into t5 (a, b) values(1, 1) on duplicate key update a=1, b=1");
 var_dump(mysql_insert_id());

 $cur = $db->query("select last_insert_id()");
 var_dump(mysql_fetch_assoc($cur));

By running this code twice, on my pc the result is 1st

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "1"
}

2nd

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "2"
}

You can see, both times the mysql_insert_id() return the same value "1", this is fine to me, because I want to know the real id after the insertion , but not the next auto_increment value.

But when I ran this code on another environment twice: 1st

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "1"
}

2nd

int(2)
array(1) {
  ["last_insert_id()"]=>
  string(1) "2"
}

As you can see the difference, the second time's result mysql_insert_id() return the same value as last_insert_id().

This result is horrible, but I don't know why. My codes run fine on both environment for about 3 months, and this never happened until today. can someone explain ? I did upgrade the second environment's PHP version to 5.3.8 about 30 days ago, and no other changes. Is this a bug?

update

I switch to the third mysql server(5.1.38-log), the second insert return int(0) array(1) { ["last_insert_id()"]=> string(1) "0" }

So I realized that the problem maybe is about mysql version.

At last, I changed table define to this one

DROP TABLE IF EXISTS `t5`;
CREATE TABLE IF NOT EXISTS `t5` (
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `t` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Also edit my script

$db = DBFactory::getInstance();
$db->selectDB('test');
$db->query("insert into t5 (a, b, t) values(1, 1, ".time().") on duplicate key update a=1, b=1, t=".time());
//$db->query("insert ignore into t5 (a, b) values(1, 1)");
var_dump(mysql_insert_id());

$cur = $db->query("select last_insert_id()");
var_dump(mysql_fetch_assoc($cur));

Different mysql server return the same mysql_insert_id but different last_insert_id()

5.0.24a-community-nt

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "2"
}

5.0.51a-log

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "2"
}

5.1.38-log

int(1)
array(1) {
  ["last_insert_id()"]=>
  string(1) "0"
}

Is there any system variable control this behavior? If someone know that would be greetful. If there is no solution, the only thing I can do is to force insertion like this to update some field with different value...

Mesothorium

Are you using the same schema on the same db instance? Is it possible in the second case the UNIQUE key isn't there for some reason? – Stirrup

Are these instances both pointing at the same MySQL database, or different ones? – Tefillin

In both databases after twice insert, there is only one row in the table, that's no doubt.. – Mesothorium

"Are these instances both pointing at the same MySQL database, or different ones?" different databases, my local mysql(on PC) is 5.0.24a-community-nt, and the other is 5.0.51a-log (on Linux) – Mesothorium

Thanks Simon's remind, I use my PC's php script to connect the mysql on linux and ran the code, and still got mysql_insert_id()=2, so that is an mysql bug? I have restart mysql service, but it still gives me the wrong result. – Mesothorium

last_insert_id is not thread safe. Are you doing something else with the same connection? – Interpretive

Answer 1

I think you are trying to use last_insert_id() is way that is not meant to be - in these case you are not inserting anything, so you should not trust the return value, either. From MySQL docs:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful.

However, it appears that there is a workaround for that (same doc) - manually setting last_insert_id:

However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

Pluvial