Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-4648

Support sqlalchemy use with qserv

    Details

    • Type: Bug
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: Qserv
    • Labels:
    • Templates:
    • Story Points:
      3
    • Sprint:
      DB_W16_01, DB_W16_02
    • Team:
      Data Access and Database

      Description

      When one tries to connect to qserv using sqlalchemy there is an exception generated currently:

      $ python -c 'import sqlalchemy; sqlalchemy.create_engine("mysql+mysqldb://qsmaster@127.0.0.1:4040/test").connect()'
      /u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py:298: SAWarning: Exception attempting to detect unicode returns: InterfaceError("(_mysql_exceptions.InterfaceError) (-1, 'error totally whack')",)
        "detect unicode returns: %r" % de)
      Traceback (most recent call last):
        File "<string>", line 1, in <module>
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2018, in connect
          return self._connection_cls(self, **kwargs)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 72, in __init__
          if connection is not None else engine.raw_connection()
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2104, in raw_connection
          self.pool.unique_connection, _connection)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2078, in _wrap_pool_connect
          e, dialect, self)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception_noconnection
          exc_info
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py", line 199, in raise_from_cause
          reraise(type(exception), exception, tb=exc_tb)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect
          return fn()
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 318, in unique_connection
          return _ConnectionFairy._checkout(self)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 713, in _checkout
          fairy = _ConnectionRecord.checkout(pool)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 480, in checkout
          rec = pool._do_get()
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 1060, in _do_get
          self._dec_overflow()
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
          compat.reraise(exc_type, exc_value, exc_tb)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 1057, in _do_get
          return self._create_connection()
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 323, in _create_connection
          return _ConnectionRecord(self)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 454, in __init__
          exec_once(self.connection, self)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/event/attr.py", line 246, in exec_once
          self(*args, **kw)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/event/attr.py", line 256, in __call__
          fn(*args, **kw)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 1312, in go
          return once_fn(*arg, **kw)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 165, in first_connect
          dialect.initialize(c)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/dialects/mysql/base.py", line 2626, in initialize
          default.DefaultDialect.initialize(self, connection)
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 256, in initialize
          self._check_unicode_description(connection):
        File "/u2/salnikov/STACK/Linux64/sqlalchemy/2015_10.0/lib/python/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 343, in _check_unicode_description
          ]).compile(dialect=self)
        File "/u2/salnikov/STACK/Linux64/mysqlpython/1.2.3.lsst1/lib/python/MySQL_python-1.2.3-py2.7-linux-x86_64.egg/MySQLdb/cursors.py", line 174, in execute
          self.errorhandler(self, exc, value)
        File "/u2/salnikov/STACK/Linux64/mysqlpython/1.2.3.lsst1/lib/python/MySQL_python-1.2.3-py2.7-linux-x86_64.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
          raise errorclass, errorvalue
      sqlalchemy.exc.InterfaceError: (_mysql_exceptions.InterfaceError) (-1, 'error totally whack')
      

      The reason for that is that sqlalchemy generate few SELECT queries to figure out unicode support by the engine, and those selects are passed to qserv which cannot parse them. Here is the list of SELECTs which appears in proxy log:

      SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
      SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
      SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
      SELECT 'x' AS some_label
      

        Attachments

          Container Issues

            Issue Links

              Activity

                People

                • Assignee:
                  bvan Brian Van Klaveren
                  Reporter:
                  salnikov Andy Salnikov
                  Reviewers:
                  Fabrice Jammes
                  Watchers:
                  Andy Salnikov, Fabrice Jammes, Jacek Becla
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Summary Panel