Re: last_insert_id() returns 0 (instead of correct value) for auto_increment field

#1

I am also having an issue with LAST_INSERT_ID() returning 0.

The script :

DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES(NULL);
SELECT LAST_INSERT_ID() FROM t;
INSERT INTO t VALUES(NULL),(NULL),(NULL);
SELECT LAST_INSERT_ID() FROM t;

returns

0
0
0
0

In the table t there are rows contain 1,2,3,4.

We are using V 4.1.7-nt on Windows Server 2003 & InnoDB as the table
handler.

regards,
Tommy Doherty

Paul DuBois wrote:

Quote:

At 7:33 -0500 4/27/03, Mark Matthews wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

David at McNeill Computers wrote:

From: davemc (AT) mcpond (DOT) co.nz
To: win32 (AT) lists (DOT) 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
[snip]

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

It's just 'SELECT last_insert_id()', no 'FROM' clause needed. It
returns
the last AUTO_INCREMENT value generated for a query on the current
connection. See
http://www.mysql.com/doc/en/Miscella...functions.html
for the full description on how to use it.

-Mark

It's true that you normally use no FROM, but if there *is* a FROM,
LAST_INSERT_ID() should return the AUTO_INCREMENT value for each for
row.

If it returns 0 *on the same connection*, then it's still a bug.

David, are you issuing the query for LAST_INSERT_ID() on the same
connection, or do you generate the AUTO_INCREMENT value, then close
the connection, then retrieve LAST_INSERT_ID() on a different
connection?
If it's the latter, then that's your problem.

Try this script:

DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES(NULL);
SELECT LAST_INSERT_ID() FROM t;
INSERT INTO t VALUES(NULL),(NULL),(NULL);
SELECT LAST_INSERT_ID() FROM t;

The result should be

1
2
2
2
2

If you don't get that, there is a problem; please file a bug report
(bugs.mysql.com), and provide information about your version of
MySQL, and give a repeatable test case. Thanks.

- --
For technical support contracts, visit
https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <mark (AT) mysql (DOT) com
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager -
J2EE/Windows
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=m...ie.nctu.edu.tw

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: last_insert_id() returns 0 (instead of correct value) for auto_increment field

mailing.database.mysql-win32 mailing.database.mysql-win32

Re: last_insert_id() returns 0 (instead of correct value) for auto_increment field - 12-17-2004 , 10:10 AM