Re: autocommit property broken with MySQL

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.

> 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.

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.

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.

js


> 
> I am trying to use the autocommit property, but I've noticed that it 
> doesn't work with this setup:
> 
> >>>> import MySQLdb.constants.CLIENT
> >>>> import pydo
> >>>> pydo.dbi.initAlias('my_test', 'mysql', { 'db': 'my_test', 'user': 
> >'my_test', 'passwd': 'my_test', 'client_flag': 
> >MySQLdb.constants.CLIENT.FOUND_ROWS })
> >>>> db = pydo.dbi.getConnection('my_test')
> >>>> db.autocommit
> >Traceback (most recent call last):
> >  File "<stdin>", line 1, in ?
> >  File 
> >"/Users/matt/Documents/dev/solus/dev/trunk/src/lib/py/pydo/dbi.py", 
> >line 40, in fget
> >    return self.conn.autocommit
> >AttributeError: 'Connection' object has no attribute 'autocommit'
> 
> Setting the property before attempting to get its value avoids the 
> above exception, but has no affect on the underlying connection.
> 
> I'm proposing that the pydo.drivers.mysqlconn.MysqlDBI class be amended 
> to include the following (overriding) definition (I've tried to stay 
> true to form of the original code):
> 
> >class MysqlDBI(DBIBase):
> >
> >    ...
> >
> >    def autocommit():
> >        def fget(self):
> >            ac_row = self.execute('SELECT @@SESSION.AUTOCOMMIT')
> >            ac_val = ac_row[0]['AUTOCOMMIT']
> >            return bool(ac_val)
> >        def fset(self, val):
> >            ac_val = int(val == True)
> >            self.execute('SET AUTOCOMMIT = %s', (ac_val,))
> >        fdel = DBIBase.autocommit.fdel
> >        doc = DBIBase.autocommit.__doc__
> >        return fget, fset, fdel, doc
> >    autocommit=property(*autocommit())
> >
> >    ...
> 
> This, while (I believe) correct, is not scalable, since the autocommit 
> property is accessed very frequently (potentially with every call to 
> execute()). After thinking about it some more, the following might be a 
> better solution:
> 
> >class MysqlDBI(DBIBase):
> >
> >    ...
> >
> >    def __init__(self, ...):
> >        ...
> >        ac_row = self.execute('SELECT @@SESSION.AUTOCOMMIT')
> >        ac_val = bool(ac_row[0]['AUTOCOMMIT'])
> >        DBIBase.autocommit.fset(self, ac_val)
> >
> >    ...
> >
> >    def autocommit():
> >        fget = DBIBase.autocommit.fget
> >        def fset(self, val):
> >            ac_val = bool(val)
> >            self.execute('SET AUTOCOMMIT = %s', (int(ac_val),))
> >            DBIBase.autocommit.fset(self, ac_val)
> >        fdel = DBIBase.autocommit.fdel
> >        doc = DBIBase.autocommit.__doc__
> >        return fget, fset, fdel, doc
> >    autocommit=property(*autocommit())
> >
> >    ...
> 
> This approach may break if the underlying implementation of the 
> autocommit property of the base class changes, but at least this will 
> stay (mostly) true to form and effectively cache the value of the 
> AUTOCOMMIT session variable for MySQL connections without unnecessary 
> queries.
> 
> I believe this will also solve the MySQL breakage problem seen above.
> 
> Just my $0.02 from looking at it for a brief moment.
> 
> 	-- Matt
> 
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.0 (Darwin)
> 
> iD8DBQFC+ZB8nLpDzL5I7l8RAi5NAKCKQabp+eY7DmNlBQdcaN1D0CiyVACdHO6C
> iaAaLX+lxCykhs8vmFTF/IA=
> =2Oij
> -----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
> _______________________________________________
> Skunkweb-list mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/skunkweb-list
> 

-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFC+fZMuqamFyFXXLIRAmbEAKCWt79+gfFl9madvWiMobxJpT9grwCfUgy2
13QufaRn8QmLpT53EMRsAEU=
=MgJ8
-----END PGP SIGNATURE-----