List:MySQL on Win32« Previous MessageNext Message »
From:Paul DuBois Date:April 27 2003 5:19pm
Subject:Re: last_insert_id() returns 0 (instead of correct value) for auto_increment field
View as plain text  
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

Thread
last_insert_id() returns 0 (instead of correct value) for auto_increment fieldDavid at McNeill Computers27 Apr
  • Re: last_insert_id() returns 0 (instead of correct value) for auto_increment fieldMark Matthews27 Apr
    • Re: last_insert_id() returns 0 (instead of correct value) for auto_increment fieldPaul DuBois27 Apr
      • I have a 'simple' questionCharles Mabbott28 Apr
        • RE: I have a 'simple' questionJohn Hopkins28 Apr
      • R: last_insert_id() returns 0 (instead of correct value) for auto_increment fieldtrashMan29 Apr