Problem with DBI::Multiplex

I'm trying to load balance reads across two MySQL database that have =
replication. The reads are working wonderfully. The inserts actually =
do insert. However, there are some table that use auto increment =
primary keys. I can't figure out how to actually get those particular =
keys now.

Peter Halliday
Excelsior Systems
http://www.excelsiorsystems.net
(Phone:) 607-936-2172
(Support:) 607-329-6905
(Fax:) 607-398-7928

Re: Problem with DBI::Multiplex

On Tue, May 09, 2006 at 11:07:38AM -0400, Peter Halliday wrote:
> I'm trying to load balance reads across two MySQL database that have replication. The reads are working wonderfully. The inserts actually do insert. However, there are some table that use auto increment primary keys. I can't figure out how to actually get those particular keys now.

You could patch DBI::Multiplex so the last_insert_id method uses the same dbh as the previous operation.

Patches welcome.

Tim.

RE: Problem with DBI::Multiplex

I actually got the last insert ID working. However, not sure why, but blobs
and text are screwed up. We have a couple of fields where we stored latex
which is faxes to be sent. The latex is so screwed up the documents were
lost. We did some testing and whenever blobs and text were used we had
formatting issue like that.

Peter Halliday
Excelsior Systems
http://www.excelsiorsystems.net
(Phone:) 607-936-2172
(Support:) 607-329-6905
(Fax:) 607-398-7928


> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Thursday, May 11, 2006 7:44 AM
> To: Peter Halliday
> Cc: dbi-users@perl.org
> Subject: Re: Problem with DBI::Multiplex
>
> On Tue, May 09, 2006 at 11:07:38AM -0400, Peter Halliday wrote:
> > I'm trying to load balance reads across two MySQL database
> that have replication. The reads are working wonderfully.
> The inserts actually do insert. However, there are some
> table that use auto increment primary keys. I can't figure
> out how to actually get those particular keys now.
>
> You could patch DBI::Multiplex so the last_insert_id method
> uses the same dbh as the previous operation.
>
> Patches welcome.
>
> Tim.
>

Re: Problem with DBI::Multiplex

On Thu, May 25, 2006 at 04:03:39PM -0400, Peter Halliday wrote:
> Tim and Thomas,
>
> Not sure why this is the case, but I noticed that doing an insert with
> an execute via DBD::Multiplex didn't work when using text that
> contained latex for example. But a do with the same did work.

What kind of "didn't work"? An error? If not, could you enable tracing
and try to see what happening that way.

> After much work I found that calling execute with @_ (even though it was
> empty) caused the error. So I added logic to not call it with @_.

So $sth->execute(@_); "didn't work",
but
my @tmp = @_;
$sth->execute(@tmp);
did?

If so, that seems very odd. Can you come up with a small self-contained
test case?

Tim.

> Peter Halliday
> Excelsior Systems
> http://www.excelsiorsystems.net
> (Phone:) 607-936-2172
> (Support:) 607-329-6905
> (Fax:) 607-398-7928
>
> > -----Original Message-----
> > From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> > Sent: Thursday, May 11, 2006 7:44 AM
> > To: Peter Halliday
> > Cc: dbi-users@perl.org
> > Subject: Re: Problem with DBI::Multiplex
> >
> > On Tue, May 09, 2006 at 11:07:38AM -0400, Peter Halliday wrote:
> > > I'm trying to load balance reads across two MySQL database that have
> > replication. The reads are working wonderfully. The inserts actually do
> > insert. However, there are some table that use auto increment primary
> > keys. I can't figure out how to actually get those particular keys now.
> >
> > You could patch DBI::Multiplex so the last_insert_id method uses the same
> > dbh as the previous operation.
> >
> > Patches welcome.
> >
> > Tim.
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
> >
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
>
>

RE: Problem with DBI::Multiplex

> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Friday, May 26, 2006 3:52 AM
> To: Peter Halliday
> Cc: 'Tim Bunce'; dbi-users@perl.org; tkishel+perl@gmail.com
> Subject: Re: Problem with DBI::Multiplex
>=20
> On Thu, May 25, 2006 at 04:03:39PM -0400, Peter Halliday wrote:
> > Tim and Thomas,
> >
> > Not sure why this is the case, but I noticed that doing an insert =
with
> > an execute via DBD::Multiplex didn't work when using text that
> > contained latex for example. But a do with the same did work.
>=20
> What kind of "didn't work"? An error? If not, could you enable tracing
> and try to see what happening that way.
>=20

Everything was perfect in the application except two fields. One is a =
text field that stores latex. The other is a blob field that stored =
frozen data structure from Storable for easy reloading later. We are =
running 5.0 of MySQL as well. When writing to those specific fields =
(text and blob) for those specific scenarios the content of the fields =
are corrupted. In the case of the latex, it looks like the backslashes =
in the content were being read as escapes in perl. I printed out =
$sth->{Statement} after the prepare, but before the execute and =
everything looked fine. I turned on tracing, but didn't see anything =
that stuck out that would lead to a solution. However, it still got rid =
of the backslashes the character after them. The weirdness was it =
worked in the case of the do function. The only difference in the two =
functions was that there was no parameter left in @_ for execute, but =
there was in do. So I assumed that pass @_ to execute might be the =
problem I created logic to=20

If(@_) {
Pass it to the function
} else {
Pass nothing
}

And when I did that the code worked. It blows my mind why. My guess was =
that it was a DBD::mysql error. I haven't looked at the C code for the =
execute yet. However, my guess is that execute(@_) is seen different as =
execute() to the DBD::mysql even wth @_ is empty.


> > After much work I found that calling execute with @_ (even though it =
was
> > empty) caused the error. So I added logic to not call it with @_.
>=20
> So $sth->execute(@_); "didn't work",
> but
> my @tmp =3D @_;
> $sth->execute(@tmp);
> did?
>=20
> If so, that seems very odd. Can you come up with a small =
self-contained
> test case?
>=20
> Tim.
>=20
> > Peter Halliday
> > Excelsior Systems
> > http://www.excelsiorsystems.net
> > (Phone:) 607-936-2172
> > (Support:) 607-329-6905
> > (Fax:) 607-398-7928
> >
> > > -----Original Message-----
> > > From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> > > Sent: Thursday, May 11, 2006 7:44 AM
> > > To: Peter Halliday
> > > Cc: dbi-users@perl.org
> > > Subject: Re: Problem with DBI::Multiplex
> > >
> > > On Tue, May 09, 2006 at 11:07:38AM -0400, Peter Halliday wrote:
> > > > I'm trying to load balance reads across two MySQL database that =
have
> > > replication. The reads are working wonderfully. The inserts =
actually
> do
> > > insert. However, there are some table that use auto increment =
primary
> > > keys. I can't figure out how to actually get those particular =
keys
> now.
> > >
> > > You could patch DBI::Multiplex so the last_insert_id method uses =
the
> same
> > > dbh as the previous operation.
> > >
> > > Patches welcome.
> > >
> > > Tim.
> > >
> > > --
> > > No virus found in this incoming message.
> > > Checked by AVG Free Edition.
> > > Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date:
> 5/24/2006
> > >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: =
5/24/2006
> >
> >
>=20
>=20
>=20
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.1/348 - Release Date: =
5/25/2006
>=20

--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.1/348 - Release Date: 5/25/2006
=20

RE: Problem with DBI::Multiplex

> So $sth->execute(@_); "didn't work",
> but
> my @tmp = @_;
> $sth->execute(@tmp);
> did?


It was more like this

$sth->execute(@_) didn't work

$sth->execute() did.

Peter Halliday
Excelsior Systems
http://www.excelsiorsystems.net
(Phone:) 607-936-2172
(Support:) 607-329-6905
(Fax:) 607-398-7928




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.1/348 - Release Date: 5/25/2006

RE: Problem with DBI::Multiplex

This is embarrassing, the reason it didn't work was as designed. The =
content needed to be escaped by DBD::mysql. The function do auto =
escapes things, as well as passing args as parameters to execute. =
However, If you create the SQL without the placeholders, but don't =
escape the content then this is what happened.

Peter Halliday
Excelsior Systems
http://www.excelsiorsystems.net
(Phone:) 607-936-2172
(Support:) 607-329-6905
(Fax:) 607-398-7928
> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Friday, May 26, 2006 3:52 AM
> To: Peter Halliday
> Cc: 'Tim Bunce'; dbi-users@perl.org; tkishel+perl@gmail.com
> Subject: Re: Problem with DBI::Multiplex
>=20
> On Thu, May 25, 2006 at 04:03:39PM -0400, Peter Halliday wrote:
> > Tim and Thomas,
> >
> > Not sure why this is the case, but I noticed that doing an insert =
with
> > an execute via DBD::Multiplex didn't work when using text that
> > contained latex for example. But a do with the same did work.
>=20
> What kind of "didn't work"? An error? If not, could you enable tracing
> and try to see what happening that way.
>=20
> > After much work I found that calling execute with @_ (even though it =
was
> > empty) caused the error. So I added logic to not call it with @_.
>=20
> So $sth->execute(@_); "didn't work",
> but
> my @tmp =3D @_;
> $sth->execute(@tmp);
> did?
>=20
> If so, that seems very odd. Can you come up with a small =
self-contained
> test case?
>=20
> Tim.
>=20
> > Peter Halliday
> > Excelsior Systems
> > http://www.excelsiorsystems.net
> > (Phone:) 607-936-2172
> > (Support:) 607-329-6905
> > (Fax:) 607-398-7928
> >
> > > -----Original Message-----
> > > From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> > > Sent: Thursday, May 11, 2006 7:44 AM
> > > To: Peter Halliday
> > > Cc: dbi-users@perl.org
> > > Subject: Re: Problem with DBI::Multiplex
> > >
> > > On Tue, May 09, 2006 at 11:07:38AM -0400, Peter Halliday wrote:
> > > > I'm trying to load balance reads across two MySQL database that =
have
> > > replication. The reads are working wonderfully. The inserts =
actually
> do
> > > insert. However, there are some table that use auto increment =
primary
> > > keys. I can't figure out how to actually get those particular =
keys
> now.
> > >
> > > You could patch DBI::Multiplex so the last_insert_id method uses =
the
> same
> > > dbh as the previous operation.
> > >
> > > Patches welcome.
> > >
> > > Tim.
> > >
> > > --
> > > No virus found in this incoming message.
> > > Checked by AVG Free Edition.
> > > Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date:
> 5/24/2006
> > >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: =
5/24/2006
> >
> >
>=20
>=20
>=20
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.1/348 - Release Date: =
5/25/2006
>=20

--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.2/349 - Release Date: 5/26/2006
=20