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...

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;