> 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_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
