Getting TimeoutError with SqlAlchemy

Showing 1-5 of 5 messages
Getting TimeoutError with SqlAlchemy charlax 9/11/12 4:52 AM
Hi,

I have to explicitly close the engine connection, otherwise I get a TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 error when using nosetest and sqlalchemy. I'm not using multiprocessing.

I'm following the SqlAlchemy documentation about how to join a Session into an external transaction. The only difference is that I'm using a scopedsession. Here is the offending code:


    import unittest

    from sqlalchemy import create_engine

    from myapp.mymodel import Session

    engine = create_engine(
        '<REDACTED>',
        echo = False,
        # To make it faster to fail, but also fails with the default options
        # pool_size=2,
        # max_overflow=0,
        # echo_pool="debug",
        # pool_timeout=10,
    )


    class MyTest(unittest.TestCase):

        def setUp(self):
            self.connection = engine.connect()  # Btw, not attaching as an attribute does not change anything

            # begin a non-ORM transaction
            self.trans = self.connection.begin()

            # bind an individual Session to the connection
            Session.configure(bind=self.connection)

            self.addCleanup(self._teardown)

        def _teardown(self):
            """Rollback the db.

            Added to the list of cleanup by setUp, so that subclasses do not have to
            call super() on tearDown.
            """

            # Rollback database
            self.trans.rollback()

            # Session must be closed BEFORE being removed
            Session.close()
            Session.remove()
            # If I don't do that, I get TimeOut
            # self.connection.close()

Here's the echo_pool debug output:

    2012-09-11 12:34:28,506 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bae1e20>
    2012-09-11 12:34:28,514 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bae1e20> checked out from pool
    2012-09-11 12:34:29,664 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bc61c20>
    2012-09-11 12:34:29,665 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bc61c20> checked out from pool
    2012-09-11 12:34:30,368 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bc9ea20>
    2012-09-11 12:34:30,369 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bc9ea20> checked out from pool
    2012-09-11 12:34:31,042 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bcd6820>
    2012-09-11 12:34:31,043 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bcd6820> checked out from pool
    2012-09-11 12:34:31,775 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bcbd820>
    2012-09-11 12:34:31,775 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bcbd820> checked out from pool
    2012-09-11 12:34:32,439 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bcc2220>
    2012-09-11 12:34:32,439 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bcc2220> checked out from pool
    2012-09-11 12:34:33,129 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd0e220>
    2012-09-11 12:34:33,129 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd0e220> checked out from pool
    2012-09-11 12:34:33,802 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd1e420>
    2012-09-11 12:34:33,802 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd1e420> checked out from pool
    2012-09-11 12:34:34,590 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bcf6a20>
    2012-09-11 12:34:34,590 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bcf6a20> checked out from pool
    2012-09-11 12:34:35,452 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd53420>
    2012-09-11 12:34:35,452 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd53420> checked out from pool
    2012-09-11 12:34:36,276 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd32420>
    2012-09-11 12:34:36,276 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd32420> checked out from pool
    2012-09-11 12:34:36,970 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd80420>
    2012-09-11 12:34:36,971 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd80420> checked out from pool
    2012-09-11 12:34:37,639 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bda9020>
    2012-09-11 12:34:37,640 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bda9020> checked out from pool
    2012-09-11 12:34:37,664 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bdc3c20>
    2012-09-11 12:34:37,664 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bdc3c20> checked out from pool
    2012-09-11 12:34:37,675 DEBUG sqlalchemy.pool.QueuePool Created new connection <_mysql.connection open to '127.0.0.1' at 7f938bd95e20>
    2012-09-11 12:34:37,675 DEBUG sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 7f938bd95e20> checked out from pool

I'm using Python 2.7.2, SQA 0.7.5, nosetests 1.1.3 and Pyramid.

Thanks for your help!

Charles
Re: [sqlalchemy] Getting TimeoutError with SqlAlchemy Michael Bayer 9/11/12 8:01 AM

On Sep 11, 2012, at 7:52 AM, charlax wrote:

Hi,

I have to explicitly close the engine connection, otherwise I get a TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 error when using nosetest and sqlalchemy. I'm not using multiprocessing.

I'm following the SqlAlchemy documentation about how to join a Session into an external transaction. The only difference is that I'm using a scopedsession. Here is the offending code:

    class MyTest(unittest.TestCase):

        def setUp(self):
            self.connection = engine.connect()  # Btw, not attaching as an attribute does not change anything

            # begin a non-ORM transaction
            self.trans = self.connection.begin()

            # bind an individual Session to the connection
            Session.configure(bind=self.connection)

            self.addCleanup(self._teardown)

        def _teardown(self):
            """Rollback the db.

            Added to the list of cleanup by setUp, so that subclasses do not have to
            call super() on tearDown.
            """

            # Rollback database
            self.trans.rollback()

            # Session must be closed BEFORE being removed
            Session.close()
            Session.remove()
            # If I don't do that, I get TimeOut
            # self.connection.close()

OK, so that's a small omission in the doc then, because yes you need the connection.close() there.  When you run the tests with unittest, unittest creates a new instance of your test class, which means all those test class instances will have self.connection present there and not returned to the pool.

doc fix in 5 minutes.

also remove() implies close() when using scoped_session.







--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4ZcTN0cHDnIJ.
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.

Re: [sqlalchemy] Getting TimeoutError with SqlAlchemy Charles-Axel Dein 9/11/12 8:09 AM
Ok great! Thanks for the doc fix.

Ok also for Session.remove(), I must admit Session.remove() is one of the part where the documentation is quite elliptical...
Re: [sqlalchemy] Getting TimeoutError with SqlAlchemy Michael Bayer 9/11/12 8:27 AM

On Sep 11, 2012, at 11:09 AM, Charles-Axel Dein wrote:

Ok great! Thanks for the doc fix.

Ok also for Session.remove(), I must admit Session.remove() is one of the part where the documentation is quite elliptical...

I rewrote a lot of it last night, give it a try:


Re: [sqlalchemy] Getting TimeoutError with SqlAlchemy Charles-Axel Dein 9/11/12 8:36 AM
Wow. You're amazing. Far clearer now. Thanks a lot!