Bug #530 LAST_INSERT_ID() returns autoincrement of current table, not last table
Submitted: 28 May 2003 23:18 Modified: 29 May 2003 2:54
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:MySQL 4.0.13-max-debug OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[28 May 2003 23:18] [ name withheld ]
Description:
In a PHP script with MySQL 4.0.13-max-debug on Windows, I get the Autoincrement of the table I am currently inserting into, NOT the LAST_INSERT_ID() of the last table, maybe I do something wrong or maybe there is a bug in this version. Test carefully.  See manual section 8.1.12.3 How Can I Get the Unique ID for the Last Inserted Row?

My code: 
$insertSQL = sprintf("INSERT INTO multi_categories (id_art_mul) VALUES (LAST_INSERT_ID())";
$Result2 = $LindaConn->Execute($insertSQL) or die($LindaConn->ErrorMsg());

How to repeat:
On tables with structures:
CREATE TABLE `paintings` (
  `id_art` smallint(5) NOT NULL default '0',
  `name_art` varchar(100) default NULL,
  `height_art` decimal(4,0) default NULL,
  `width_art` decimal(4,0) default NULL,
  `size_units_art` set('inches','cm') default 'inches',
  `thumb_url_art` varchar(100) default NULL,
  `small_url_art` varchar(100) default NULL,
  `medium_url_art` varchar(100) default NULL,
  `large_url_art` varchar(100) default NULL,
  `detail_url_art` varchar(100) default NULL,
  `full_url_art` varchar(100) default NULL,
  `id_typ_art` tinyint(3) default NULL,
  `year_painted_art` smallint(5) default NULL,
  `month_painted_art` tinyint(5) default NULL,
  `original_price_art` decimal(5,0) default NULL,
  `glicee_price_art` decimal(5,0) default NULL,
  `sold_art` tinyint(4) default NULL,
  `for_sale_art` tinyint(4) default NULL,
  `id_dis_art` tinyint(3) default NULL,
  `id_sol_art` smallint(6) default NULL,
  PRIMARY KEY  (`id_art`),
  UNIQUE KEY `name_art_3` (`name_art`),
  KEY `id_whe_art` (`id_dis_art`),
  FULLTEXT KEY `name_art_2` (`name_art`)
) TYPE=MyISAM;

CREATE TABLE `multi_categories` (
  `id_mul` smallint(6) NOT NULL auto_increment,
  `id_art_mul` smallint(6) NOT NULL default '0',
  `id_cat_mul` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id_mul`),
  KEY `id_art_mul` (`id_art_mul`),
  KEY `id_cat_mul` (`id_cat_mul`)
) TYPE=MyISAM COMMENT='Lists what painting belong to what categories (many to many)' AUTO_INCREMENT=45 ;

# Host: localhost
# Generation Time: May 29, 2003 at 02:16 AM
# Server version: 4.0.13
# PHP Version: 4.3.2RC3
# Database : `lindaart`
# --------------------------------------------------------

$insertSQL = sprintf("INSERT INTO paintings (id_art, name_art, height_art, width_art, size_units_art, thumb_url_art, small_url_art, medium_url_art, large_url_art, detail_url_art, full_url_art, id_typ_art, year_painted_art, month_painted_art, original_price_art, glicee_price_art, sold_art, for_sale_art, id_dis_art, id_sol_art) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['hiddenNextPainting_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpName_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpHeight_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpWidth_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['radioSizeUnits'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpThumb_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpSmall_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpMedium_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpLarge_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpDetail_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpFull_url_art'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['InpType_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpYear_painted_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['ImpMonth_painted_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpOriginal_price_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpGlicee_price_art'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['radioSold'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['radioForSale'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['InpDisplayLocation'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['radioSold'], "int"));

  $Result1 = $LindaConn->Execute($insertSQL) or die($LindaConn->ErrorMsg());

  if ($HTTP_POST_VARS['InpCat1'] > "0")  {
  $insertSQL = sprintf("INSERT INTO multi_categories (id_art_mul, id_cat_mul) VALUES (LAST_INSERT_ID(), %s)",
                       GetSQLValueString($HTTP_POST_VARS['InpCat1'], "int"));
  
  $Result2 = $LindaConn->Execute($insertSQL) or die($LindaConn->ErrorMsg());
  }
}

Suggested fix:
LAST_INSERT_ID() should function as desribed in manual
[29 May 2003 2:54] Alexander Keremidarski
Please don't send us PHP code as it is not related to problem you describe.

Prepare test case consisting of plain SQL statements like this one:

create table t1 (id int unsigned auto_increment primary key);
create table t2 (a int, b int);
insert into t1 values(null);
insert into t2 values(1, last_insert_id());
 
mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.06 sec)
 
insert into t2 values(2, last_insert_id());
 
select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    1 |
+------+------+

Works as described in manual.
[1 Jul 2005 12:26] Espen Skogen
I've tried to replicate the test and got a different result:

create table t1 (id int unsigned auto_increment primary key);

create table t2 (a int, b int);

insert into t1 values(null);
insert into t2 values(1, last_insert_id());

select * from t1

id
--
1

select * from t2

a | b 
------
1 | 0 

So for some reason last_insert_id() is returning 0 even though last insert id was 1.

Any ideas? I saw this issue referred to as a bug somewhere else - Is there any patches for this?

Any help would be good.

Thanks.
E
[7 Dec 2006 20:50] Keith Young
The return of 0 suggests a connection failure.