Lost connection to MySQL server during query

Hello,

I’m trying to install an Enterprise ESB using zato.

Now, I have make 3 versions of the ESB :

  1. RHEL7, Python3.6, Redis on the host, SQLite : It’s work.
  2. RHEL7, Python3.6, Redis on the host, MariaDB on a dedicated server: Quickstart create worked but I have timeout error in the webadmin.
  3. RHEL7, Python3.6, Redis on the host, MariaDB on the host : Quickstart create don’t work, ‘Lost connection to MySQL server during query’.

The goal is 2 Zato servers with a loadbalancer, Redis and MariaDB on dedicated servers.

Thanks,
Thierry Leurent

The command.
zato quickstart create /opt/zato/environment/int mysql localhost 6379 --kvdb_password ‘’ --odb_user zatoint --odb_db_name ZATO_INT --odb_password pwd4zato --odb_host localhost --odb_port 3306 --verbose

The stacktrace.

[1/9] Certificate authority created
2020-06-11 17:07:05,635 - INFO - 6299:MainThread - Create:319 - [1/9] Certificate authority created
File “/opt/zato/current/bin/zato”, line 11, in
load_entry_point(‘zato-cli’, ‘console_scripts’, ‘zato’)()
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/zato_command.py”, line 424, in main
return run_command(args)
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/init.py”, line 382, in run_command
command_classargs.command.run(args)
Traceback (most recent call last):
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1193, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/default.py”, line 508, in do_execute
cursor.execute(statement, parameters)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 893, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1103, in _read_query_result
result.read()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1396, in read
first_packet = self.connection._read_packet()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1032, in _read_packet
packet_header = self._read_bytes(4)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1078, in _read_bytes
CR.CR_SERVER_LOST, “Lost connection to MySQL server during query”)
pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/init.py”, line 658, in run
return_code = self.execute(args)
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/quickstart.py”, line 326, in execute
if create_odb.Create(args).execute(args, False) == self.SYS_ERROR.ODB_EXISTS:
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/create_odb.py”, line 72, in execute
Base.metadata.create_all(engine)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/schema.py”, line 4004, in create_all
tables=tables)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1940, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1549, in _run_visitor
**kwargs).traverse_single(element)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 757, in visit_metadata
_is_metadata_operation=True)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 796, in visit_table
self.traverse_single(index)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 823, in visit_index
self.connection.execute(CreateIndex(index))
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 948, in execute
return meth(self, multiparams, params)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1009, in _execute_ddl
compiled
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1200, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1413, in _handle_dbapi_exception
exc_info
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 186, in reraise
raise value.with_traceback(tb)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1193, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/default.py”, line 508, in do_execute
cursor.execute(statement, parameters)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 893, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1103, in _read_query_result
result.read()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1396, in read
first_packet = self.connection._read_packet()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1032, in _read_packet
packet_header = self._read_bytes(4)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1078, in _read_bytes
CR.CR_SERVER_LOST, “Lost connection to MySQL server during query”)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, ‘Lost connection to MySQL server during query’) [SQL: ‘CREATE INDEX zato_u_appr_stat_idx ON zato_sso_user (approval_status)’] (Background on this error at: http://sqlalche.me/e/e3q8)

2020-06-11 17:07:06,041 - ERROR - 6299:MainThread - Create:670 - File “/opt/zato/current/bin/zato”, line 11, in
load_entry_point(‘zato-cli’, ‘console_scripts’, ‘zato’)()
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/zato_command.py”, line 424, in main
return run_command(args)
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/init.py”, line 382, in run_command
command_classargs.command.run(args)
Traceback (most recent call last):
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1193, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/default.py”, line 508, in do_execute
cursor.execute(statement, parameters)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 893, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1103, in _read_query_result
result.read()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1396, in read
first_packet = self.connection._read_packet()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1032, in _read_packet
packet_header = self._read_bytes(4)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1078, in _read_bytes
CR.CR_SERVER_LOST, “Lost connection to MySQL server during query”)
pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/init.py”, line 658, in run
return_code = self.execute(args)
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/quickstart.py”, line 326, in execute
if create_odb.Create(args).execute(args, False) == self.SYS_ERROR.ODB_EXISTS:
File “/opt/zato/3.1.0/code/zato-cli/src/zato/cli/create_odb.py”, line 72, in execute
Base.metadata.create_all(engine)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/schema.py”, line 4004, in create_all
tables=tables)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1940, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1549, in _run_visitor
**kwargs).traverse_single(element)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 757, in visit_metadata
_is_metadata_operation=True)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 796, in visit_table
self.traverse_single(index)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py”, line 121, in traverse_single
return meth(obj, **kw)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 823, in visit_index
self.connection.execute(CreateIndex(index))
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 948, in execute
return meth(self, multiparams, params)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py”, line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1009, in _execute_ddl
compiled
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1200, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1413, in _handle_dbapi_exception
exc_info
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 186, in reraise
raise value.with_traceback(tb)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1193, in _execute_context
context)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/sqlalchemy/engine/default.py”, line 508, in do_execute
cursor.execute(statement, parameters)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 893, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1103, in _read_query_result
result.read()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1396, in read
first_packet = self.connection._read_packet()
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1032, in _read_packet
packet_header = self._read_bytes(4)
File “/opt/zato/3.1.0/code/lib/python3.6/site-packages/pymysql/connections.py”, line 1078, in _read_bytes
CR.CR_SERVER_LOST, “Lost connection to MySQL server during query”)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, ‘Lost connection to MySQL server during query’) [SQL: ‘CREATE INDEX zato_u_appr_stat_idx ON zato_sso_user (approval_status)’] (Background on this error at: http://sqlalche.me/e/e3q8)

Hello @BackFromHell ,
I could not reproduce your error. Please, can you send me more information? Like the version of MariaDB and the steps you followed to install it?
Thank you

Hello @anielkis,

This is the version on the software:

  • Zato : Zato 3.1+rev.2822d65-py3.6.9
  • MariaDB : Server version: 10.4.13-MariaDB MariaDB Server
  • Redis : Redis server v=3.2.12 sha=00000000:0 malloc=jemalloc-3.6.0 bits=64 build=7897e7d0e13773f

I don’t have any custom configuration for MariaDB.

Regards,

Hello @BackFromHell,

Can you answer other questions? I am trying to reproduce your environment and trigger your error in order to help you.

Did you follow the steps of the guide lo install the version 10.4? Did you run the mysql_secure_installation script?

Regards,