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.
