Dan Fairs wrote:
> Hi,
>
> We're experiencing intermittent problems using the mxODBCZopeDA
> connecting to a SQL Server database. Every so often (in our development
> environments), we'll see tracebacks that look like this:
>
> Site Error
> An error was encountered while publishing this resource.
>
> mx.ODBC.Error.InternalError
>
> Sorry, a site error occurred.
>
> Traceback (innermost last):
>
> • Module ZPublisher.Publish, line 202, in publish_module_standard
> • Module ZPublisher.Publish, line 150, in publish
> • Module ZPublisher.Publish, line 119, in publish
> • Module ZPublisher.mapply, line 88, in mapply
> • Module ZPublisher.Publish, line 42, in call_object
> • Module webui.browser.repositoriesui.base, line 28, in __call__
> • Module webui.browser.repositoriesui.repositories, line 43, in update
> • Module webui.browser.repositoriesui.base, line 251, in getAssessments
> • Module app.directdb.loaders, line 124, in repositories
> • Module app.db._db, line 86, in execute
> • Module app.db._db, line 119, in _execute
> • Module Shared.DC.ZRDB.DA, line 500, in __call__
> <FSZSQLMethod at /root/zsql/direct/s_Repositories>
> • Module Products.mxODBCZopeDA.ZopeDA, line 1503, in query
> • Module Products.mxODBCZopeDA.ZopeDA, line 1400, in
> run_cursor_callback
> • Module Products.mxODBCZopeDA.ZopeDA, line 999, in errorhandler
> InternalError: ('24000', 0, '[unixODBC][FreeTDS][SQL Server]Invalid
> cursor state', 4543)
>
> Once this has happened, refreshing the page consistently returns the
> same error. Other pages in the site (which use other FSZSQLMethods using
> the same ZopeDA instance in the ZODB) continue to work fine - so the
> problem appears to be bound to a particular FSZSQLMethod. Restarting
> Zope makes the problem go away temporarily. The problem may recur
> quickly, or it may take days to appear again.
>
> When we load a page that provokes the error, while running SQL Profiler
> on the database server, we can see the SQL generated by the ZSQL method
> arrive and appear to be served correctly. I've turned on tracing in
> /etc/odbcinst.ini (though of course, since I've done that I can't
> provoke the error! I'll attach a trace log when I get one.)
Since you are using FreeTDS, you can also try enabling TDS tracing
which will have very low-level details about the error...
Edit ~/.freetds.conf and enable these settings in the [global] section:
[global]
...
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
dump file = /tmp/freetds.log
debug level = 10
> We know that the SQL that is being run is valid - we can copy it out of
> Profiler and run it in Management Studio; and besides, the page usually
> works flawlessly. The ZSQL method itself has no caching specified. All
> FSZSQLMethods live in FileSystemDirectoryViews, and are invoked directly
> from trusted Python code (ie. not a skin script).
This sounds a lot like a bug somewhere in FreeTDS or unixODBC
or perhaps a connection problem that causes the cursor to
get invalidated.
Have you tried using the latest unixODBC version ?
> We're using the following software versions (from Ubuntu's package
> manager):
>
> unixODBC 2.2.11-16build2
> tdsodbc 0.82-3ubuntu1
> Zope 2.10.4 (built using buildout and sources, not Ubuntu packages)
> mxODBCZopeDA-1.0.10
> SQL Server 2005 Developer (also happens on SQL Server 2008 Express)
> Zope server is Ubuntu 8.10, though this also happens with 9.04.
> Python 2.4.5 (built from source)
>
> The DA is configured with the following parameters:
>
> ODBC Driver Manager: unixODBC
> Connection pool size: 25
> All connection options set to 'off'
> Connection state - 'Open Connection' is checked.
>
> We don't see anything in the logs on the SQL Server side.
>
> I'm not sure where to look next, or what further tests to carry out in
> order to debug this further. The query does appear to be running
> correctly (with both BatchStarting and BatchCompleted entries in SQL
> Profiler).
>
> Has anyone seen something like this before, or have any idea what to
> look at when it starts happening again?
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, Jul 13 2009)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
::: Try our new mxODBC.Connect Python Database Interface for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/