(Migrated) Outgoing SQL connection: connect_args in extra fails

forum-admin · June 20, 2016, 4:18pm

(This message has been automatically imported from the retired mailing list)

Hi list,

I’ve been trying to set charset=utf8 for my MySQL database connection via
the web-admin by adding the following to the extra field:

connect_args = {‘charset’:‘utf8’}

The result is as follow:
2015-04-10 09:35:44,165 - ERROR - 17615:Dummy-137 - zato.server.base:22 -
Could not handle broker msg:[Bunch(action=u’100812’, cluster_id=1L,
db_name=u’dev_luca1’, engine=u’mysql+pymysql’, extra=u"connect_args =
{‘charset’:‘utf8’}", host=u’’, id=8L, is_active=True,
msg_type=u’0002’, name=u’crm-db’, old_name=u’crm-db’,
password=u’****’, pool_size=10L, port=3306L, username=u’luca’)],
e:[Traceback (most recent call last):
File “/opt/zato/2.0.3/zato-server/src/zato/server/base/init.py”, line
47, in on_broker_msg
getattr(self, handler)(msg)
File “/opt/zato/2.0.3/zato-server/src/zato/server/base/worker.py”, line
1049, in on_broker_msg_OUTGOING_SQL_CREATE_EDIT
self.sql_pool_store[msg[‘name’]] = msg
File “/opt/zato/2.0.3/zato-server/src/zato/server/connection/sql.py”,
line 200, in setitem
pool = self.sql_conn_class(name, config, config_no_sensitive)
File “/opt/zato/2.0.3/zato-server/src/zato/server/connection/sql.py”,
line 102, in init
self.engine = create_engine(engine_url, **_extra)
File
"/opt/zato/2.0.3/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/init.py",
line 386, in create_engine
return strategy.create(*args, **kwargs)
File
"/opt/zato/2.0.3/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/strategies.py",
line 83, in create
cparams.update(pop_kwarg(‘connect_args’, {}))
ValueError: dictionary update sequence element #0 has length 1; 2 is
required

The other configuration of my SQL channel can be seen in the first line of
the above log.

Apparently it does not matter what you set in the connect_args dictionary
since the same error is always triggered.

Any ideas? I strongly doubt that I am the first one to encounter such
problem since setting the charset for the SQL connection is a quite common
action.

Thanks in advance,
Luca

forum-admin · June 20, 2016, 4:18pm

On 10/04/2015 08:53, Luca Valtulina wrote:

I’ve been trying to set charset=utf8 for my MySQL database connection
via the web-admin by adding the following to the extra field:

connect_args = {‘charset’:‘utf8’}
I haven’t tried this, but maybe the “extra text” field is appended to
the connection URL passed to SQLAlchemy. See

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

which provides the example:

e = create_engine(“mysql+pymysql://scott:tiger@localhost/test?charset=utf8”)

So try setting the extra text to just

charset=utf8

(no connect_args, no JSON) and see if that works. This would also agree
with the screenshot example shown at
https://zato.io/docs/web-admin/outgoing/sql.html

Regards,

Brian.

forum-admin · June 20, 2016, 4:18pm

On 10/04/15 10:14, Brian Candler wrote:

So try setting the extra text to just

charset=utf8

(no connect_args, no JSON) and see if that works.

Hi there,

I’m not sure which MySQL parameter to use but like Brian says, this
needs to be something that SQLAlchemy’s create_engine accepts in its
**kwargs.

http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine

In Zato this is provided as key=value lines, so …

foo1=bar1
foo2=bar2

… gets translated, ultimately, into engine =
sqlalchemy.create_engine(url, foo1=bar1, foo2=bar2)

forum-admin · June 20, 2016, 4:18pm

Hi,

well I thought it was needless to say it but well charset=utf8 has been my
first (unsuccsessful) attempt. That lead to:

TypeError: Invalid argument(s) ‘charset’ sent to create_engine(), using
configuration MySQLDialect_mysqldb/QueuePool/Engine. Please check that the
keyword arguments are appropriate for this combination of components.

which then brought me here:
http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#custom-dbapi-args
since, as Dariusz said, Zato translate the web-admin request to: engine =
sqlalchemy.create_engine(url, foo1=bar1, foo2=bar2)

Luca

2015-04-10 10:21 GMT+02:00 Dariusz Suchojad dsuch@zato.io:

On 10/04/15 10:14, Brian Candler wrote:

So try setting the extra text to just

charset=utf8

(no connect_args, no JSON) and see if that works.

Hi there,

I’m not sure which MySQL parameter to use but like Brian says, this
needs to be something that SQLAlchemy’s create_engine accepts in its
**kwargs.

http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine

In Zato this is provided as key=value lines, so …

foo1=bar1
foo2=bar2

… gets translated, ultimately, into engine =
sqlalchemy.create_engine(url, foo1=bar1, foo2=bar2)

forum-admin · June 20, 2016, 4:18pm

2015-04-10 10:32 GMT+02:00 Dariusz Suchojad dsuch@zato.io:

On 10/04/15 10:31, Luca Valtulina wrote:

well I thought it was needless to say it but well charset=utf8 has been
my first (unsuccsessful) attempt.

That lead to:

TypeError: Invalid argument(s) ‘charset’ sent to create_engine(), using
configuration MySQLDialect_mysqldb/QueuePool/Engine. Please check that
the keyword arguments are appropriate for this combination of components.

Ok, but the question is - what is the correct argument to be sent to
create_engine?

Seems charset is not the one?

charset is the right one. I can successfully create the engine directly in
python using either:

create_engine(‘DB_URL?charset=utf8’)
create_engine(‘DB_URL’, connect_args={‘charset’: ‘utf8’})

I should have specified all these details in my first post, apologies.

Luca

forum-admin · June 20, 2016, 4:18pm

What I do now is setting up the engine “the hard way” in my service. It is
a temporary solution and it would be nice to have this fixed in the next
Zato version, since I suspect it might affect also other users.

Another (really really ugly) solution is to include the extra parameter(s)
in the database name field.

Luca

2015-04-10 11:38 GMT+02:00 Dariusz Suchojad dsuch@zato.io:

On 10/04/15 10:46, Luca Valtulina wrote:

charset is the right one. I can successfully create the engine directly
in python using either:

create_engine(‘DB_URL?charset=utf8’)

create_engine(‘DB_URL’, connect_args={‘charset’: ‘utf8’})

Ok, but ‘charset’ is not an argument to create_engine, instead, it’s a
key to ‘connect_args’ and ‘connect_args’ is the argument to create_engine.

The net result is - it seems an oversight on Zato’s side that you cannot
use dictionaries in extra args to SQL pools.

I’ll see about adding it to 2.0.4 - this is something that should be
supported because the documentation says you can use anything that
create_engine accepts.

Seeing as your services always need to use UTF-8 anyway and your DB will
most likely be in UTF-8 as well, do you think this is something that
will cause issues right now?

–
Dariusz Suchojad

https://zato.io
ESB, SOA, REST, APIs and Cloud Integrations in Python

keith · August 7, 2017, 6:07am

Hi,

I am facing the same error with ssl for MySQL. I insert the following to the extra field:

connect_args={ssl : {‘ca’:’/pathtofile’, ‘cipher’:‘AES256-SHA’}}

Am I doing it correctly?

Regards,

Keith

keith · August 7, 2017, 7:04am

Forgot to mention, I am using 2.0.7.

Regards,

Keith

dsuch · August 7, 2017, 9:26am

Hi @keith,

can you please send across the full traceback from server.log when you submit the form?

Also, do you mean that ssl is not in qoutes? What if you use ‘ssl’ instead of ssl?

Thanks.

keith · August 7, 2017, 10:07am

Oh you are right. I may have forgotten to use quote the ssl. After putting in the quote, I got another error throw out, it seems pymysql 0.6.2 doesn’t support “cipher”.

==> server1c1/logs/server.log <==
2017-08-07 17:31:03,169 - WARNING - 21049:Dummy-403 - SessionWrapper:22 - Could not ping:[IVLE-EHS], session will be left uninitialized, e:[Traceback (most recent call last):
File “/opt/zato/2.0.7/code/zato-server/src/zato/server/connection/sql.py”, line 50, in init_session
self.pool.ping()
File “/opt/zato/2.0.7/code/zato-server/src/zato/server/connection/sql.py”, line 143, in ping
self.engine.connect().execute(query)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 1890, in connect
return self._connection_cls(self, **kwargs)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 60, in init
self.__connection = connection or engine.raw_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 1964, in raw_connection
return self.pool.unique_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 280, in unique_connection
return _ConnectionFairy._checkout(self)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 645, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 440, in checkout
rec = pool._do_get()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 964, in _do_get
return self._create_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 285, in _create_connection
return _ConnectionRecord(self)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 411, in init
self.connection = self.__connect()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 539, in __connect
connection = self.__pool._creator()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/strategies.py”, line 90, in connect
return dialect.connect(*cargs, **cparams)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py”, line 377, in connect
return self.dbapi.connect(*cargs, **cparams)
File “/opt/zato/2.0.7/code/eggs/PyMySQL-0.6.2-py2.7.egg/pymysql/init.py”, line 88, in Connect
return Connection(*args, **kwargs)
File “/opt/zato/2.0.7/code/eggs/PyMySQL-0.6.2-py2.7.egg/pymysql/connections.py”, line 546, in init
raise NotImplementedError(‘ssl options capath and cipher are not supported’)
NotImplementedError: ssl options capath and cipher are not supported

I then upgrade PyMySQL to 0.7.11 and try again. This time, it throws another error:

2017-08-07 18:02:21,159 - WARNING - 22051:Dummy-335 - SessionWrapper:22 - Could not ping:[IVLE-EHS], session will be left uninitialized, e:[Traceback (most recent call last):
File “/opt/zato/2.0.7/code/zato-server/src/zato/server/connection/sql.py”, line 50, in init_session
self.pool.ping()
File “/opt/zato/2.0.7/code/zato-server/src/zato/server/connection/sql.py”, line 143, in ping
self.engine.connect().execute(query)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 1890, in connect
return self._connection_cls(self, **kwargs)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 60, in init
self.__connection = connection or engine.raw_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py”, line 1964, in raw_connection
return self.pool.unique_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 280, in unique_connection
return _ConnectionFairy._checkout(self)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 645, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 440, in checkout
rec = pool._do_get()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 964, in _do_get
return self._create_connection()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 285, in _create_connection
return _ConnectionRecord(self)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 411, in init
self.connection = self.__connect()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/pool.py”, line 539, in __connect
connection = self.__pool._creator()
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/strategies.py”, line 90, in connect
return dialect.connect(*cargs, **cparams)
File “/opt/zato/2.0.7/code/eggs/SQLAlchemy-0.9.9-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py”, line 377, in connect
return self.dbapi.connect(*cargs, **cparams)
File “/opt/zato/2.0.7/code/lib/python2.7/site-packages/pymysql/init.py”, line 90, in Connect
return Connection(*args, **kwargs)
File “/opt/zato/2.0.7/code/lib/python2.7/site-packages/pymysql/connections.py”, line 650, in init
self.ctx = self._create_ssl_ctx(ssl)
File “/opt/zato/2.0.7/code/lib/python2.7/site-packages/pymysql/connections.py”, line 709, in _create_ssl_ctx
if isinstance(sslp, ssl.SSLContext):
AttributeError: ‘module’ object has no attribute ‘SSLContext’
]

Am not sure how to proceed from here.

Regards,

Keith

dsuch · August 7, 2017, 11:29am

This message above means that your particular Python 2.7.x release does not have the necessary ssl.SSLContext object in the ‘ssl’ module, which apparently PyMySQL 0.7.11 requires.

You would need to update Python 2.7 in the operating system (unless this is RHEL 6.x?) so as to have PyMySQL 0.7.11 access ssl.SSLContext.

keith · August 8, 2017, 1:23am

We are using the default rpm packages from zato-2.0 repo (RHEL 6.x). So unless the package’s Python 2.7.x is being updated, I guess we are stuck without the support of ssl at this moment. I don’t wish to deviate too much by manually upgrading python. Am afraid it might break when upgrade to next zato release. Will the next release of 2.0.8 solve this issues?

dsuch · August 8, 2017, 12:57pm

Hi @keith - is it RHEL 6.x 32-bit or a 64-bit one?

keith · August 10, 2017, 2:06am

It’s 64 bits.

Thanks.