MySQL OperationalError when running a Flask server (Apache) for some days

Question

I have a Flask server under Apache which I'm using as a Rest API for an app and when the server is running for 2 - 3 days it suddenly stops working and raises OperationalError: MySQL Connection not available.

The error always happens at the login method because it's the first called when the app opens (but all methods follow the same pattern).

This is the login method:

@app.route(LOGIN_API_URL, methods=['POST'])
def login():
    if (request.method == 'POST'):
        cursor = connection.cursor(buffered=True, dictionary=True)
        cursor.execute('select * from users where username = %s', (request.form['username'],))
        user = cursor.fetchone()
        if user is None or user['password'] != str(request.form['password']):
            abort(403)
        else:
            cursor.execute('update users set last_login = (%s) where user_id = %s', str(int(round(time.time() * 1000))), user['user_id'],)
            utils.safe_commit(connection, cursor)
            return utils.sanitize_response({'status':200, 'message':'Logged in'})

Both safe_commit and sanitize_response follows:

def sanitize_response(response, is_array=False):
    if response is None:
        return '[]' if is_array else '{}'
    else:
        return jsonify(response)

def safe_commit(connection, cursor):
    try:
        connection.commit()
    except:
        connection.rollback()
    finally:
        cursor.close()

At first I thought the problem was happening because I wasn't using buffered=True in a cursor that is calling fetchone method. But I added that parameter after reading this.

This is my wsgi file:

#!/usr/bin/python
import sys
sys.path.append("/var/www/protestr/")
from protestr import app as application

This is my sites-available conf file (I wanted to say that I have tried a lot of combinations of both threads and processes parameters and this combination is the one that keeps the server running for the most time, usally 2 - 3 days):

<VirtualHost *:80>
    ServerName protestr.tk
    DocumentRoot /var/www/protestr/

    WSGIDaemonProcess protestr user=www-data group=www-data processes=2 threads=25
    WSGIScriptAlias / /var/www/protestr/protestr.wsgi

    <Directory /var/www/protestr>
        WSGIProcessGroup protestr
        WSGIApplicationGroup %{GLOBAL}
        Require all granted
    </Directory>
</VirtualHost>

These are the contents of the error.log file:

[Fri May 12 03:34:14.967624 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727] [2017-05-12 03:34:14,963] ERROR in app: Exception on /api/v1/users/login [POST]
[Fri May 12 03:34:14.967812 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727] Traceback (most recent call last):
[Fri May 12 03:34:14.967861 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1982, in wsgi_app
[Fri May 12 03:34:14.967900 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     response = self.full_dispatch_request()
[Fri May 12 03:34:14.967937 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1614, in full_dispatch_request
[Fri May 12 03:34:14.967973 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     rv = self.handle_user_exception(e)
[Fri May 12 03:34:14.968007 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1517, in handle_user_exception
[Fri May 12 03:34:14.968043 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     reraise(exc_type, exc_value, tb)
[Fri May 12 03:34:14.968076 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1612, in full_dispatch_request
[Fri May 12 03:34:14.968111 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     rv = self.dispatch_request()
[Fri May 12 03:34:14.968144 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1598, in dispatch_request
[Fri May 12 03:34:14.968179 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     return self.view_functions[rule.endpoint](**req.view_args)
[Fri May 12 03:34:14.968251 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/var/www/protestr/protestr.py", line 89, in login
[Fri May 12 03:34:14.968290 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     cursor = connection.cursor(buffered=True, dictionary=True)
[Fri May 12 03:34:14.968326 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]   File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 809, in cursor
[Fri May 12 03:34:14.968363 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727]     raise errors.OperationalError("MySQL Connection not available.")
[Fri May 12 03:34:14.968399 2017] [wsgi:error] [pid 18673:tid 2849002544] [remote 192.168.1.139:25727] OperationalError: MySQL Connection not available.

---

ADDITIONAL INFORMATION:

I'm running Apache/2.4.10 under armbian (Debian) in a Banana Pi.

I really don't know why the server stops after running for a while, I think I have tried almost everything.

---

EDIT: I've added also cursor.close() before throwing the 403 error in the login method. But this is not relevant as I'm the only one logging in the app and I'm always entering the correct credentials.

EDIT 2: As @stamaimer told me, if I add connection.ping() before getting any cursor, it works well, but this approach seems like a hacky way to me and I don't know if it's a good solution or even why the MySQL server is dropping the connection.

Answer 1

This example is somehow crude, but hopefully shows the logic around handling of disconnections. Specifics depend on the way connections are obtained in a particular framework.

The code below assumes the use of retry; you can use different retry logic if needed.

It also assumes you're using a connection pool with your app, provided by the framework. Usually you can ask it to reconnect, or at least to close idle connections, so that another request for a connection would create a new one.

def reconnect_on_failure(func):
    @retry(OperationalError, delay=0.25, backoff=1.5, max_delay=5)
    @wraps(func)
    def reconnecting_func(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except OperationalError as e:
            if 'connect' in e.msg.lower():
                force_reconnection_somehow()  # Look at your connection pool API.
                raise  # We want to retry on it
            raise Exception('Unhandled MySQL error', e)  # Will not retry.
    return reconnecting_func


@reconnect_on_failure
def something(...):
    connection = get_connecton_somehow()  # Look at the framework API.
    # A transaction implicitly begins with the first statement executed.
    cursor = connection.cursor()
    result = cursor.execute(...)  # do stuff
    connection.commit()

Instead of the overly-broad Exception, you can use a narrower class, e.g. specific for your app; the idea is that raising anything but OperationalError will not trigger retries and it will raise an Exception immediately to report a problem.

EDIT from Grender: I've added @wraps decorator in order to avoid an AssertionError as seen here.

Answer 2

Building onto @9000s answer, ping should work fine when called with parameter reconnect=True, see the corresponding code. This will issue a ping, and in case of a failed ping, try to reconnect to the database.

While this should fix the error, you should dig a little to find the underlying problem, which is a non-functional connection to MySQL.

As already mentioned, this can result from multiple sources, so maybe you can use the following list to guide your search:

• Are there other error messages in the log, before the one you posted?
• Are you connecting to MySQL via TCP/IP or via socket? Maybe try the other variant, if one works and the other does not, it might help to identify the problem.
• Increase max_timeout to a large value, just to make sure if it has an effect.
• You may also want to increase max_allowed_packet, as this helped in other cases.
• Check the MySQL server logs for potential problems with lost connections.
• Also check the syslog of the operating system, because MySQL might get killed due to low memory, which can be fixed by setting better values in config.

Hope this helps you to find the underlying cause.