<html><head><meta http-equiv="Content-Type" content="text/html charset=windows-1252"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><blockquote type="cite"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; ">I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase.<br>
<br>
Basically my connections just time out and fail in weird, generic ways like:<br>
<br>
Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)')<br>
<br>
(but in many others too, there is no real pattern)</blockquote><div>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.<br></div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>I tried to do a for loop like:</div><div><br></div><div><div> succ = False</div><div> for _ in range(self.dbpool.max + 1):</div><div> try:</div><div> yield self.dbpool.runOperation(self.dbpool.good_sql)</div><div> succ = True</div><div> except Exception:</div><div> pass</div><div><br></div><div> if not succ:</div><div> raise pyodbc.Error(b'Found no healthy connection.')</div><div><br></div><div>…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.</div></div><div><br></div><blockquote type="cite"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; ">
In my desperation, I’m employing for-loops for the SQL queries now. :(<br>
<br>
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.<br>
<br>
Is there some straight-forward way to do that? Or any better approach I’ve overlooked?</blockquote><div>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:<br>
<br><div style="margin-left:40px"> <font face="courier new,monospace">def dbTxn(x):<br> conn = db.connect(...)<br> cursor = conn.cursor()<br> cursor.execute()<br> result = cursor.fetchall()<br> conn.close()<br>
return result<br><br>deferredResult = deferToThread(dbtxn, argForX)</font></div></div></div></blockquote><br></div><div>Sounds like a solid backup plan, thanks!</div><div><br></div><div>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. :(</div><div><br></div><div>Cheers,</div><div>Hynek</div></body></html>