Twisted Sybase SQL Anywhere

Using the official sqlanydb driver for Python together with Twisted’s adbapi produces not-so-occasional crashes as of today (sqlanydb 1.0.2, Twisted 11.0.0). Apparently, the official SQL Anywhere drivers aren’t thread-safe. It cost me several days to figure out because I was searching the fault in my code so I hope to spare you some pain.

Please note: I wrote a summary on SQLAnywhere and Python that supersedes this article. I’m keeping it around only for googleability.

Basically, there are two possible solutions to avoid the crashes (manifesting themselves in aborts/SIGABRTs, gdb revealed that there are segmentation faults inside of the binary-only driver.) : Limit the database pool to one thread by supplying cp_max=1 when constructing adbapi.ConnectionPool. Unfortunately that means that only one database query at a time can run as the driver isn’t asynchronous.

The better alternative is using unixODBC, pyodbc and SQL Anywhere’s ODBC drivers. It can get a bit hairy though as again, thread issues can get into your way. So if you see crashes like:

python: pthread_mutex_lock.c:62: __pthread_mutex_lock: Assertion `mutex->__data.__owner == 0' failed.

Try to add Threading = 2 to your odbcinst.ini (or odbc.ini if you put everything in there):

[Sybase]
Driver          = /opt/sqlanywhere12/lib64/libdbodbc12_r.so
Threading       = 2

A full example of an odbc.ini for a shared memory test server installed in the default location may look like this:

[test]
Uid             = DBA
Pwd             = sql
Driver          = /opt/sqlanywhere12/lib64/libdbodbc12_r.so
Threading       = 2
ServerName      = test

It might seem ironic to be adding such an option for an asynchronous framework like Twisted but as there’s nothing like txpostgres for ODBC yet, so the database code runs in a separate thread.

At the moment, it seems pretty stable but developing with it is a pain as database errors just make the application hang. A txODBC would be really awesome.

On a related note I learned the hard way that the latest pyodbc (as of writing 2.1.8) and RHEL/CentOS 5’s ancient unixODBC (2.2.11, released in March 2005) don’t play along very well. At least in the case of using it together with SQL Anywhere, the queries simply hang.

Updating unixODBC to 2.3 (2. 2.2.14 works too, it’s default for eg. RHEL 6 or Ubuntu Natty Narwhal) fixed the problem but introduces problems with either LD_LIBRARY_PATH juggling or broken dependencies in packages like FreeTDS or Erlang that depend on unixODBC. Sometimes computers just aren’t fun.

Update: In production I encountered another problem after a few hours runtime: some part of the unixodbc/sqlanywhere driver stack hogs semaphores and at some point runs out of them. My advice is to not use sqlanywhere if possible.

Update 2: I’ve been contacted by a Sybase engineer that the leaked semaphore issues has been fixed by SQLAnywhere “12.0.1 build 3713 or higher”.