mssql+pyodbc on sa06 still raising errors?

[Victor Ng]

I didn't know if the mssql test cases were supposed to pass 100% clean
using the pyodbc backend?

I'm getting lots of problems in test.orm.test_session where the tests
hang.

Is there a builtbot page somewhere that I can use a reference to see
what tests I can expect to work with mssql?

I'm currently testing a backend for mssql2k with jython and wanted to
have a baseline of tests to measure against.

thanks,
vic

[Victor Ng]

Something in the fixture code - test.orm._fixtures is causing the DDL
generation to hang.

Anybody seeing this on any other database backends ?

vic

[mike bayer]

0.6 is not yet on the buildbot and we also do not have a working MSSQL buildbot.  MSSQL is in the "not all tests passing" category right now.   although I can get every test in test/engine and test/sql to pass on MSSQL using pyodbc+freetds, save for those that write binary data.

[mike bayer]

although with Jython, that would require a zxjdbc bridge to the MSSQL JDBC driver.   running pyodbc through jython is basically insane.

[Victor Ng]

Sorry - I wasn't being clear in my earlier mail. I slapped together a
zxJDBC dialect for MSSQL (using jTDS). I just wanted to see my tests
passing as well as the pyodbc test cases.

I've made some headway in the hanging transactions problem with
MSSQL. Some are definitely related to the wonky way that foreign keys
are handled by sql server.

If I disable all the foreign key references in the test.orm._fixtures
module, at least some of the tests no longer hang - an example is
test.orm.test_session:SessionTest.test_active_flag.

vic

[Victor Ng]

err.. nevermind. My python process detached from my windows
terminal. Stupid Windows.

So the transactional hanging bugs are still there.

vic

[mike bayer]

yah they're hard to deal with.   usually they are when the test tries to drop tables and locks still remain.  with MSSQL specifically, sometimes its the "fetch the last row id" logic occurring at an inappropriate place.   you need to turn on SQL echoing fully, use PDB, etc.   as of last weekend I have MSSQL + pyodbc working really well and we also have jython + PG working really well, so the two sides of the puzzle seem to be doing the right thing.

[Victor Ng]

I guess the better question I should be asking is - what's the right
way of handling inconsistent isolation definitions across database
backends?

I was testing against pyodbc+mssql and psycopg2+pgsql8.3.

With the READ COMMITTED isolation level, mssql 2000 (version 8) just
hangs forever. Setting it to READ UNCOMMITTED is obviously the wrong
thing to do, but it prevents the hanging from happening.

Here's my test harness, I invoked it with "python test_isolation.py
pyodbc" and "python test_isolation.py psycopg2"

The test is patterned after
test.orm.test_session:SessionTest.test_autoflush.

So - basically here's what I think:

MSSQL is basically doing the wrong thing here - I don't see why it
should be hanging on different connections.

With READ COMMITED, the behaviour is:

pyodbc: hangs
zxJDBC: hangs

Bizarrely, with READ UNCOMMITTED, behaviour is divergent.

pyodbc:
raises an error on fetching the results from cursor 1 :
pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC SQL Server Driver]
Function sequence error (0) (SQLFetch)')

zxJDBC+jTDS:
cursor 2 fetches back nothing , which is what i expect.

Ideas on handling this wackyness? Do we just mark the testcase with
the @testing.crashes decorator for specific drivers?

I can make the MSSQL driver default to UNCOMMITTED READ isolation to
make sure the tests run, or we can leave the tests with COMMITED READ
and the tests halt.

I'm not sure what's worse - tests that run incorrectly or a testsuite
that halts.


vic (i really really hate sqlserver at this moment)

Here's my test driver - forgive the sloppy code, I just wanted to see
what the hell was going on.

----

import sys
import os

dbapi_driver = sys.argv[-1]

def zxjdbc_conn():
from com.ziclix.python.sql import zxJDBC
jdbc_url = "jdbc:jtds:sqlserver://localhost:1433/sa06_test"
conn = zxJDBC.connect(jdbc_url, None, None,
'net.sourceforge.jtds.jdbc.Driver')
return conn

def pyodbc_conn():
import pyodbc
return pyodbc.connect('dsn=sa06_odbc')

def psycopg2_conn():
import psycopg2
return psycopg2.connect('dbname=ngvictor')

def get_connection():
print "Using: ", sys.argv[-1]
if dbapi_driver == 'psycopg2':
return psycopg2_conn()
elif dbapi_driver == 'pyodbc':
return pyodbc_conn()
return zxjdbc_conn()

