Forums

Hi, I am using pool_class=NullPool in create_engine. but the Connection is still not getting released immidiately after doing connection.close(). It is still getting released after 300 seconds. Can you pls let me know how to accomplish immediate release of Connection. I do not want to use connection pooling.

    with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
     ssh_username='arguptaHello', ssh_password='******',
     remote_bind_address=('arguptaHello.mysql.pythonanywhere-services.com', 3306)
    ) as tunnel:
        port = str(tunnel.local_bind_port)
        conn_addr = 'mysql+mysqlconnector://' + user + ':' + passwd + '@' + host + ':' + port + '/' + db
        print("[SQL] Trying to connect to " + conn_addr)
        #engine =create_engine(conn_addr, pool_recycle=280)
        engine =create_engine(conn_addr,poolclass=NullPool,echo=True)
        dbConnection = engine.connect()
        try:
            dbConnection.execute("DELETE FROM ******* where x=%s",(y,))
            dbConnection.execute("INSERT INTO " + tblName +"(y,jsonm,status)"+ " VALUES (%s,%s,%s)",(id,json.dumps(final,default=str),status))
        except Exception as e:
            print(e)
        finally:
            dbConnection.close
            dbConnection.close()
            engine.dispose()

Are you sure that your finally block isn't raising an exception before you close the connection? That dbConnection.close looks strange. Also, how are you determining that the connection is still open?

The dbConnection.close is a typo from my side. I looked at the process list (MySQL Console)and all the processes the Time until it reaches close to 300 is not removed from the list. I expect the connection to be removed far more quickly so that it is available for new user connection.

My requirement is that my API will be called once in more than 150 seconds for the next 4-5 hours continously.

My requirement is that my API will be called once in more than 150 seconds for the next 4-5 hours continuouslyby one User only** . That is why I am a bit confused as to why max_user_connections is reached.

Something is opening a connection and not closing it. When do you get max_user_connections error?

I checked for open connections . Do not seem to find any I have used the same process of closing the connection and disposing the engine. I get the max_user_connections when I hit the API for data within 30 seconds interval and the number of hits start exceeding 6.

Maybe add some logging to your code just before you close the connection to see if it's getting there.

Is there a way to reduce the connection release after close may be using max_timeout. Can this be done 'SET GLOBAL wait_timeout=60' . Will this help

I wouldn't have thought so. What happens if you allow your engine to create a pool, but set its size explicitly to one?

Yes you were right. Your suggestion of checking all the connections getting closed I missed out in one of the connections I was using pool_recycle =280 and that was getting recycled after 300 seconds. Thank you very much for your help. Appreciate that.

No problem, glad we could help!

I would now like to ask one more question . When we set pool_recycle=280 . should we close the connection , because anyways the connection will be recycled after the specified time in pool_recycle. If the above is correct then is it correct to say that I have to just dispose the engine before program concludes.

I'm not sure if I understand you correctly, but probably you are right. Looks like you can easily make an experiment.