On Fri, Jul 28, 2006 at 03:46:20PM +0100, Martin J. Evans wrote:
>
> On 27-Jul-2006 Tim Bunce wrote:
> > 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.
>
> I can reproduce here so I'll try and simplify it.
>
> <rant>
>
> To be honest, I am starting to get a bit frustrated so perhaps I'm losing some
> vision - appologies. I am writing code that needs to work with around 3-6 DBDs
> and seem to be hitting a lot of bugs and incompatibilities (almost on a daily
> basis). DBI->last_insert_id is one I struggled to get working in all of them
> so
> resorted to other means (I never got last_insert_id method working
> in Oracle and had to resort to a trigger and a global package variable,
> similarly with DB2 where I use identity_val_local()).
Free free to start a separate thread about that.
> Todays problem is:
>
> use DBI;
> #my $h = DBI->connect("dbi:Oracle:XE", "xxx", "yyy");
> $h = DBI->connect("dbi:mysql:xxx", "xxx","yyy");
> for (my $n = 0; $n < 2; $n++) {
> $h->do(q/insert into mje (b) values('a')/);
> #$sth = $h->prepare_cached("select LastInsertID() from dual");
> $sth = $h->prepare_cached("select LAST_INSERT_ID()");
> $sth->execute;
> my $r = $sth->fetchall_arrayref;
> }
>
> which returns:
>
> prepare_cached(select LAST_INSERT_ID()) statement handle
> DBI::st=HASH(0x9ba8114) still Active at -e line 8
>
> when run for mysql but works fine for Oracle (see commented out lines). Yes,
> you can fix it by putting a finish in (or adding if_active arg to
> prepare_cached) but why are they different. I'd guess its because mysql is not
> calling dbd_st_finish when all the rows are consumed.
Sure looks like a DBD::mysql bug. fetchall_arrayref should always leave
the sth inactive.
> Not really asking for any help here, just using it as an illustration.
>
> </rant>
>
> > 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.
>
> Connecting to a remote mysql server over a 100M network I could not see any
> difference in using mysql_insertid and select LAST_INSERT_ID(), I remember
> looking into that:
If "select LAST_INSERT_ID()" is broken so it always returns the
same value then perhaps that contributes to the similar performance.
(More generally, as systems and networks get faster then things that
are less efficient are less noticeably inefficient. There are fewer
situations in which the inefficiency is relevant.)
Tim.
