On Wed, Jul 26, 2006 at 10:43:34AM +0100, Martin J. Evans wrote:
>
> On 25-Jul-2006 Tim Bunce wrote:
> > Use DBI::Profile to see where the time is being spent.
>
> I got this working and could not see anything taking longer. However, on
> further investigation of my logs I have found the reason why doing a
> prepare_cached on "select LAST_INSERT_ID()" to mysql is a BAD thing to do.
> It always returns the first id ever inserted. i.e.
>
> insert into table1 values (val)
> select LAST_INSERT_ID() returns 1
> insert into table1 values (val)
> select LAST_INSERT_ID() returns 1 (instead of 2)
> etc
>
> I see there is a warning about how you can get into trouble with
> prepare_cached
> in the DBI pod. Perhaps it would be worth noting that there is also some SQL
> you don't want to prepare_cached as it won't work properly - like this
> example.
You're making a guess about the cause of the problem. You might be
right, but if so it's a bug in DBD::mysql.
But for mysql you'll find it much faster to just use the $dbh->{mysql_insertid}
attribute after each insert and avoid the select entirely.
Tim.
