On May 8, 3:40*pm, alaric <mwisne...@gmail.com> wrote:
> Hi,
>
> I'm writing to a MySQL database (version 5.0.51b on solaris10 built
> from source) using multiple Java threads and, after successfully
> inserting a row in to a table, run *"select *max(rowid) from table" to
> get the auto incr id (note, the program i'm using to insert does not
> work with the last_insert_id() because when I get control back the
> session that inserted the row is closed).
>
> Anyway, for the most part, doing the "select max()" works but
> sometimes zero is returned *but that is clearly wrong. *I'm wondering
> if this is a known issue with MySQL, a bug in the code I'm using to do
> the inserts,
> or a layer 8 problem (user error). *Any help would be greatly
> appreciated.

This was an error in older (3.x) versions of MySQL (select max() would
sometimes return 0 or null), so its quite possible there's a bug in
there someplace. I'm not aware of an open bug (at present) that
matches your scenario though.

On another note, your method of doing things might not give you the
result you expect. The last_insert_id() function returns the last
auto_increment value in the current session. The key here is that the
value is session specific. The 'select max()' function will select
the highest value in the table. That means you could run into this
situation:

Create table a (id int unsigned auto_increment primary key);

Session A: Insert into a (id) values (null); // Inserted value is 1
Session B: Insert into a (id) values (null); // Inserted value is 2
Session A: select max(id) from a; // Returns 2, even though the value
that was inserted in session A was 1.
Session A: select last_insert_id(); // Returns 1, since the last
auto_increment value generated in the session was 1

While in many cases your code will seem to work, you'll find that this
race condition causes it to fail when multiple inserts are done near
the same time.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about Expert MySQL Training Delivered Worldwide.