Error using mssql+pyodbc via sqlalchemy to connect to MS SQL Server from Linux

Question

I have a Python application that runs on a Linux machine and connects to an MS SQL Server database running in a Windows Server inside a virtual machine. I have my reasons. My application makes some large queries and processes a lot of data. It mostly works fine, but every so often the program just crashes with the following:

python: read.c:207: tds_get_string: Assertion `dest_size >= (size_t) string_len' failed.

I'm not sure where to start with this. There is no Python stack trace. Rather, the program just prints the above and terminates. If this were a normal exception I could catch it and deal with it.

My sqlalchemy connection string is:

mssql+pyodbc://username:password@localhost:1433/database?driver=FreeTDS

If it's important, tsql -C gives the following output:

Compile-time settings (established with the "configure" script)

                        Version: freetds v0.91
         freetds.conf directory: /etc/freetds
 MS db-lib source compatibility: no
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes

My guess is that my problem is the result of a bug in freetds. I installed freetds and unixodbc through apt-get on my Linux Mint machine. I installed pyodbc via pip, and I'm using anaconda. I've been experimenting with installing various different versions of freetds, unixodbc, and pyodbc from source. So far, I haven't found a combination that works at all. However, I don't really know what I'm doing. I'm looking for explanations, ideas, or workarounds. Or, of course, simple and complete solutions.

Can you include your connection string? What TDS Version are you using? Also, for more comprehensive FreeTDS stack traces, you can turn on error logging to a dump file: freetds.org/userguide/logging.htm These should provide some clues. — FlipperPA
– FlipperPA, Commented Jan 19, 2017 at 11:19
@FlipperPA Thanks for the link. I think the TDS version is in the tsql -C output? I will add my connection string to my question. — jcrudy
– jcrudy, Commented Jan 19, 2017 at 22:41

jcrudy · Accepted Answer · 2017-01-20 22:51:28Z

11

I found a workaround: don't use freetds. It turns out there is a microsoft odbc driver available for linux. I followed the instructions here to install it. I had to upgrade my kernel, among other things, to make it work. Once installed, I looked in my odbcinst.ini file and there was a new entry, "ODBC Driver 13 for SQL Server". I changed my sqlalchemy connections string to match it:

mssql+pyodbc://username:password@localhost:1433/database?driver=ODBC+Driver+13+for+SQL+Server

and now everything seems to be working. The microsoft driver feels significantly slower than freetds (I haven't actually timed it), but I'm not experiencing any of the errors I was before.

answered Jan 20, 2017 at 22:51

jcrudy

4,0711 gold badge26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FlipperPA Over a year ago

That solution will work; I've had issues with the MS ODBC driver in the past, but haven't tried the most recent versions. Older versions had issues with any code that used threading (like Django's manage.py runserver). With FreeTDS, the TDS Version from tsql -C is just the global default version. If you choose to go back to FreeTDS, you'll have to include a TDS_Version=7.x in your connection string.

Collectives™ on Stack Overflow

Error using mssql+pyodbc via sqlalchemy to connect to MS SQL Server from Linux

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related