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)