Apache::DBI and mySQL LAST_INSERT_ID

This is a multi-part message in MIME format.

------=_NextPart_000_007B_01C9572A.558C22A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi

The mySQL documentation for LAST_INSERT_ID says that it returns the last =
auto-incremented field value for 'that client'.

I am not sure what the client is in a mod_perl situation with =
Apache::DBI?

I have an Apache child process which uses one open database connection =
for its lifetime. If 2 page requests use the same child process and thus =
the same database connection does the mySQL server not see this as the =
same client? If so - this seems to suggest that I can't rely on =
LAST_INSERT_ID ...

Unless (and I don't understand much about Apache) each the process is =
used to serve one request completely and then the next. If this is the =
case then it seems that LAST_INSERT_ID would be reliable.

I hope that makes some kind of sense and thanks for any help

Justin Wyllie

------=_NextPart_000_007B_01C9572A.558C22A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Apache::DBI and mySQL LAST_INSERT_ID

On Fri, Dec 5, 2008 at 5:39 PM, Justin Wyllie wrote:
> The mySQL documentation for LAST_INSERT_ID says that it returns the last
> auto-incremented field value for 'that client'.
>
> I am not sure what the client is in a mod_perl situation with Apache::DBI?

The current connection.

> I have an Apache child process which uses one open database connection for
> its lifetime. If 2 page requests use the same child process and thus the
> same database connection does the mySQL server not see this as the same
> client? If so - this seems to suggest that I can't rely on LAST_INSERT_ID

You can rely on LAST_INSERT_ID. It is not affected by Apache::DBI.

> Unless (and I don't understand much about Apache) each the process is used
> to serve one request completely and then the next. If this is the case then
> it seems that LAST_INSERT_ID would be reliable.

That is correct.

- Perrin

Re: Apache::DBI and mySQL LAST_INSERT_ID

Perrin Harkins wrote:
> On Fri, Dec 5, 2008 at 5:39 PM, Justin Wyllie wrote:
>
>> The mySQL documentation for LAST_INSERT_ID says that it returns the last
>> auto-incremented field value for 'that client'.
>>
>> I am not sure what the client is in a mod_perl situation with Apache::DBI?
>>
>
> The current connection.
>
>
>> I have an Apache child process which uses one open database connection for
>> its lifetime. If 2 page requests use the same child process and thus the
>> same database connection does the mySQL server not see this as the same
>> client? If so - this seems to suggest that I can't rely on LAST_INSERT_ID
>>
>
> You can rely on LAST_INSERT_ID. It is not affected by Apache::DBI.
>
yes, but, do you really want to gamble that the second request is
handled by the same child process? and what if another request comes in
between? I'd say this is only reliable during a keepalive session, but
it really depends on what you're trying to do.

Enno

Re: Apache::DBI and mySQL LAST_INSERT_ID

On Wed, Dec 10, 2008 at 1:22 PM, Enno wrote:
> yes, but, do you really want to gamble that the second request is handled by
> the same child process? and what if another request comes in between?

I think you're misunderstanding his question. As I read it, he was
worried that multiple requests would be handled by the same connection
_at the same time_, making it impossible to rely on last_insert_id.
Since each apache child or thread handles requests sequentially, that
would never happen, and he doesn't need to be concerned about it.

You're correct that it would be totally wrong to try to use
last_insert_id for a value that was inserted on a previous request.
That will not work reliably.

- Perrin

Re: Apache::DBI and mySQL LAST_INSERT_ID

Hi Guys

Thanks for all the help.

I have some code which inserts something into a database and immediately
(the next line) executes another sql statement with SELECT
LAST_INSERT_ID() - so it is not a previous one from somewhere else in the
code.

My concern was that because the actual connection is always the same in this
child process that I couldn't rely on LAST_INSERT_ID. However if Apache uses
child processes sequentially - i.e there is no danger of that child process
being used simultaneously by two requests I think I should be ok. I think
this is what Perrin was confirming?

Thanks

Justin




> On Wed, Dec 10, 2008 at 1:22 PM, Enno wrote:
>> yes, but, do you really want to gamble that the second request is handled
>> by
>> the same child process? and what if another request comes in between?
>
> I think you're misunderstanding his question. As I read it, he was
> worried that multiple requests would be handled by the same connection
> _at the same time_, making it impossible to rely on last_insert_id.
> Since each apache child or thread handles requests sequentially, that
> would never happen, and he doesn't need to be concerned about it.
>
> You're correct that it would be totally wrong to try to use
> last_insert_id for a value that was inserted on a previous request.
> That will not work reliably.
>
> - Perrin

Re: Apache::DBI and mySQL LAST_INSERT_ID

Post removed (X-No-Archive: yes)

Re: Apache::DBI and mySQL LAST_INSERT_ID

On Wed, Dec 10, 2008 at 2:27 PM, Justin Wyllie wrote:
> However if Apache uses
> child processes sequentially - i.e there is no danger of that child process
> being used simultaneously by two requests I think I should be ok. I think
> this is what Perrin was confirming?

Yes, that's right. And Todd's advice about avoiding another SELECT is good.

- Perrin