InternalError: current transaction is aborted, commands ignored until end of transaction block...

[tom]

Hey,

I'm applying finishing touches to my web app before rolling it out,
when this strange error occurs:

sqlalchemy.exc.InternalError
InternalError: (InternalError) current transaction is aborted,
commands ignored until end of transaction block 'SELECT count(1) AS
count_1 \nFROM personal_message \nWHERE personal_message.receiver_id =
%(receiver_id_1)s AND personal_message.read = %
(read_1)s' {'receiver_id_1': 1, 'read_1': False}

Now, this hasn't happened before, and as far as I can tell I haven't
touched the concerning code recently. Googling the error doesn't turn
up anything useful, I'm not even sure it has to do with Sqlalchemy
(I'm using version 0.5.8, with Postgres).

Any pointers in the right direction would be greatly appreciated.

- Tom

[Michael Bayer]

when an integrity error is issued by Postgresql, such as attempting to insert a primary key that already exists, the ongoing transaction is then marked as invalid. If you try to do anything else in the transaction, you get that error.

The underlying reason from a Python interaction perspective is that the session or connection you're using has encountered an error, but the transaction was not rolled back, and the application proceeded with that same connection. In a web applciation, the simplest cause of this is that one web request is re-using the session or connection from a previous request that raised an error. Theres myriad other variants of that sequence but that is the most ordinary one.

[tom]

First, thanks for that quick answer, that explains it. I turned
autocommit on, and it works again.

That leads me to this question: I do have session.commit() sprinkled
throughout my code without any "except: rollback()" blocks, can that
lead to problems down the line? I had the impression that rollback is
called automatically anytime a commit fails, but I'm not so sure
anymore.

[Michael Bayer]

On Sep 29, 2010, at 11:03 AM, tom wrote:

> First, thanks for that quick answer, that explains it. I turned
> autocommit on, and it works again.
>
> That leads me to this question: I do have session.commit() sprinkled
> throughout my code without any "except: rollback()" blocks, can that
> lead to problems down the line? I had the impression that rollback is
> called automatically anytime a commit fails, but I'm not so sure
> anymore.

If the session has autocommit on, then the commit() calls are moot (and slightly wasteful since it opens a no-op tranasction then closes it). The rollback is also always implicit, since in fact every operation, not just errors, results in connection resources being restored to the connection pool when the operation is complete, which unconditionally performs a rollback.

"autocommit" isn't the current "mainstream" way to do things, usually for web apps we recommend one session per request, which maintains a transaction until commit() or rollback() is called, using an enclosure scheme like that described at http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session .

>
>
> On Sep 29, 4:07 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Sep 29, 2010, at 7:13 AM, tom wrote:
>>
>>
>>> Hey,
>>
>>> I'm applying finishing touches to my web app before rolling it out,
>>> when this strange error occurs:
>>
>>> sqlalchemy.exc.InternalError
>>> InternalError: (InternalError) current transaction is aborted,
>>> commands ignored until end of transaction block 'SELECT count(1) AS
>>> count_1 \nFROM personal_message \nWHERE personal_message.receiver_id =
>>> %(receiver_id_1)s AND personal_message.read = %
>>> (read_1)s' {'receiver_id_1': 1, 'read_1': False}
>>
>>> Now, this hasn't happened before, and as far as I can tell I haven't
>>> touched the concerning code recently. Googling the error doesn't turn
>>> up anything useful, I'm not even sure it has to do with Sqlalchemy
>>> (I'm using version 0.5.8, with Postgres).
>>
>>> Any pointers in the right direction would be greatly appreciated.
>>
>> when an integrity error is issued by Postgresql, such as attempting to insert a primary key that already exists, the ongoing transaction is then marked as invalid. If you try to do anything else in the transaction, you get that error.
>>
>> The underlying reason from a Python interaction perspective is that the session or connection you're using has encountered an error, but the transaction was not rolled back, and the application proceeded with that same connection. In a web applciation, the simplest cause of this is that one web request is re-using the session or connection from a previous request that raised an error. Theres myriad other variants of that sequence but that is the most ordinary one.
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[tom]

Thank you very much, that was very helpful!

On Sep 29, 5:27 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 29, 2010, at 11:03 AM, tom wrote:
>
> > First, thanks for that quick answer, that explains it. I turned
> > autocommit on, and it works again.
>
> > That leads me to this question: I do have session.commit() sprinkled
> > throughout my code without any "except: rollback()" blocks, can that
> > lead to problems down the line? I had the impression that rollback is
> > called automatically anytime a commit fails, but I'm not so sure
> > anymore.
>
> If the session has autocommit on, then the commit() calls are moot (and slightly wasteful since it opens a no-op tranasction then closes it).     The rollback is also always implicit, since in fact every operation, not just errors, results in connection resources being restored to the connection pool when the operation is complete, which unconditionally performs a rollback.
>

> "autocommit" isn't the current "mainstream" way to do things, usually for web apps we recommend one session per request, which maintains a transaction until commit() or rollback() is called, using an enclosure scheme like that described athttp://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-context....

[Mark Sharp]

I am trying to establish a connection to a database provided by Microsoft SQL Server 2008 on our local network. The traceback seems to indicate that pyodbc is not being found, but this is my first attempt to use SQLAlchemy with a database other than sqlite so there are many holes in my understanding.

The server is "hellcat"
The instance is "cohiba"
The user is "userA"
The password is "usera$dbo"

I installed pyodbc
benjy:pyodbc-2.1.8 msharp$ python setup.py build install
running build
running build_ext
building 'pyodbc' extension
creating build
creating build/temp.macosx-10.6-universal-2.6
...
Installed /Library/Python/2.6/site-packages/pyodbc-2.1.8-py2.6-macosx-10.6-universal.egg
Processing dependencies for pyodbc==2.1.8
Finished processing dependencies for pyodbc==2.1.8

benjy:python msharp$ python
Python 2.6.1 (r261:67515, Feb 11 2010, 00:51:29)
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.6.4'
>>> mssql_db = sqlalchemy.create_engine('mssql+pyodbc://userA:usera$dbo@[hellcat\cohiba]') # I have tried this with and without the brackets (that is, ...dbo@hellcat\cohiba and ...dbo@[hellcat\cohiba])
>>> connection = mssql_db.connect()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line 1814, in connect
return self.Connection(self, **kwargs)
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line 835, in __init__
self.__connection = connection or engine.raw_connection()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line 1877, in raw_connection
return self.pool.unique_connection()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 143, in unique_connection
return _ConnectionFairy(self).checkout()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 370, in __init__
rec = self._connection_record = pool.get()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 214, in get
return self.do_get()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 733, in do_get
con = self.create_connection()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 148, in create_connection
return _ConnectionRecord(self)
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 254, in __init__
self.connection = self.__connect()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/pool.py", line 320, in __connect
connection = self.__pool._creator()
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/strategies.py", line 76, in connect
return dialect.connect(*cargs, **cparams)
File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/default.py", line 249, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) (SQLDriverConnectW)') None None

Mark Sharp

[Michael Bayer]

you first have to get your ODBC config set up. Try out "iodbctest", which should be on your mac, and see if you can establish a connection to the database. It matters if you're using FreeTDS or some other driver system.

FreeTDS docs/etc. are at http://www.freetds.org/ .

[Mark Sharp]

I am using the Actual Technologies SQL Server driver and am having no trouble connecting within R using the RODBC library. I think my trouble is with not getting pyodbc set up correctly. I have not seen anything that is helpful (to me) for pyodbc.

Mark