cymysql fails in get_isolation_level during engine.connect()

[John Mark Vandenberg]

While connecting, the following error occurs.

==================================== ERRORS ====================================
______________________ ERROR at setup of test_2010_count _______________________
request = <SubRequest 'connection' for <Function 'test_2010_count'>>
engine = Engine(mysql+cymysql://travis@localhost/era_data)
    @pytest.fixture(scope="module")
    def connection(request, engine):
>       connection = engine.connect()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/pytest_sqlalchemy.py:27: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2018: in connect
    return self._connection_cls(self, **kwargs)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:72: in __init__
    if connection is not None else engine.raw_connection()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2104: in raw_connection
    self.pool.unique_connection, _connection)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2074: in _wrap_pool_connect
    return fn()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:318: in unique_connection
    return _ConnectionFairy._checkout(self)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:713: in _checkout
    fairy = _ConnectionRecord.checkout(pool)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:480: in checkout
    rec = pool._do_get()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:1060: in _do_get
    self._dec_overflow()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:60: in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:1057: in _do_get
    return self._create_connection()
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:323: in _create_connection
    return _ConnectionRecord(self)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/pool.py:454: in __init__
    exec_once(self.connection, self)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py:246: in exec_once
    self(*args, **kw)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py:256: in __call__
    fn(*args, **kw)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:1319: in go
    return once_fn(*arg, **kw)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py:165: in first_connect
    dialect.initialize(c)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2633: in initialize
    default.DefaultDialect.initialize(self, connection)
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py:249: in initialize
    self.get_isolation_level(connection.connection)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
self = <sqlalchemy.dialects.mysql.cymysql.MySQLDialect_cymysql object at 0x1db15d0>
connection = <cymysql.connections.Connection object at 0x1db19d0>
    def get_isolation_level(self, connection):
        cursor = connection.cursor()
        cursor.execute('SELECT @@tx_isolation')
        val = cursor.fetchone()[0]
        cursor.close()
        if util.py3k and isinstance(val, bytes):
            val = val.decode()
>       return val.upper().replace("-", " ")
E       AttributeError: 'NoneType' object has no attribute 'upper'
../../../virtualenv/python2.7_with_system_site_packages/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2502: AttributeError

https://travis-ci.org/jayvdb/era_data/jobs/127444574

[Mike Bayer]

If the SELECT is failing here I wonder if this is a cymysql issue.

[Mike Bayer]

workaround:

engine = create_engine( 'mysql+cymysql://scott:tiger@localhost/test?use_unicode=1', echo=True)

that is, add the use_unicode=1 flag.

[Mike Bayer]

I'm getting completely broken behavior from cymysql:

import cymysql

conn1 = cymysql.connect(use_unicode=1)
conn2 = cymysql.connect(use_unicode=0)

def test(conn):
    cursor = conn.cursor()

    cursor.execute("SELECT 'any old text'")
    print cursor.fetchall()

    cursor.execute("SELECT @@tx_isolation")

    print cursor.fetchall()

test(conn1)
test(conn2)

output:

[(u'any old text',)]
[(u'REPEATABLE-READ',)]
[(None,)]
[(None,)]

I'm on a newer MariaDB, can you please share your MySQL version info? this is an upstream cymysql bug.

[Mike Bayer]

https://github.com/nakagami/CyMySQL/issues/15

my advice would be to switch to mysqlclient, as this is widely used is also native.

[John Mark Vandenberg]

This test was done on Travis, Ubuntu precise, not 100% sure on the MySQL version, as it is their default. It isnt 5.6 , based on https://docs.travis-ci.com/user/database-setup/#MySQL-5.6. I'll check tomorrow.

I've switched to using mysqldb, but will look at mysqlclient also.

I raised this issue because it is listed at http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#module-sqlalchemy.dialects.mysql.cymysql

[Mike Bayer]

upstream issue is fixed please upgrade to cymysql 0.8.7 if you're still using it, thanks!