debugging sqlite/thread errors

[Jonathan Vanasco]

I am working on a rewrite of a package to support ACME2 and Python3, and somehow introduced a thread unsafe error.

This is happening in a web application, and I have absolutely no clue where I introduced the bug or what is causing it.

Does anyone know of a good way to bugtest for this sort of situation?  Is there an event that might pick it up, or a good place to drop some pdb breakpoints in the sqlalchemy source?

2020-02-24 17:47:25,325 ERROR [sqlalchemy.pool.impl.NullPool:702][waitress] Exception during reset or similar

Traceback (most recent call last):

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 693, in _finalize_fairy

    fairy._reset(pool)

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 880, in _reset

    pool._dialect.do_rollback(self)

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 538, in do_rollback

    dbapi_connection.rollback()

ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140735142240256 and this is thread id 123145319129088

2020-02-24 17:47:25,327 ERROR [sqlalchemy.pool.impl.NullPool:273][waitress] Exception closing connection <sqlite3.Connection object at 0x105c2bb10>

Traceback (most recent call last):

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 270, in _close_connection

    self._dialect.do_close(connection)

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 544, in do_close

    dbapi_connection.close()

ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140735142240256 and this is thread id 123145319129088

2020-02-24 17:47:25,467 INFO  [sqlalchemy.engine.base.Engine:731][waitress] ROLLBACK

[Mike Bayer]

it looks like garbage collection.   SQLAlchemy 2.0 won't do that, it will just warn that you left a connection open somewhere.   we would hope that the SQLite gc routine doesn't raise like that.

I'm assuming this is an error you see in the logs but isn't actually making anyone's request fail, otherwise it's something different.  Anyway you want to make sure you close connections.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e213a07e-e1ee-4da6-981f-cc539b6eeb15%40googlegroups.com.

[Jonathan Vanasco]

On Monday, February 24, 2020 at 6:23:32 PM UTC-5, Mike Bayer wrote:

it looks like garbage collection.   SQLAlchemy 2.0 won't do that, it will just warn that you left a connection open somewhere.   we would hope that the SQLite gc routine doesn't raise like that.

Thanks Mike, this helped immensely.

I traced this to happening somewhere in the interplay of pyramid/pyramid_tm/transaction.  It only seems to happen on the first request, and seems to be an artifact of the testing environment *before* i get to close my connections. 

[Jonathan Vanasco]

> I'm assuming this is an error you see in the logs but isn't actually making anyone's request fail, otherwise it's something different. 

Yes, that is what is happening.

AH HA, I GOT IT!

The application startup routine had this block in it:

   dbEngine = ...

   dbSession = ...
   ... setup ...
   dbEngine.dispose()

this fixed it:

   dbEngine = ...

   dbSession = ...
   try:

       ... setup ...

   finally:
       dbSession.close()

   dbEngine.dispose()