Getting 'MySQL server has gone away' when using mysql backend for ciwatch

Bug #1512476 reported by Mikhail S Medvedev on 2015-11-02
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
ciwatch
High
Mikhail S Medvedev

Bug Description

After ciwatch has been up for awhile without web requests, two errors pop up:
    MySQL server has gone away
 and then
    StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT projects.id AS projects_id, projects.name AS projects_name \nFROM projects ORDER BY projects.name'] [parameters: [immutabledict({})]

First hit returns this article https://mofanim.wordpress.com/2013/01/02/sqlalchemy-mysql-has-gone-away/.
The fix would be to do a better session management as described in
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications.

Changed in ciwatch:
importance: Undecided → High
assignee: nobody → Mikhail S Medvedev (msmedved)
Changed in ciwatch:
status: New → Confirmed

Fix proposed to branch: master
Review: https://review.openstack.org/245550

Changed in ciwatch:
status: Confirmed → In Progress

Reviewed: https://review.openstack.org/245550
Committed: https://git.openstack.org/cgit/openstack-infra/ciwatch/commit/?id=68f1910e7774780f4279a30ce1b31130cc3e2c32
Submitter: Jenkins
Branch: master

commit 68f1910e7774780f4279a30ce1b31130cc3e2c32
Author: Mikhail S Medvedev <email address hidden>
Date: Sat Nov 14 18:51:30 2015 -0600

    Use thread-local SQLAlchemy sessions

    There are a few problems with how SQLAlchemy session is currently used.

    First, there is global, non-thread-safe (see [1]) session instantiated
    in db.py. That makes it hard to ensure session is properly closed when
    there is no need to keep them open (e.g. in-between Flask requests). We
    also get stale data when we do queries without invalidating the
    long-living session. The fix is to use scoped_session, as described in
    [2], and by using new session for each Flask request, which is ensured
    by using Session.remove() at the end of each request.

    Another problem manifests when MySQL backend is used. During long idle
    time, MySQL would close inactive connections. And because SQLAlchemy
    does maintain a pool of connections open independently from session,
    once we try to use it, we'll see 'MySQL server went away' and crash.
    This further made worse by single global session that then gets stuck,
    not being able to process any additional transactions. The fix is to
    set SQLAlchemy pool_recycle to one hour, which is less than MySQL
    interactive timeout.

    In the future, instead of continuing to manage Flask with SQLAlchemy by
    hand, migration to Flask-SQLAlchemy extension should be considered, [3].

    [1] http://docs.sqlalchemy.org/en/rel_1_0/orm/session_basics.html#is-the-session-thread-safe
    [2] http://docs.sqlalchemy.org/en/rel_1_0/orm/contextual.html#unitofwork-contextual
    [3] https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application

    Closes-Bug: #1512476

    Change-Id: I78a7f3f0715c5aa71d73f9c496ba3b07208674b9

Changed in ciwatch:
status: In Progress → Fix Committed
Changed in ciwatch:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers