From: dav...@mcpond.co.nz To: win...@lists.mysql.com Subject: last_insert_id() returns 0 (instead of correct value) for
auto_increment field

Description: See attached repeatable script. last_insert_id() returns zero , instead of the correct auto_increment value
to a select

How-To-Repeat: Create a table, with 1 autoinc field. This is a table for osCommerce , create
taken from oscommerce.sql. Add a couple of fields for my implementation, with alter table. Insert more than one row in a query Ask for value of last_insert_id() - get all 0's, to the number of inserts you
made. Same on mysql command line, mysqlcontrol centre, oledb driver, navicat.

Fix: Workaround select max(products_id) from products

Synopsis: last_insert_id() faulty Submitter-Id: 0 Originator: David McNeill Organization: McNeill Computers MySQL support: none Severity: non-critical Priority: low Category: mysqld Class: sw-bug Release: mysql-3.23.38

Exectutable: mysqld-nt Environment: Win2k Server SP3, Athlon 2000, 512Mb RAM, Promise RAID, 80Gb
disks. Test Server. System: Win2000 Compiler: VC++ 6.0 Architecture: i

# From osCommerce 1.71 DROP TABLE IF EXISTS products; CREATE TABLE products ( products_id int NOT NULL auto_increment, products_quantity int(4) NOT NULL, products_model varchar(12), products_image varchar(64), products_price decimal(15,4) NOT NULL, products_date_added datetime NOT NULL, products_last_modified datetime, products_date_available datetime, products_weight decimal(5,2) NOT NULL, products_status tinyint(1) NOT NULL, products_tax_class_id int NOT NULL, manufacturers_id int NULL, products_ordered int NOT NULL default '0', PRIMARY KEY (products_id), KEY idx_products_date_added (products_date_added) );

# Extra fields /* Add the primary foreign key field we track in Sybiz with */ alter table products add products_sybizinventryaccountno varchar(25);

/* Add a field to determine which products have just been checked, so know which
to delete */ alter table products add products_sybizupdaterunid int;

# Bif in some products ( auto made by Delphi). insert into products
(products_quantity,products_model,products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,products_sybizinventryaccountno,products_sybizupdaterunid)
values (0,'C5DZRDP-503','',250,'2003-04-27 18:59:07','2003-04-27
18:59:07','2003-04-27 18:59:07',0,1,1,0, 0,'MON-KTX-C5DZRDP-503',1); insert into products
(products_quantity,products_model,products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,products_sybizinventryaccountno,products_sybizupdaterunid)
values (0,' COLOUR MONITOR','',330,'2003-04-27 18:59:07','2003-04-27
18:59:07','2003-04-27 18:59:07',0,1,1,0, 0,'MON-17in- COLOUR MONITOR',1); insert into products
(products_quantity,products_model,products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,products_sybizinventryaccountno,products_sybizupdaterunid)
values (0,'105S21','',210,'2003-04-27 18:59:07','2003-04-27
18:59:07','2003-04-27 18:59:07',0,1,1,0, 0,'MON-PHILIPS-105S21',1); insert into products
(products_quantity,products_model,products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,products_sybizinventryaccountno,products_sybizupdaterunid)
values (0,'107S21','',355,'2003-04-27 18:59:07','2003-04-27
18:59:07','2003-04-27 18:59:07',0,1,1,12, 0,'MON-PHILIPS-107S21',1); insert into products
(products_quantity,products_model,products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,products_sybizinventryaccountno,products_sybizupdaterunid)
values (1,'107T21','',319,'2003-04-27 18:59:07','2003-04-27
18:59:07','2003-04-27 18:59:07',0,1,1,12, 0,'MON-PHILIPS-107T21',1);

# Want a bad result... select last_insert_id() from products # Get five lines of 0 instead of 1 line of 1