Is MySQL 5.5 supported by SQLAlchemy? (Ubuntu 12.04)

[Mike B]

Hello everyone,

Has anyone successfully run SQLAlchemy 0.7.8 or .9 with MySQL 5.5? How about in Ubuntu 12.04? ...with MySQL-python 1.2.3 or a 1.2.4beta?

I ask because Ubuntu 12.04 rescinds package manager support for MySQL 5.1, forcing us to upgrade to 5.5. Last week I experimentally upgraded a very stable server from {Ubuntu 11.10, MySQL 5.1, InnoDB, Python 2.7.2} to {Ubuntu 12.04, MySQL 5.5, InnoDB, Python 2.7.3}. Unfortunately, ever since the upgrade I have seen sporadic errors like this one:

Module sqlalchemy.engine.default:606 in create_cursor view
Module MySQLdb.connections:243 in cursor view
StatementError: 'Connection' object has no attribute 'cursorclass' (original cause: AttributeError: 'Connection' object has no attribute 'cursorclass') 'SELECT DATABASE()' []
(Python 2.7.3, SQLAlchemy 0.7.8, MySQL-python 1.2.3, MySQL 5.5, Pylons 1.0, mod-wsgi, apache2)

The server will run normally for an hour or two before this error arises. Each time I hit this error, SHOW FULL PROCESSLIST shows a leaked connection. Subsequent attempts to use SQLAlchemy within the same process die with the same error, leaking one additional connection per error until the server's max connections limit is exceeded, resulting in this error:

Module sqlalchemy.engine.default:281 in connect view
Module MySQLdb:81 in Connect view
Module MySQLdb.connections:187 in __init__ view
OperationalError: (OperationalError) (1040, 'Too many connections') None None

At that point I am forced to restart mysql and apache2, and normal service resumes for a random interval, perhaps an hour or two. Then the SELECT DATABASE error returns, and I restart again.

The SQLAlchemy 0.7.9 docs state that "SQLAlchemy supports 6 major MySQL versions: 3.23, 4.0, 4.1, 5.0, 5.1 and 6.0, with capabilities increasing with more modern servers." The docs do not explicitly state that 5.5 is no good, and I haven't read any horror stories on the list. This leads me to hope that the docs are out of date, and 5.5 might now be supported.

Before I rip out my MySQL 5.5 installation by the roots and built 5.1 from source, could somebody please confirm that 5.5 is positively unsupported by SQLAlchemy? If 5.5 is unsupported, are there plans to support it in the near future? Can anybody describe the failures I should expect with SQLAlchemy? Should I attribute my error to SQLAlchemy, or is it more likely to be MySQL-python's fault?

I appreciate any advice you all might offer.

MB

P.S. Here is the full stacktrace:
Module foo.controllers:176 in __before__ view
>> c.sauser = User.find_first(domain_id=unicode(remote_user))
Module bar.model:120 in find_first view
>> return cls.filter(*args, **kw).first()
Module sqlalchemy.orm.query:2156 in first view
Module sqlalchemy.orm.query:2023 in __getitem__ view
Module sqlalchemy.orm.query:2227 in __iter__ view
Module sqlalchemy.orm.query:2240 in _execute_and_instances view
Module sqlalchemy.orm.query:2231 in _connection_from_session view
Module sqlalchemy.orm.session:730 in connection view
Module sqlalchemy.orm.session:736 in _connection_for_bind view
Module sqlalchemy.engine.base:2490 in contextual_connect view
Module sqlalchemy.pool:224 in connect view
Module sqlalchemy.pool:387 in __init__ view
Module sqlalchemy.pool:741 in _do_get view
Module sqlalchemy.pool:188 in _create_connection view
Module sqlalchemy.pool:273 in __init__ view
Module sqlalchemy.event:282 in exec_once view
Module sqlalchemy.event:291 in __call__ view
Module sqlalchemy.engine.strategies:167 in first_connect view
Module sqlalchemy.dialects.mysql.base:1999 in initialize view
Module sqlalchemy.engine.default:176 in initialize view
Module sqlalchemy.dialects.mysql.base:1964 in _get_default_schema_name view
Module sqlalchemy.engine.base:1449 in execute view
Module sqlalchemy.engine.base:1628 in _execute_text view
Module sqlalchemy.engine.base:1651 in _execute_context view
Module sqlalchemy.engine.base:1647 in _execute_context view
Module sqlalchemy.engine.default:529 in _init_statement view
Module sqlalchemy.engine.default:606 in create_cursor view
Module MySQLdb.connections:243 in cursor view
StatementError: 'Connection' object has no attribute 'cursorclass' (original cause: AttributeError: 'Connection' object has no attribute 'cursorclass') 'SELECT DATABASE()' []

References:
http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html
http://docs.sqlalchemy.org/en/latest/dialects/mysql.html

[Michael Bayer]

On Oct 22, 2012, at 5:06 PM, Mike B wrote:

> Hello everyone,
>
> Has anyone successfully run SQLAlchemy 0.7.8 or .9 with MySQL 5.5? How about in Ubuntu 12.04? ...with MySQL-python 1.2.3 or a 1.2.4beta?
>
> I ask because Ubuntu 12.04 rescinds package manager support for MySQL 5.1, forcing us to upgrade to 5.5. Last week I experimentally upgraded a very stable server from {Ubuntu 11.10, MySQL 5.1, InnoDB, Python 2.7.2} to {Ubuntu 12.04, MySQL 5.5, InnoDB, Python 2.7.3}. Unfortunately, ever since the upgrade I have seen sporadic errors like this one:
>
> Module sqlalchemy.engine.default:606 in create_cursor view
> Module MySQLdb.connections:243 in cursor view
> StatementError: 'Connection' object has no attribute 'cursorclass' (original cause: AttributeError: 'Connection' object has no attribute 'cursorclass') 'SELECT DATABASE()' []
> (Python 2.7.3, SQLAlchemy 0.7.8, MySQL-python 1.2.3, MySQL 5.5, Pylons 1.0, mod-wsgi, apache2)

That stack trace is in MySQLdb, and that's likely where the bug is here. Often when MySQLdb has an issue like this, its connection object also becomes invalid, but the error it raises here doesn't tell SQLAlchemy that this is the case, so SQLAlchemy keeps this now-broken connection pooled. So subsequent usages fail.

However, one possibility here is that your code might not be using the connection in a threadsafe manner. I say this because you describe the errors as "sporadic", and I'm assuming you can't come up with a simple reproduction case. The MySQLdb connection object cannot be used by more than one thread at the same time, without synchronization being in place. When using the SQLAlchemy ORM, this implies that access to the Session must also be entirely local to a thread or synchronized, as well as access to *all objects that are associated with that Session*, as all those objects are proxies to the Session's, and therefore to the MySQLdb connection object's, state. It's not impossible that some subtle synchronization issue is only brought to light when used in conjunction with some quirk of MySQL 5.5.

If thread safety is not a factor here and you're sure connections are used in a thread-safe manner, and you can come up with a determining case for it, you'd ideally want to produce a MySQLdb test case for this and report to MySQLdb: http://sourceforge.net/p/mysql-python/bugs/ .

Alternatives include using some of the other MySQL drivers, such as OurSQL or PyMySQL. MySQL Connector/Python is said to be the "official" DBAPI for MySQL but I've still been having issues with it.