At 7:33 -0500 4/27/03, Mark Matthews wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>David at McNeill Computers wrote:
>
>> From: davemc@stripped
>> To: win32@stripped
>> 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/Miscellaneous_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@stripped>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows
> /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
> <___/ www.mysql.com
