MySQL Bugs: #530: LAST_INSERT_ID() returns autoincrement of current table, not last table

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

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

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.

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

The return of 0 suggests a connection failure.