Forums

MySQL Connection not available after some minutes

Hello! I'm using, Flask and sqlalchemy in my app and experiencing a disconnection problem.

Everything runs fine for some minutes, but after that i start gettting 

sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.

I configured the pool_recycle attribute from sqlalchemy but nothing changed. Here is a part of my app's code:

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}         /{databasename}".format(
username="XXXXX",
password="XXXXXXXXXXXX",
hostname="XXXXXXXXXXXXXX,
databasename="XXXXXXXXXXXXXX",
)
engine = create_engine(SQLALCHEMY_DATABASE_URI, pool_recycle=280)

DBSession = sessionmaker(bind=engine)
session = DBSession()

app = Flask(__name__)
app.config['SQLALCHEMY_POOL_RECYCLE'] = 280
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

What am I missing here ? Any other configurations? Thank you.

deleted-user-5545867 | 2 posts | May 17, 2019, 1:27 p.m. | permalink

so when you reload your webapp it runs fine again for a bit and then breaks again?

Staff conrad | 4232 posts | PythonAnywhere staff | May 17, 2019, 3:45 p.m. | permalink

Yes, if I reload it runs for some minutes and then breaks

deleted-user-5545867 | 2 posts | May 17, 2019, 4:36 p.m. | permalink

If you're sure that you're actually running the code that adds the pool_recycle to the engine and that you're always querying the database through that engine, then check your logs to make sure that the exception you're seeing is the cause of the problem and not a symptom. Sometimes other errors can break a database connection. In that case you may see other exceptions that lead to the final exception where the connection is broken.

Staff glenn | 9718 posts | PythonAnywhere staff | May 18, 2019, 10:42 a.m. | permalink

Facing the same issue here. added pool_recycle in the way python_anywhere suggests. Since I am a beginner with flask, not sure how to debug. Did you fix it later?

deleted-user-8377759 | 4 posts | Dec. 7, 2020, 2:39 p.m. | permalink

The other poster has had their account deleted, so they're unlikely to respond. My advice to them should also apply to you.

Staff glenn | 9718 posts | PythonAnywhere staff | Dec. 7, 2020, 5:56 p.m. | permalink

Hi Glenn, since I'm not sure how to debug, let me explain what I'm doing so that you can provide me with leads. In main/ _init _.py, I do import config from object:

db = SQLAlchemy()
def create_app(config_name):
    app.config.from_object(config_by_name[config_name])
    db.init_app(app)

Using this method, I don't see anyhow I can add an engine (but some sources suggested this is the ideal way to initialize when using application factory pattern). No, my config has this property:

class Config:
    #... other config
    SQLALCHEMY_DATABASE_URI = DB_URI
    SQLALCHEMY_POOL_RECYCLE = 280

and this is set to a map, so that I can choose my preferred config. However, this config is common and according to SQLAlchemy docs, should be registered by SQLAlchemy as per this doc. Since my version is 2.3.2, it should use the variable as shown below (at least that is what I expect). I have been stuck on this for a while now, please help me out if you could.

deleted-user-8377759 | 4 posts | Dec. 8, 2020, 2:13 p.m. | permalink

As far as I can see SQLALCHEMY_POOL_RECYCLE = 280 looks good. What errors are you getting?

Staff fjl | 4614 posts | PythonAnywhere staff | Dec. 8, 2020, 3:34 p.m. | permalink

Here is the error. It happens exactly after (>=) 5 mins have elapsed since my last db statement executed. If I execute anything before that, say 4 min 30 sec, it runs fine.

    2020-12-09 05:50:21,818: Exception on /api/registration/verify [POST]
    Traceback (most recent call last):
      File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1205, in _execute_context
        context = constructor(dialect, self, conn, *args)
      File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 806, in _init_compiled
        self.cursor = self.create_cursor()
      File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1162, in create_cursor
        return self._dbapi_connection.cursor()
      File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 1000, in cursor
        return self.connection.cursor(*args, **kwargs)
      File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 541, in cursor
        raise errors.OperationalError("MySQL Connection not available.")
    mysql.connector.errors.OperationalError: MySQL Connection not available.
**NO MATCH**
The above exception was the direct cause of the following exception:
**NO MATCH**
Traceback (most recent call last):
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask_restplus/api.py", line 313, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask/views.py", line 89, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask_restplus/resource.py", line 44, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/sbsatter/lenden-api/app/main/controller/registration_controller.py", line 106, in post
    return verify_email(request.json)
  File "/home/sbsatter/lenden-api/app/main/service/registration_service.py", line 196, in verify_email
    user_reg = get_user_registration(public_id)
  File "/home/sbsatter/lenden-api/app/main/service/user_service.py", line 45, in get_user_registration
    return UserRegistration.query.filter_by(public_id=public_id).first_or_404()
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 430, in first_or_404
    rv = self.first()
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3397, in first
    ret = list(self[0:1])
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3171, in __getitem__
    return list(res)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3503, in __iter__
    return self._execute_and_instances(context)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3528, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1014, in execute
    return meth(self, multiparams, params)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1207, in _execute_context
    self._handle_dbapi_exception(
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    util.raise_(
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1205, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 806, in _init_compiled
    self.cursor = self.create_cursor()
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1162, in create_cursor
    return self._dbapi_connection.cursor()
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 1000, in cursor
    return self.connection.cursor(*args, **kwargs)
  File "/home/sbsatter/lenden-api/venv/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 541, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.
deleted-user-8377759 | 4 posts | Dec. 9, 2020, 5:53 a.m. | permalink

300s is the timeout for idle connections on our database server, but your orm settings should prevent that. Is it possible that you are somehow keeping idle connections open manually?

Staff fjl | 4614 posts | PythonAnywhere staff | Dec. 9, 2020, 8:42 a.m. | permalink

Should not be the case, since SQLAlchemy is handling all the connections and I didn't encounter this issue in other local/google cloud environment. However, I still want to debug how many connections are in the pool but haven't really found a lead that would give me this information.

deleted-user-8377759 | 4 posts | Dec. 11, 2020, 5:50 a.m. | permalink

Quick google returns something like that.

Staff fjl | 4614 posts | PythonAnywhere staff | Dec. 11, 2020, 9:11 a.m. | permalink