Configuring Mistral + PyyMySQL + SSL + MySQL

Asked by timball on 2020-03-12

We’re having trouble configuring Mistral to work with PyMySQL and SSL on MySQL. We have the user configured with the following permission grant:

`GRANT ALL ON mistral.* TO mistral@'%%' IDENTIFIED BY '{password}' REQUIRE SSL;`

When we try to run mistral with the connection string ‘mysql+pymysql://mistral:<password>@<host>/mistral’, the engine server crashes with the following stack trace:

```
CRITI [Mistral] Unhandled error
Traceback (most recent call last):
  File "/usr/local/bin/mistral-db-manage", line 10, in <module>
    sys.exit(main())
  File "/opt/stack/mistral/mistral/db/sqlalchemy/migration/cli.py", line 148, in main
    CONF.command.func(config, CONF.command.name)
  File "/opt/stack/mistral/mistral/db/sqlalchemy/migration/cli.py", line 63, in do_upgrade
    do_alembic_command(config, cmd, revision, sql=CONF.command.sql)
  File "/opt/stack/mistral/mistral/db/sqlalchemy/migration/cli.py", line 44, in do_alembic_command
    getattr(alembic_cmd, cmd)(config, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 279, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/compat.py", line 240, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "/opt/stack/mistral/mistral/db/sqlalchemy/migration/alembic_migrations/env.py", line 84, in <module>
    run_migrations_online()
  File "/opt/stack/mistral/mistral/db/sqlalchemy/migration/alembic_migrations/env.py", line 68, in run_migrations_online
    connection = engine.connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2206, in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2306, in raw_connection
    self.pool.unique_connection, _connection
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2279, in _wrap_pool_connect
    e, dialect, self
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1544, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2275, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/impl.py", line 238, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 453, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 599, in connect
    self._request_authentication()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 861, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
OperationalError: (pymysql.err.OperationalError) (1045, u"Access denied for user 'mistral'@'10.130.100.197' (using password: YES)")
(Background on this error at: http://sqlalche.me/e/e3q8)
```

The error goes away when we remove the `REQUIRE SSL` from the permissions grant

We’ve tried several approaches to having pymysql use SSL:
   1. adding sshMode=REQUIRED as a query parameter on the connection string (i.e., mysql+pymysql://mistral:<password>@<host>/mistral?sshMode=REQUIRED)
  2. adding ssh=1 as a query parameter on the connection string
  3. setting the connection_parameters configuration parameter under [database] to sshMode=REQUIRED
  4. setting connection_parameters to ssh=1
  5. setting connection_parameters to ssh=true

None of these approaches have worked.

Our mistral setup works without pymsql, but performance is significantly degraded

Any advise or guidance would be welcome.

Question information

Language:
English Edit question
Status:
Expired
For:
Mistral Edit question
Assignee:
No assignee Edit question
Last query:
2020-03-12
Last reply:
2020-03-28
Launchpad Janitor (janitor) said : #1

This question was expired because it remained in the 'Open' state without activity for the last 15 days.