conn1 = get_connection()
conn2 = get_connection()
import pdb
pdb.set_trace()

cur1 = conn1.cursor()
cur2 = conn2.cursor()

if dbapi_driver == 'pyodbc':
cur1.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
cur2.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

cur1.execute("insert into test_table (col_a) values (10)")
cur1.execute("select count(1) from test_table")
cur2.execute("select count(1) from test_table")
conn1.commit()

print "Cursor 1 results:", cur1.fetchall()
print "Cursor 2 results:", cur1.fetchall()

vic

[Victor Ng]

I should qualify - my results were tested against SQL Server 2000
(version 8) - I'm not sure if the deadlocking happens with SQL Server
2005 or high which supposedly has MVCC support.

If I get a chance, I'll try against SQL Server 2008 express tonight.

vic

[mike bayer]

if you have a plain reproducing test case in pyodbc that hangs on MS2000, then that's a pyodbc bug.  you should submit it to their tracker.

[Victor Ng]

The hanging bug happens with both pyodbc and jTDS in java. As far as
I can tell - it's a problem with the concurrency handling in MS2000.

vic

[Victor Ng]

Which version of SQL Server were you testing with?

I can reliably reproduce the locking problem with SQL2000.

I can get the locking problems with transactions to go away with a
patch to enable isolation levels, and then settting the isolation
level to SNAPSHOT, but that only works on my mssql2k5 instance, not on
sql2000.

Is there a way I can tell nose to set the **options on create_engine
for the testrunner? I've been hardcoding "isolation_level='SNAPSHOT'"
for now to just get my tests to run.

Without snapshot capability - I'm pretty sure that SQL2000 is going to
lock up for some tests. What do you think about adding yet another
decorator to filter out specific database server versions?

Setting the isolation level means I need to be able to issue a SET
TRANSACTION ISOLATION LEVEL statement for all DML statements, but not
for DDL statements. Is there a proper hook to do that? I hacked up
the do_execute and do_executemany methods, and my change works, but
it's too hacky.

thanks,
vic

[mike bayer]

is it just the SAVEPOINT tests failing ?  because sure, we can start filtering out MS2000 for that.  I only test on MS2005.  At one point i had 2008 installed but its beyond my windowness to actually figure out how to have both installed at the same time.

[Victor Ng]

No, the lockups occur because SQL2000 doesn't really have independent
database connections.

test.orm.test_session:SessionTest.test_autoflush is a good example.

It starts a transaction, inserts a record and selects the record.

Before the transaction is closed, a different connection tries to
select records.

That causes deadlock since the testcase is running both connections in
one thread (the test runner) and SQL2000 doesn't support MVCC/SNAPSHOT
isolation at all.

You can reproduce this with raw SQL in the query analyzer with:

---snip--
begin transaction
go
insert into users (name) values ('ed');
go
select count(1) from users
go
waitfor delay '00:00:10'
-- open a separate connection to the database once the wait takes
place and run "select count(1) from users"
go

rollback
---snip--

For SQL2005+, you still have to manually enable MVCC isolation with:

ALTER DATABASE db_name SET ALLOW_SNAPSHOT_ISOLATION ON;
(http://msdn.microsoft.com/en-us/library/ms175095.aspx)

So... I think the right thing to do is to make a new decorator that :

* checks dialect name
* checks the server version number (< 9 is unsupported) and
* checks the dialect isolation (must have SNAPSHOT enabled)

I'm a bit confused by sqlalchemy.test.testing - so my code 'works' but
it seems clunky.

I've added a new testing decorator filter like this:

def _dialect_isolation_level(bind=None):
"""Return a isolation_level from the dialect."""

if bind is None:
bind = config.db

# force metadata to be retrieved
conn = bind.connect()
isolation_level = getattr(bind.dialect, 'isolation_level', None)
conn.close()
return isolation_level

def check_required_isolation(db, op, spec, required_isolation_level,
reason=None):
"""
Skip a test if the isolation level doesn't match.
"""
spec = db_spec(db)
def decorate(fn):
fn_name = fn.__name__
def maybe(*args, **kw):
if _is_excluded(db, op, spec):
msg = "'%s' unsupported on DB %s version '%s':
Isolation level: %s : %s" % (
fn_name, config.db.name, _server_version(),
_dialect_isolation_level(), reason)
print msg
return True
elif spec(config.db) and _dialect_isolation_level() <>
required_isolation_level:
msg = "'%s' unsupported on DB %s version '%s':
Isolation level: %s : %s" % (
fn_name, config.db.name, _server_version(),
_dialect_isolation_level(), reason)
print msg
return True
else:
return fn(*args, **kw)
return function_named(maybe, fn_name)
return decorate

