select max(column) returns 0

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.

Regards,
-MW

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.

On Thu, 8 May 2008 12:40:55 -0700 (PDT), alaric
<mwisner69@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).

Then the structure of the program is incorrect.
max(rowid) will never be reliable, some other
session/hread may already have inserted yet another
row.
last_insert_id() is the only way to get the last
inserted rowid within the current connection reliably.

>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,

I doubt it.

>a bug in the code I'm using to do the inserts,

Hm, you said "after successfully inserting a row".

>or a layer 8 problem (user error).

Constraints and the application should protect the
database against user errors at all times.

>Any help would be greatly appreciated.

You could define an AFTER INSERT trigger which records
last_insert_id() in another table, with a session
identifier as its primary key.

replace into anothertable (session,lastrowid)
values $sessionid, last_insert_id();

Then use
select lastrowid
from anothertable
where session = $sessionid;

>Regards,
>-MW

Good luck.
--
( Kees
)
c[_] Work like you don't need the money,
Love like you've never been hurt,
Dance like nobody's watching. (#426)