I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase.

Basically my connections just time out and fail in weird, generic ways like:

Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)')

(but in many others too, there is no real pattern) Have you tried enabling reconnects (cp_reconnect=True)? If "select 1" doesn't work with your database you may also have to pass in custom cp_good_sql.

Yep, and the connections *do* heal after dying. I don't have to restart twistd, it just takes a few failed requests to warm up again.

I tried to do a for loop like:

succ = False for _ in range(self.dbpool.max + 1): try: yield self.dbpool.runOperation(self.dbpool.good_sql) succ = True except Exception: pass

if not succ: raise pyodbc.Error(b'Found no healthy connection.')

…for new connections, but that helped only a bit. I presume once it finds a working connection, it keeps using it. But I still had failure within the session later.

In my desperation, I’m employing for-loops for the SQL queries now. :(

Since there isn’t much traffic (yet) I would like to just make ConnectionPool close the connections and re-open fresh ones, as soon as they are necessary.

Is there some straight-forward way to do that? Or any better approach I’ve overlooked? Don't use ConnectionPool at all. Just have a function that does the SQL connect etc usually normal DB-API, and call it with twisted.internet.threads.deferToThread:

def dbTxn(x): conn = db.connect(...) cursor = conn.cursor() cursor.execute() result = cursor.fetchall() conn.close() return result

deferredResult = deferToThread(dbtxn, argForX)

Sounds like a solid backup plan, thanks!

The only reason I’d prefer to stay with pooling is that I very much expect the traffic to rise and wouldn’t really want to change away and go back later. :(

Cheers, Hynek

Am 27.11.2012 um 14:39 schrieb exarkun@twistedmatrix.com:

...

The only reason I’d prefer to stay with pooling is that I very much expect the traffic to rise and wouldn’t really want to change away and go back later. :(

Then I'd strongly recommend taking a look at twext.enterprise:

http://trac.calendarserver.org/browser/CalendarServer/trunk/twext/enterprise

There's a good chance this will become part of Twisted as a replacement for twisted.enterprise at some point. So any experience you get with it now should serve you well in the future. Plus, if you find it also can't deal with your situation, then it'd be nice if we knew that now. :)

Gladly! Do I see it correctly, that the current canonical way to use it, is to svn co it?