[Sqlrelay-discussion] SQL Server connections hanging
Brought to you by:
mused
|
From: Dave B. <ra...@ra...> - 2005-10-10 18:02:23
|
I've been trying to get Zope to talk to SQL Server using FreeTDS, SQL
Relay, and ZSQLRelayDA. I ran into a problem that apparently others have
experienced but nobody has been able to help with, so I'm posting this
message in hopes that it will help others understand the problem and
perhaps fix the bug.
I'm not sure if the bug is with FreeTDS or SQL Relay, so please let me
know if I should bring this up with the FreeTDS folks.
The basic problem is that any calls to "commit" or "rollback" after a
query operation result in an error message, "Attempt to initiate a new SQL
Server operation with results pending.", which is printed to the console.
Once this occurs, the connection is corrupted. This is particularly
unfortunate with Zope because the ZSQLRelayDA adapter calls "commit" or
"rollback" after every transaction, whether it was a query or otherwise.
The end result is that after three queries (or however many connections
SQL Relay is configured for), future queries hang and the whole site goes
down.
Apparently, as long as your last statement was not a query (ie. anything
other than SELECT), you can commit or roll back without crashing the
connection. My temporary fix was to alter the db.py file that comes with
ZSQLRelayDA to add some (hopefully harmless) dummy statements before the
commit or rollback:
def _finish(self, *ignored):
self.cur.execute('SET NOCOUNT OFF') # hack
self.con.commit()
self.con.close()
def _abort(self, *ignored):
self.cur.execute('SET NOCOUNT OFF') # hack
self.con.rollback()
self.con.close()
These two lines that end with "# hack" have solved the problem for me, at
least so far. However, now my database adapter is MSSQL-specific, since I
doubt this statement would work in any other database.
I don't know too much about how the underlying libraries work, but I
believe this problem has something to do with result sets not being freed
properly. I can get it to crash with something as simple as:
con = PySQLRDB.connect(...)
cur = con.cursor()
cur.execute('select * from users') # or some other table
con.commit()
Thanks for your time,
Dave
--
.:[ dave benjamin: ramen/[sp00] ]:.
"Three pounds of VAX!"
|