SQL Server: crash on conn.close() without rollback/commit

[dirkjonker]

When closing a connection (only after cursor usage), I get the following error:

terminate called after throwing an instance of 'cpp_odbc::error'
  what():  ODBC error
state: 25000
native error code: 0
message: [Microsoft][ODBC Driver 13 for SQL Server]Invalid transaction state

Script to reproduce the error:

import turbodbc

conn = turbodbc.connect(driver='ODBC Driver 13 for SQL Server', 
    server='xxx', port='1433', uid='xxx', pwd='xxx')
cursor = conn.cursor()
cursor.execute("SELECT * FROM sys.tables")
cursor.close()
conn.close()

This can be prevented by calling conn.rollback() or conn.commit() before closing the connection, since turbodbc connects to SQL Server in manual commit mode, which means all commands will be running inside of a transaction as soon as you open a cursor, and closing a connection before closing the transaction will cause this error.

Specifications:
Fedora 25 - 64 bit
Python 3.5.2
Turbodbc 1.0.0
unixODBC 2.3.4
ODBC Driver: msodbcsql-13.1.4.0-1 (official Microsoft driver)

[MathMagique]

Hi Dirk! Weird how databases and database drivers behave differently; I have never seen such behavior with other databases. I will think about how best to avoid this issue as soon as I add automated tests for MSSQL (which will be soon).

One option could be to do a rollback whenever a connection with uncommitted operations is closed. Another option would be to offer an autocommit option as requested in #41.

[MathMagique]

Btw: I just had a look at your ffodbc project and your goal to be compatible with pypy. Turbodbc uses pybind11 for its Python bindings. The pybind11 developers are working on pypy support (see their issue pybind/pybind11#596), so when this lands, turbodbc should support pypy as well.

[MathMagique]

I just realized the terminate called part. I think it would be sensible (for now) to raise an exception, but it should not terminate. That sounds like a bug.

[dirkjonker]

@MathMagique thanks for your comments! The ffodbc project is pretty much a hobby project to help me figure out how ODBC works, and how to get more speed out of it. Then I found turbodbc ;) would indeed be awesome if it would just work with pypy!

[MathMagique]

This bug was caused by the connection being closed while there were still open transactions. While closing the connection, MSSQL complained about this. This lead to exceptions being thrown in C++ destructors, which (as of C++11) have an invisible noexcept specification by default. This lead to the calls to terminate().

The issue is resolved by doing a rollback before disconnecting. This is safe because anything that was committed before is not affected by a subsequent rollback.

The issue was not spotted earlier because Exasol, MySQL, and PostgreSQL (my tested databases) all perform implicit rollbacks when closing connections with open transactions.

[keitherskine]

If I understand this issue correctly, and I've run a test, even if I wrap an exception handler around @dirkjonker 's test code, I'm getting a core dump. That's not good. I'm not sure how that can be trapped and handled in client code.

It would be nice if MS SQL rolled back any open transactions before disconnecting, but apparently it doesn't. So it would be very helpful indeed if turbodbc did a rollback before disconnecting anyway, just in case. (Unless this is being fixed by #21 ?)

[MathMagique]

@keitherskine You understood the issue correctly, and I agree a core dump is not what you want to see ;-). And you could not catch it in the Python code (except for manually rolling back to avoid the issue altogether). That's the state with turbodbc<=1.0.1.

In the upcoming release (and indeed the current status here at GitHub), turbodbc does the rollback you requested and as I have written above.

The fixes I did for #21 (just about an hour ago) should avoid similar errors in the future for situations when the server closes the connection abruptly etc.

So all in all: just wait for turbodbc 1.02 to come out (probably this weekend).

[keitherskine]

Great! Many thanks!

[davidalbertonogueira]

Just as a note for future argonauts crossing this sea, I had the same problem with pypyodbc.
Adding connection.rollback() solved the problem.