Bug 1121798

Summary: some mysql "commands out of sync" errors may not be interpreted correctly as a "database disconnect" situation, sqlalchemy 0.8.4
Product: Red Hat OpenStack Reporter: Michael Bayer <mbayer>
Component: python-sqlalchemyAssignee: Michael Bayer <mbayer>
Status: CLOSED ERRATA QA Contact: Ami Jeain <ajeain>
Severity: unspecified Docs Contact:
Priority: low    
Version: 5.0 (RHEL 6)CC: aberezin, apevec, dnavale, lhh, mbayer, nbarcet, sclewis, yeylon
Target Milestone: z4Keywords: ZStream
Target Release: 5.0 (RHEL 7)   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: 0.8.4-2.el7ost Doc Type: Bug Fix
Doc Text:
Previously, the MySQL-Python DBAPI was observed under some circumstances using the ProgrammingError exception class to report on the 'command out of sync' errors, which is considered to be the case where a connection need to be thrown away; the SQLAlchemy dialect only expected this error to be emitted within the OperationalError class. As a result, in some cases a MySQL-Python connection that became corrupt would not signal to the SQLAlchemy engine that the pool of connections should be disposed, leading the engine not being able to proceed with new operations. With this update, the error handling scheme of the MySQL-Python dialect is modified to expect either the OperationalError or ProgrammingError exception class when testing for this particular class of error. As a result, the SQLAlchemy engine/connection pool now correctly disposes off its connections when a MySQL-Python ProgrammingError delivers the 'command out of sync' error code.
Story Points: ---
Clone Of:
: 1198774 (view as bug list) Environment:
Last Closed: 2015-04-16 14:37:16 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 1198774    

Description Michael Bayer 2014-07-21 22:10:49 UTC
Description of problem:

SQLAlchemy release 0.8.7 fixes an issue in the MySQL dialect, where a "commands out of sync" exception may not be interpreted correctly as a "database disconnect" situation.   The "commands out of sync" exception in MySQLdb is often a non-recoverable situation, and typically is the result of inappropriate concurrent access on a single connection object.  Because the connection is often unusable when this error occurs, we treat this as a disconnect, throwing out the connection and also refreshing the remaining connections stored in the connection pool.

The fix in issue #3101 adds ProgrammingError to the list of base exceptions searched for the given error code (2014).


SQLAlchemy issue: https://bitbucket.org/zzzeek/sqlalchemy/issue/3101

Comment 4 Ami Jeain 2015-03-31 15:31:38 UTC
ran the following python test code to verify:

import unittest
from sqlalchemy import create_engine
from sqlalchemy import exc


class MysqlFailureCase(unittest.TestCase):
    def setUp(self):
        self.engine = create_engine(
            "mysql+mysqldb://xxx:xxx@localhost/test")

    def test_mysql_operror(self):
        connection = self.engine.connect()
        raw_conn = connection.connection

        # use raw commands to place the connection into
        # a state where additional queries are not allowed
        raw_conn.query("select 1")
        raw_result = raw_conn.use_result()

        # use the SQLAlchemy connection to try to execute a statement;
        # this raises ProgrammingError on MySQLdb
        # (usually raises OperationalError on others)
        self.assertRaises(
            exc.ProgrammingError,
            connection.execute, "select 1"
        )

        # the engine and connection pool is flushed totally,
        # and on subsequent use reconnects and works again
        self.assertEqual(
            connection.scalar("select 1"), 1
        )


if __name__ == '__main__':
    unittest.main()

Comment 6 errata-xmlrpc 2015-04-16 14:37:16 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://rhn.redhat.com/errata/RHBA-2015-0825.html