AutoCommit and DBI::Proxy driver

------=_Part_115533_22878485.1157139651003
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I have the mySQL server, to which I am connecting through DBI::Proxy. When I
do '$dbh->begin_work' and then call '$dbh->commit' everything *seems* to
work (at least, I don't see any errors or warnings). According to
documentation, a call to '$dbh->commit' after '$dbh->begin_work' turns
'AutoCommit' back on.
The problems start when I try to set '$dbh-{AutoCommit} =0' (or call
$dbh->begin_work' ) for the SECOND time (after the first call to
'$dbh->commit'). The error says that AutoCommit is not supported by my
driver.

When I connect to mySQL server directly (using 'mysql' driver) everything
works, so, given that my database supports AutoCommit, I suspect that the
problem is in DBI::Proxy driver.

Any ideas ?

Thanks in advance for reply,
Artem.

------=_Part_115533_22878485.1157139651003--

Re: AutoCommit and DBI::Proxy driver

> The problems start when I try to set '$dbh-{AutoCommit} =0' (or call
> $dbh->begin_work' ) for the SECOND time (after the first call to
> '$dbh->commit'). The error says that AutoCommit is not supported by my
> driver.

I've been bitten by this in the past too, and understand it's a bug in
DBD::Proxy (at least it was in 0.2004; not sure it it's fixed in the
latest version).

You can work around it by setting $dbh->{AutoCommit} = 1 after your commit
or rollback.

best regards, John.

Re: AutoCommit and DBI::Proxy driver

On Mon, Sep 04, 2006 at 09:36:30AM +0100, John Escott wrote:
> > The problems start when I try to set '$dbh-{AutoCommit} =0' (or call
> > $dbh->begin_work' ) for the SECOND time (after the first call to
> > '$dbh->commit'). The error says that AutoCommit is not supported by my
> > driver.
>
> I've been bitten by this in the past too, and understand it's a bug in
> DBD::Proxy (at least it was in 0.2004; not sure it it's fixed in the
> latest version).

I don't recall it getting fixed.

I'd appreciate it, John, if you could file a bug report on rt.cpan.org

Even better would be a patch to the t/80proxy.t test script that
demonstrates the problem.

Tim.

> You can work around it by setting $dbh->{AutoCommit} = 1 after your commit
> or rollback.

Re: AutoCommit and DBI::Proxy driver

Tim Bunce wrote on 04/09/2006 12:02:53:

> I'd appreciate it, John, if you could file a bug report on rt.cpan.org

Ok, Tim, done. Ticket #21320.

best regards, John.

Re: AutoCommit and DBI::Proxy driver

On Mon, Sep 04, 2006 at 02:27:38PM +0100, John Escott wrote:
> Tim Bunce wrote on 04/09/2006 12:02:53:
>
> > I'd appreciate it, John, if you could file a bug report on rt.cpan.org
>
> Ok, Tim, done. Ticket #21320.

Finally got to look at this this evening. Had to dig into some dusty
corners of the DBI and DBD::Proxy and DBI::ProxyServer. Turned out the
central issue was the AUTOLOAD in DBD::Proxy.

I've fixed it (passes your tests - thanks for those) and in doing so
learnt more about how the proxy works (I didn't write the internals and
have never paid much attention to them.)

Can you try the version in the svn repository. For how, see
http://search.cpan.org/~timb/DBI-1.52/DBI.pm#CONTRIBUTING

Let me know how it goes.

Also, please setup a little test script that uses selectall_arrayref
to fetch from the server. Time how long it takes with a select that
fetches, say, a few thousand rows. Then add this like to the code:
sub DBD::Proxy::db::selectall_arrayref;
are time it again.

Thanks!

Tim.

Re: AutoCommit and DBI::Proxy driver

Hi Tim,

Sorry it's been a while, I was on holiday.

Tim Bunce wrote on 26/09/2006 23:36:06:

> Can you try the version in the svn repository. For how, see
> http://search.cpan.org/~timb/DBI-1.52/DBI.pm#CONTRIBUTING
>
> Let me know how it goes.

I can confirm that AutoCommit now switches on and off correctly, so
multiple transactions (begin_work/commits) now work as expected.

>
> Also, please setup a little test script that uses selectall_arrayref
> to fetch from the server. Time how long it takes with a select that
> fetches, say, a few thousand rows. Then add this like to the code:
> sub DBD::Proxy::db::selectall_arrayref;
> are time it again.

I tried that but when I added the sub, $dbh->selectall_arrayref returned
an undef value (quite quickly: 0.26769s vs 8.144393s without). I had
RaiseError on but no error was reported.

As a side issue, I started to wonder how large result sets would get on
with RPC::PlServer's maxmessage option, since I assume the intention is to
return the results in 1 message. (maxmessage defaults to 64K, but the pod
doesn't mention if you can disable it completely -- I've been bitten by it
before).

>
> Thanks!
>
> Tim.

No, my thanks to you :-)

best regards, John.