Re: Pydo pools - two simple questions

--===============0692370240==
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="mYCpIKhGyMATD0i+"
Content-Disposition: inline


--mYCpIKhGyMATD0i+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 01, 2006 at 12:23:26PM +1200, Richard Shea wrote:

> Hi - I'm using Pydo with MySQL but after the system has not been used
> for a while I keep getting the dreaded "MySQL has gone away" message.
>
> FIRST QUESTION:
>=20
> I understand using a Pool may help this but is this only true if I
> override subclass ConnectionPool and override onHandOut or will it just
> help anyway ? (I'm not 100% sure what to do in onHandOut otherwise I'd
> just try). Any examples ?

A pool is one approach; all you need do in onHandOut is return True or
False depending on whether the connection is any good.  Here is some
(untested) code:

  from pydo import ConnectionPool, exception

  class MyRobusterPool(ConnectionPool):

    def onHandOut(self, realConn):
        try:
           c=3DrealConn.cursor()
           c.execute("SELECT ''")
           c.fetchone()
           c.close()
           return True
        except:
           exception("stale connection in onHandOut")
           return False

If you aren't using multiple threads, you don't need more than one
connection and don't really need a pool at all, although you can still
do so (with max_poolsize=3D1) simply to hide the connection health test.
If you are calling commit very frequently, say, in a loop, then that
isn't going to be such a great idea, however.  And if you are in
autocommit mode, the health test will be getting called very
frequently indeed, much more than you want.  So you may want to put
the health test in your application rather than in pydo, or have the
health test in your pool subclass be application-aware:

  from FictitiousWebFramework import MagicGlobalRequestObject
  from pydo import ConnectionPool, exception

  class MyWebFrameworkSpecificRobusterPool(ConnectionPool):

    def onHandOut(self, realConn):
        isok=3Dgetattr(MagicGlobalRequestObject, '_connectionOK', False)
        if isok:=20
            return True
        try:
           c=3DrealConn.cursor()
           c.execute("SELECT ''")
           c.fetchone()
           c.close()
           MagicGlobalRequestObject._connectionOK=3DTrue
           return True
        except:
           exception("stale connection in onHandOut")
           return False

In the above, the health check is performed once per request, and only
if the request actually wants to talk to MySQL; non-db requests that
the framework might be handling won't be performing these health
checks.  You could also limit the checks to only execute sql every so
often, caching the result; if your mysql only misbehaves after a
period of user neglect that might work for you.

With a web application, incidentally, you really want to issue a
rollback at the end of every request if you are reusing connections.

(Hmm, I'm now tempted to write a web framework called "Fictitious").

If you prefer to put a health check into the application itself and
not use a pool, to axe a connection you believe to be faulty, you can
do this:

   del myObj.getDBI().conn

The next time the connection is needed, it will be recreated.
=20
> SECOND QUESTION:
>=20
> I define a connection and then import that into a class which subclasses
> PyDO (see below). Will the connection get returned to the pool after
> every method invocation of Programme ? I have a feeling it won't !

In PyDO, when you are using a pool the connection is returned to the
pool when the transaction is over.  The assumption made is that
transaction boundaries are also connection state boundaries. =20

I hope you aren't using mysql in autocommit mode. Especially not with
a pydo pool. If you are, then you potentially have a different
connection for each statement with a PyDO pool (well, for each atomic
block that PyDO executes, which may involve more than one statement),
which I doubt is a wonderful thing, if the connection maintains any
state that might impinge on your application.

Cheers,

js

--=20
Jacob Smullyan

--mYCpIKhGyMATD0i+
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFEzr63uqamFyFXXLIRAjjAAKDVDfcGZWSGPjuHET4jzrmvLew1nACcC8i6
r4Nx4wMAplifS02FUbV+88E=
=sAVk
-----END PGP SIGNATURE-----

--mYCpIKhGyMATD0i+--


--===============0692370240==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
--===============0692370240==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
Skunkweb-list mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/skunkweb-list

--===============0692370240==--