1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

support MS ODBC for linux / Connection is busy with results for another command

Issue #2909 resolved
Former user created an issue

I'm getting an error when using the "Microsoft ODBC Driver 11 for SQL Server on Linux" (http://msdn.microsoft.com/en-us/library/hh568451.aspx) driver with unixODBC, pyodbc and SQLAlchemy, but only when reflecting tables. My connection setup seems to be ok since I can query the database with raw pyodbc connection and get results back. Here's a minimal example of my reflection:

import sqlalchemy as sa

metadata = sa.MetaData()
metadata.bind = sa.create_engine('mssql+pyodbc://user:pass@work_database',
    logging_name='work',
    echo=True)

metadata_terrain = sa.MetaData()
metadata_terrain.bind = sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
    logging_name='terrain',
    echo=True)

address = sa.Table('address', metadata,
    sa.Column('addressid', sa.Integer, primary_key=True, autoincrement=False),
    sa.Column('refid', sa.Integer, sa.ForeignKey('dbo.Foo.Bar')),
    schema='dbo',
    autoload=True,
    extend_existing=True,
    )

Echoed by the engine:

2014-01-13 15:29:16,508 INFO sqlalchemy.engine.base.Engine.work SELECT user_name()
2014-01-13 15:29:16,508 INFO sqlalchemy.engine.base.Engine.work ()
2014-01-13 15:29:16,535 INFO sqlalchemy.engine.base.Engine.work
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2014-01-13 15:29:16,535 INFO sqlalchemy.engine.base.Engine.work (u'dbo',)
2014-01-13 15:29:16,681 INFO sqlalchemy.engine.base.Engine.work SELECT [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA), [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME), [COLUMNS_1](COLUMNS_1).[COLUMN_NAME](COLUMN_NAME), [COLUMNS_1](COLUMNS_1).[IS_NULLABLE](IS_NULLABLE), [COLUMNS_1](COLUMNS_1).[DATA_TYPE](DATA_TYPE), [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION), [COLUMNS_1](COLUMNS_1).[CHARACTER_MAXIMUM_LENGTH](CHARACTER_MAXIMUM_LENGTH), [COLUMNS_1](COLUMNS_1).[NUMERIC_PRECISION](NUMERIC_PRECISION), [COLUMNS_1](COLUMNS_1).[NUMERIC_SCALE](NUMERIC_SCALE), [COLUMNS_1](COLUMNS_1).[COLUMN_DEFAULT](COLUMN_DEFAULT), [COLUMNS_1](COLUMNS_1).[COLLATION_NAME](COLLATION_NAME)
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) AS [COLUMNS_1](COLUMNS_1)
WHERE [COLUMNS_1](COLUMNS_1).[TABLE_NAME](TABLE_NAME) = CAST(? AS NVARCHAR(max)) AND [COLUMNS_1](COLUMNS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = CAST(? AS NVARCHAR(max)) ORDER BY [COLUMNS_1](COLUMNS_1).[ORDINAL_POSITION](ORDINAL_POSITION)
2014-01-13 15:29:16,683 INFO sqlalchemy.engine.base.Engine.work ('address', 'dbo')
/usr/lib64/python2.6/site-packages/sqlalchemy/dialects/mssql/base.py:1350: SADeprecationWarning: Additional keyword arguments passed to Float ignored.
  coltype = coltype(**kwargs)
2014-01-13 15:29:16,753 INFO sqlalchemy.engine.base.Engine.work sp_columns @table_name = 'address', @table_owner = 'dbo'
2014-01-13 15:29:16,754 INFO sqlalchemy.engine.base.Engine.work ()
2014-01-13 15:29:16,801 INFO sqlalchemy.engine.base.Engine.work SELECT [C](C).[COLUMN_NAME](COLUMN_NAME), [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[CONSTRAINT_TYPE](CONSTRAINT_TYPE), [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME)
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [C](C), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[TABLE_CONSTRAINTS](TABLE_CONSTRAINTS) AS [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1)
WHERE [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME) AND [TABLE_CONSTRAINTS_1](TABLE_CONSTRAINTS_1).[TABLE_SCHEMA](TABLE_SCHEMA) = [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) AND [C](C).[TABLE_NAME](TABLE_NAME) = CAST(? AS NVARCHAR(max)) AND [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) = CAST(? AS NVARCHAR(max))
2014-01-13 15:29:16,801 INFO sqlalchemy.engine.base.Engine.work ('address', 'dbo')
2014-01-13 15:29:16,868 INFO sqlalchemy.engine.base.Engine.work SELECT [C](C).[COLUMN_NAME](COLUMN_NAME), [R](R).[TABLE_SCHEMA](TABLE_SCHEMA), [R](R).[TABLE_NAME](TABLE_NAME), [R](R).[COLUMN_NAME](COLUMN_NAME), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[MATCH_OPTION](MATCH_OPTION), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[UPDATE_RULE](UPDATE_RULE), [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[DELETE_RULE](DELETE_RULE)
FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [C](C), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[KEY_COLUMN_USAGE](KEY_COLUMN_USAGE) AS [R](R), [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[REFERENTIAL_CONSTRAINTS](REFERENTIAL_CONSTRAINTS) AS [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1)
WHERE [C](C).[TABLE_NAME](TABLE_NAME) = CAST(? AS NVARCHAR(max)) AND [C](C).[TABLE_SCHEMA](TABLE_SCHEMA) = CAST(? AS NVARCHAR(max)) AND [C](C).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME) AND [R](R).[CONSTRAINT_NAME](CONSTRAINT_NAME) = [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[UNIQUE_CONSTRAINT_NAME](UNIQUE_CONSTRAINT_NAME) AND [C](C).[ORDINAL_POSITION](ORDINAL_POSITION) = [R](R).[ORDINAL_POSITION](ORDINAL_POSITION) ORDER BY [REFERENTIAL_CONSTRAINTS_1](REFERENTIAL_CONSTRAINTS_1).[CONSTRAINT_NAME](CONSTRAINT_NAME), [R](R).[ORDINAL_POSITION](ORDINAL_POSITION)
2014-01-13 15:29:16,868 INFO sqlalchemy.engine.base.Engine.work ('address', 'dbo')
2014-01-13 15:29:16,952 INFO sqlalchemy.engine.base.Engine.work select ind.index_id, ind.is_unique, ind.name from sys.indexes as ind join sys.tables as tab on ind.object_id=tab.object_id join sys.schemas as sch on sch.schema_id=tab.schema_id where tab.name = ? and sch.name=? and ind.is_primary_key=0
2014-01-13 15:29:16,953 INFO sqlalchemy.engine.base.Engine.work ('address', 'dbo')
2014-01-13 15:29:17,009 INFO sqlalchemy.engine.base.Engine.work select ind_col.index_id, ind_col.object_id, col.name from sys.columns as col join sys.tables as tab on tab.object_id=col.object_id join sys.index_columns as ind_col on (ind_col.column_id=col.column_id and ind_col.object_id=tab.object_id) join sys.schemas as sch on sch.schema_id=tab.schema_id where tab.name=? and sch.name=?
2014-01-13 15:29:17,010 INFO sqlalchemy.engine.base.Engine.work ('address', 'dbo')

Nothing wrong there, but when I add another table on the second database:

terrain_attachment = sa.Table('Attachment', metadata_terrain,
    schema='dbo',
    autoload=True,
    extend_existing=True,
    )

Echo and traceback:

2014-01-13 15:29:17,321 INFO sqlalchemy.engine.base.Engine.terrain SELECT user_name()
2014-01-13 15:29:17,321 INFO sqlalchemy.engine.base.Engine.terrain ()
Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line 355, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line 429, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line 457, in _autoload
    self, include_columns, exclude_columns
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1578, in run_callable
    with self.contextual_connect() as conn:
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1645, in contextual_connect
    self.pool.connect(),
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 271, in connect
    return _ConnectionFairy.checkout(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 466, in checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 329, in checkout
    rec = pool._do_get()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 799, in _do_get
    return self._create_connection()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 224, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 320, in __init__
    exec_once(self.connection, self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/event/attr.py", line 238, in exec_once
    self(*args, **kw)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/event/attr.py", line 247, in __call__
    fn(*args, **kw)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/util/langhelpers.py", line 1180, in go
    return once_fn(*arg, **kw)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/strategies.py", line 166, in first_connect
    dialect.initialize(c)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/connectors/pyodbc.py", line 142, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/dialects/mssql/base.py", line 1129, in initialize
    super(MSDialect, self).initialize(connection)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py", line 199, in initialize
    self._get_default_schema_name(connection)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/dialects/mssql/base.py", line 1145, in _get_default_schema_name
    user_name = connection.scalar("SELECT user_name()")
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 594, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 656, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 805, in _execute_text
    statement, parameters
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1023, in _handle_dbapi_exception
    exc_info
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py", line 388, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DBAPIError: (Error) ('HY000', '[HY000](HY000) [Microsoft](Microsoft)[Server Native Client 11.0](SQL)Connection is busy with results for another command (0) (SQLExecDirectW)') 'SELECT user_name()' ()

The same application code works fine with the FreeTDS driver.

CentOS 6.5 x86_64 Python 2.6.6 pyodbc 3.0.3 SQLAlchemy 0.9.1 unixODBC 2.3.0

Comments (4)

  1. Mike Bayer repo owner

    correct me if I'm wrong, this doesn't seem at all like any issue with reflecting tables; the reflection works fine. Something is specifically wrong with either your second data source or just having an application that's talking to two datasources at once.

    let's keep it simple. run this:

    import sqlalchemy as sa

e1 = sa.create_engine('mssql+pyodbc://user:pass@work_database',
    logging_name='work',
    echo=True)

e2 = sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
    logging_name='terrain',
    echo=True)

e1.execute("select 1").fetchall()
e2.execute("select 1").fetchall()

    a. does it crash and b. does it crash only on the second engine? in that case, how about:

    import sqlalchemy as sa

e2 = sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
    logging_name='terrain',
    echo=True)

e2.execute("select 1").fetchall()

    e.g. is there some issue just with the "terrain" database.

    also note that we haven't supported the MS ODBC driver for linux as of yet.

  2. Former user Account Deleted

    Okay, it looks like two datasources is the problem. Both of the connections work fine on their own, but when I try to use another one I get the same exception. I tried creating two connections with just pyodbc alone, and even that crashes. Only the first connection making the trip to database works. So I guess this isn't SQLAlchemy issue after all.

  5. Log in to comment
Assignee
Type
bug
Priority
critical
Status
resolved
Component
mssql
Milestone
1.0.xx
Votes
0
Watchers
0
Jira: the preferred issue tracker for Bitbucket. Join the team!