Re: autocommit property broken with MySQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Aug 10, 2005, at 05:42, Jacob Smullyan wrote:

> On Tue, Aug 09, 2005 at 10:28:27PM -0700, Matthew Bogosian wrote:
>> I can't log this as a bug on BerliOS (since I can't seem to create an
>> account), so I thought I'd bring this up here instead.
>
> That's odd.  If you send me some detail of the problem I can bugify 
> them.

Actually, it was just a matter of waiting the 12 hours or so for the 
registration confirmation e-mail. My family will attest that patience 
is a virtue not often expressed by me.... :-)

>> I have been using PyDO-2.0b1 with MySQL and have been playing around
>> with InnoDB tables and ACID transactions (i.e., with commit, rollback,
>> etc.). I am using PyDO with MySQLdb 1.0.1.
>
> Oy, mysql autocommit is indeed broken.  In MySQLdb 1.2, there is an
> autocommit method (not property!), which caused the one lame
> autocommit test that wasn't disabled for mysql to pass. The
> autocommit() method only sets autocommit; it doesn't report on its
> value.

That seems kind of odd for that implementation, but not that 
surprising. I've actually filed a few bugs and patches with MySQLdb in 
the past (it's come a long way since I started using it). 
Unfortunately, I'm usually one or two revisions behind since I use the 
fink version (there's usually a large delay between the time a new 
version comes out and a package is available in fink).

> As for the base class autocommit implementation, it probably shouldn't
> be there at all, since it needs to be overridden just about
> everywhere.  Caching the value is slightly tricky, as the real
> connection can disappear and be replaced by another (by switching to a
> different thread - connections are thread-local - or by other means).
> So autocommit values, if cached, need to be cached keyed by
> id(connection) or something like that.

Hmmm...this *is* tricky. My *guess* is that developers who take 
advantage of pooled connections (or connections which can be shared 
across threads/processes/etc.) are probably likely to want a single 
autocommit behavior to be maintained across all of them (I could be 
wrong, but at least that seems like it would be the most popular use 
case).

> So -- the whole autocommit business needs some TLC.  (I see now that
> the autocommit value is tracked incorrectly as a private attribute in
> the psycopg driver.) Thanks for the patch and the detailed analysis,
> which is helpful; I'll incorporate it or something similar, hopefully
> later today.

I'm wondering if perhaps (like you said) autocommit should be there at 
all? I have not used that many different databases (only Oracle, 
PostgreSQL and MySQL). For ACID support (I believe) all of those DBs 
have a non SQL92-compliant mechanism for demarcating the beginning of a 
transaction (in most cases with the BEGIN statement). Would it be 
easier to remove autocommit in favor of a begin() method which would be 
a no-op in some cases?

On second thought, this may not be such a good idea, since, although 
popular, the BEGIN statement is *not* standard, and I don't have a 
handle on the autocommit behaviors of other DBs (e.g., SAP/MaxDB 
<http://www.mysql.com/products/maxdb/>, etc.).

It seems like this is a particularly hairy issue since there doesn't 
appear to be any standard regarding its implementation. MySQL makes 
things even more confusing since non InnoDB tables behave as if 
autocommit was on even if it's not. I guess that's a detail which 
should be taken into account by the database designer (i.e., if you 
depend on transactionality and are design an application for use with 
several database back-ends, then you should probably be aware of the 
idiosyncrasies of each).

One solution might be to get rid of autocommit and add something to the 
alias definition which would be zero or more statements which would be 
executed on every new connection immediately after it was created. For 
example:

> conn_args = \
> {
>     'db': 'mydb',
>     'user': 'myuser',
>     'passwd': 'mypass',
> }
> init_sql = \
> (
>     'SET AUTOCOMMIT = 0',
>     ...
> )
> pydo.dbi.initAlias('mydb', 'mysql', conn_args, initSql = init_sql)

That way, one could do any appropriate initialization without having to 
sub-class an existing PyDO DBI implementation. New connections which 
share the alias would all have the same initialization. It doesn't 
address the abstract the want/need to see if a particular connection 
has the autocommit attribute set, but I'm wondering if that's such a 
bad thing, since it seems to be such a non standard thing anyway.

Sorry for the length of my communique...I'm just typing as I think of 
things. Would you like me to file a bug/issue regarding this on 
BerliOS?

By the way, thanks for all the great work. PyDO has grown into a really 
cool library since I first saw it as part of SkunkWeb. Just out of 
curiosity, are you planning to break PyDO out as a separate product 
with its own development schedule (i.e., not ship it with SkunkWeb)? 
I'm just curious since there seems to be natural division there (and 
since I also use SKunkWeb)....

	-- Matt

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)

iD8DBQFC+kJEnLpDzL5I7l8RAhzeAJ9lnskxkJ8t1da1ESQIvBCO/PoV1QCgh5Np
l0dfZ88C/h5wH629q3tCCt4=
=Io2h
-----END PGP SIGNATURE-----



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf