SOLVED "Lost connection to MySQL server during query" with Python App

[TragedyStruck]

I've created a Python App and MySQL database all through cPanel. The Python app uses Flask, SQLAlchemy and PyMySQL to connect to a DB_URI like "mysql+pymysql://un:pw@host/db".

Getting it up and running with Passenger works perfectly, but I'm randomly getting the error message:

OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT user.id AS user_id, user.clinic_id AS user_clinic_id, user.uuid AS user_uuid \nFROM user']

Now I've seen some suggest on StackOverflow that it could be a timeout thing, but can I know the timeout value when creating the DB from cPanel? In any case I've had queries work one minute and a few minutes later not working:

[2017-08-11 10:39:57,222] INFO [sqlalchemy.engine.base.Engine._begin_impl:679] BEGIN (implicit)
[2017-08-11 10:39:57,222] INFO [sqlalchemy.engine.base.Engine._execute_context:1140] SELECT user.id AS user_id, user.clinic_id AS user_clinic_id, user.uuid AS user_uuid
FROM user
[2017-08-11 10:39:57,222] INFO [sqlalchemy.engine.base.Engine._execute_context:1143] {}
[2017-08-11 10:39:57,224] INFO [sqlalchemy.engine.base.Engine._rollback_impl:699] ROLLBACK
[2017-08-11 10:43:08,063] INFO [sqlalchemy.engine.base.Engine._begin_impl:679] BEGIN (implicit)
[2017-08-11 10:43:08,063] INFO [sqlalchemy.engine.base.Engine._execute_context:1140] SELECT user.id AS user_id, user.clinic_id AS user_clinic_id, user.uuid AS user_uuid
FROM user
WHERE user.uuid = %(uuid_1)s
[2017-08-11 10:43:08,063] INFO [sqlalchemy.engine.base.Engine._execute_context:1143] {u'uuid_1': '050a6f7e-73fe-48d1-a322-4ae842f536c2'}
[2017-08-11 10:43:08,068] ERROR [root.internal_server_error:136] (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT user.id AS user_id, user.clinic_id AS user_clinic_id, user.uuid AS user_uuid \nFROM user \nWHERE user.uuid = %(uuid_1)s'] [parameters: {u'uuid_1': '050a6f7e-73fe-48d1-a322-4ae842f537c2'}]

Here the one at 10:39 works, but the error occurs at 10:43. I've also set
SQLALCHEMY_POOL_RECYCLE=1800 in my config, but doesn't seem to help. I've also seen some suggestions that it might occur for very large queries, but as you can see these are very small.

It happens quite frequently and apparently after a short idle time, while several very consecutive queries works fine.

An example route that might fail:

@my_app.route("/api/user", methods=['GET'])
def get_user_list():
    result = User.query.all()
    return jsonify([x.serialize() for x in result])

I'm faaairly new at Python/Flask/Passenger so anything I might've missed in handling the queries (open/close?) or other help and hints are appreciated!

 

[cPanelMichael]

Hello,

Do you have root access to the affected system in order to view the MySQL error log when encountering the timeout? If not, have you contacted your hosting provider to see if they can take a closer look to see if it's related to the MySQL configuration values in the /etc/my.cnf file on the system?

Thank you.

 

[TragedyStruck]

This has been resolved. For people who might be in trouble, my solution was this:

It was a timeout issue. Specifically my hosting provider had set a very low WAIT_TIMEOUT for their MySQL server. The MySQL-default is 28800 (8 hours), while they had set it to 100 (1 minute 40 seconds). Since I was using SQLAlchemy for ORM it had to reflect that low value in order to not fail when trying to use a timed out connection.

In my SQLAlchemy config I set this value:

SQLALCHEMY_POOL_RECYCLE=90

To figure out the timeout value my hosting provider was using I ran this query:

SHOW SESSION VARIABLES LIKE 'wait_timeout';

Resulting in this:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 100   |
+---------------+-------+

 

[cPanelMichael]

Hello,

I'm happy to see the issue is now solved. Thank you for updating us with the outcome.

 

[venkatesh123]

Hello,
SHOW SESSION VARIABLES LIKE 'wait_timeout'; = 28800
SQLALCHEMY_POOL_RECYCLE = 28700
but still, I am getting the same error,I am using the flask sqlalchemy running a cronjob which will trigger every hour 10th minute (* 10) at every day 10' o clock I am getting
(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
can anyone please help?

 

[MichellePaige]

This is very helpful