and I've changed the signature of
test.orm.test_session:SessionTest.test_autoflush to be :


@testing.check_required_isolation('mssql', '<', (9,0,0),
'SNAPSHOT', 'SQL Server 2008 or higher is required and SNAPSHOT
isolation must be enabled')
@engines.close_open_connections
@testing.resolve_artifact_names
def test_autoflush(self):
...

This still means that the database that the user is testing on needs
to manually set the ALLOW_SNAPSHOT_ISOLATION or else the tests will
lock up, but I think that's acceptable.

What do you think?

vic
Your remark about windowness made me laugh. It's also beyond my
windowness to get 2008 and 2005 running concurrently. I could barely
get 2000 and 2005 working concurrently.

[mike bayer]

the behavior you describe is the same as when we test on a sqlite :memory: database (only one connection).  Seems like the MSSQL criterion you describe needs to be added to @testing.requires.independent_connections and we're done.  note that the tests do run on 2005 since we have in the README that ALLOW_SNAPSHOT_ISOLATION should be turned on.

[Victor Ng]

Are there actually cases where SQL2005+ works properly when the
ALLOW_SNAPSHOT_ISOLATION is enabled on the database but the session
does not force the isolation level?

When I try to run test_autoflush(), I get locking if the cursor
doesn't explicitly call : "SET TRANSACTION ISOLATION LEVEL SNAPSHOT"
regardless of whether or not the database is allows snapshot
isolation.

W.r.t the independent_connections decorator - I'm not sure how to
modify the code properly.

I've got this:

def independent_connections(fn):
"""Target must support simultaneous, independent database
connections."""

# This is also true of some configurations of UnixODBC and
probably win32
# ODBC as well.
return _chain_decorators_on(
fn,
no_support('sqlite', 'no driver support'),
exclude('mssql', '<', (9, 0, 0), 'SQL Server 2005+ is required
for independent connections'),
)

But I'm not sure how to extend that to exclude the mssql dialect if
isolation_level is not SNAPSHOT.

vic

[mike bayer]

I'd just make it disallow below SQL server 2005 and be done with it.    running the tests with 2005 assumes you've set things up properly.

[Victor Ng]

But that still doesn't actually resolve lockups *with* SQL Server
2005.

test.orm.test_session:SessionTest.test_autoflush() locks up everytime
with 2005 for me with the code in the SVN trunk.

What I don't understand is how the connection tests are passing for
you at all. None of the MSSQL dialects set the isolation to snapshot

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Is test_autoflush() working for you right now? I'm getting the locks
with CPython 2.5, pyodbc and SQL Server 2005 with a database where
snapshot is enabled.

vic

On Aug 10, 3:45 pm, mike bayer <zzz...@gmail.com> wrote:
> I'd just make it disallow below SQL server 2005 and be done with it.
> running the tests with 2005 assumes you've set things up properly.
>

[mike bayer]

this is how the README describes it:

MSSQL: Tests that involve multiple connections require Snapshot Isolation
ability implented on the test database in order to prevent deadlocks that will
occur with record locking isolation. This feature is only available with
MSSQL 2005 and greater. For example::

    ALTER DATABASE MyDatabase
    SET ALLOW_SNAPSHOT_ISOLATION ON

    ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON



you ALTER the database ahead of time.

[Michael Trier]

On Aug 10, 2009, at 5:16 PM, mike bayer <zzz...@gmail.com> wrote:

this is how the README describes it:

MSSQL: Tests that involve multiple connections require Snapshot Isolation
ability implented on the test database in order to prevent deadlocks that will
occur with record locking isolation. This feature is only available with
MSSQL 2005 and greater. For example::

    ALTER DATABASE MyDatabase
    SET ALLOW_SNAPSHOT_ISOLATION ON

    ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON

Yeah it's the second command that treats all read committed as snapshot reads. Without that you will need to be sure to set the isolation level yourself. 

The locking issues on SQL 2000 cannot be avoided with the standard read committed isolation as you have shown with tsql. 

I don't think it's necessary for the tests to accomodate any database someone throws at it. There is always some expectation that things will be configured a certain way. 

[Victor Ng]

Thanks Michael (and Mike).

It wasn't obvious to me from reading the SQL Server docs that I needed
to set both alter statements. I only ran the ALLOW_SNAPSHOT_ISOLATION
statement.

Things seem to be working with zxJDBC now - or at least as well as
they do with pyodbc.

vic