[Sqlalchemy-tickets] [sqlalchemy] #2909: Reflection error: Connection is busy with results for anot

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

#2909: Reflection error: Connection is busy with results for another command
--------------------+-----------------------------------------
 Reporter:  tuomur  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:
Component:  engine  |        Severity:  no triage selected yet
 Keywords:          |  Progress State:  awaiting triage
--------------------+-----------------------------------------
 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:

 {{{#!python
 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].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
 [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
 [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
 [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
 [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
 [COLUMNS_1].[COLLATION_NAME]
 FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
 WHERE [COLUMNS_1].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND
 [COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY
 [COLUMNS_1].[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].[COLUMN_NAME], [TABLE_CONSTRAINTS_1].[CONSTRAINT_TYPE],
 [C].[CONSTRAINT_NAME]
 FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
 [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [TABLE_CONSTRAINTS_1]
 WHERE [TABLE_CONSTRAINTS_1].[CONSTRAINT_NAME] = [C].[CONSTRAINT_NAME] AND
 [TABLE_CONSTRAINTS_1].[TABLE_SCHEMA] = [C].[TABLE_SCHEMA] AND
 [C].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [C].[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].[COLUMN_NAME], [R].[TABLE_SCHEMA], [R].[TABLE_NAME],
 [R].[COLUMN_NAME], [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME],
 [REFERENTIAL_CONSTRAINTS_1].[MATCH_OPTION],
 [REFERENTIAL_CONSTRAINTS_1].[UPDATE_RULE],
 [REFERENTIAL_CONSTRAINTS_1].[DELETE_RULE]
 FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
 [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R],
 [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS
 [REFERENTIAL_CONSTRAINTS_1]
 WHERE [C].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [C].[TABLE_SCHEMA] =
 CAST(? AS NVARCHAR(max)) AND [C].[CONSTRAINT_NAME] =
 [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME] AND [R].[CONSTRAINT_NAME] =
 [REFERENTIAL_CONSTRAINTS_1].[UNIQUE_CONSTRAINT_NAME] AND
 [C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY
 [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [R].[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:

 {{{#!python
 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] [Microsoft][SQL
 Server Native Client 11.0]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

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2909>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2909: Reflection error: Connection is busy with results for anot

[Sqlalchemy-tickets] [sqlalchemy] #2909: Reflection error: Connection is busy with results for another command