Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by TragedyStruck, Aug 11, 2017.

  1. TragedyStruck

    TragedyStruck Registered

    Joined:
    Aug 11, 2017
    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    1
    Location:
    Sweden
    cPanel Access Level:
    Website Owner
    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:
    Code:
    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:
    Code:
    [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:
    Code:
    @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!
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,371
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
  3. TragedyStruck

    TragedyStruck Registered

    Joined:
    Aug 11, 2017
    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    1
    Location:
    Sweden
    cPanel Access Level:
    Website Owner
    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:
    Code:
    SQLALCHEMY_POOL_RECYCLE=90
    To figure out the timeout value my hosting provider was using I ran this query:
    Code:
    SHOW SESSION VARIABLES LIKE 'wait_timeout';
    Resulting in this:
    Code:
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | wait_timeout  | 100   |
    +---------------+-------+
     
    cPanelMichael likes this.
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,371
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

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

Share This Page