MySQL server has gone away

[Jeffrey Yunes]

Hi all,
After successful queries and a 10 minute wait, I'm getting the popular "MySQL server has gone away." I have a single-threaded app and my pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love to know which part of the docs I've missed.

I'm using oursql, so my problem sounds related to: https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ

Any suggestions?

Thanks!

Here are the details:

import sqlalchemy
>>> sqlalchemy.__version__
'1.0.13'

# mysql --version

mysql Ver 15.1 Distrib 5.5.49-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

 

aborted_clients is increasing, wait_timeout is set to 28800.

mysql error log:

Jun 14 13:36:50 hostname mysqld: 160614 13:36:50 [Warning] Aborted connection 170 to db: 'db' user: 'user' host: 'localhost' (Unknown error)

initialization code:

def init():
  global alchemy_session
  ...
  engine = create_engine("mysql+oursql://%s:%s@%s:%d/%s" % (user, passwd, host, port, db),
                         pool_recycle=300,
                         echo=True,
                         echo_pool=True)
  metadata = MetaData()
  metadata.reflect(engine)
  alchemy_base = automap_base(metadata=metadata)
  alchemy_base.prepare(engine, reflect=True)
  alchemy_session = Session(engine)

Stack trace + echo:

2016-06-14 13:26:15,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
...
2016-06-14 13:26:17,789 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
...
2016-06-14 13:37:36,986 INFO sqlalchemy.pool.QueuePool Invalidate connection <oursql.Connection object at 0x7f3743282280> (reason: OperationalError:(2006, 'MySQL server has gone away', None))
2016-06-14 13:37:36,986 ERROR sqlalchemy.pool.QueuePool Exception closing connection <oursql.Connection object at 0x7f3743282280>
Traceback (most recent call last):
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 290, in _close_connection
self._dialect.do_close(connection)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 426, in do_close
dbapi_connection.close()
File "oursqlx/connection.pyx", line 170, in oursql.Connection.close (oursqlx/oursql.c:6764)
File "oursqlx/connection.pyx", line 215, in oursql.Connection.rollback (oursqlx/oursql.c:7299)
File "oursqlx/connection.pyx", line 183, in oursql.Connection._raise_error (oursqlx/oursql.c:6984)
OperationalError: (2006, 'MySQL server has gone away', None)
Traceback (most recent call last):
File "/home/user/projects/my_project/bin/test.py", line 533, in <module>
main()
...
File "/home/user/projects/my_project/pkg/x/y.py", line 97, in func
for a, b in query:
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2761, in __iter__
return self._execute_and_instances(context)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2776, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
context)
File "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/oursql.py", line 77, in do_execute
cursor.execute(statement, parameters)
File "oursqlx/cursor.pyx", line 120, in oursql.Cursor.execute (oursqlx/oursql.c:20439)
File "oursqlx/cursor.pyx", line 111, in oursql.Cursor.execute (oursqlx/oursql.c:20301)
File "oursqlx/statement.pyx", line 157, in oursql._Statement.prepare (oursqlx/oursql.c:10423)
File "oursqlx/statement.pyx", line 127, in oursql._Statement._raise_error (oursqlx/oursql.c:9947)
sqlalchemy.exc.OperationalError: (oursql.OperationalError) (2006, 'MySQL server has gone away', None) [SQL: u'SELECT ... ]

Process finished with exit code 1

[Mike Bayer]

Well I'd get off of OurSQL to start with since it is unmaintained for
years now. The "gone away" error doesn't always mean the connection
was actually dropped, in some old school situations it just means the
client got out of sync with the MySQL protocol.

( .... waits .... )

still broken? next issue would be how your MySQL/MariaDB is set up.
Like is this using HAProxy or something like that. If not, then
looking at your network would be next.

First try using either mysqlclient or pymysql, those are the two clients
to use right now.

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jeffrey Yunes]

Sorry, no dice!

I switched to pymysql.

I'm connecting via 127.0.0.1, so I don't think it's a network issue.

Pretty much the same error...

Any thoughts?

2016-06-14 15:45:20,889 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
...
2016-06-14 15:56:42,530 INFO sqlalchemy.pool.QueuePool Invalidate
connection <pymysql.connections.Connection object at 0x7f4951a25390>
(reason: OperationalError:(2013, 'Lost connection to MySQL server during
query'))

Traceback (most recent call last):
File "/home/user/projects/my_project/bin/test.py", line 533, in
<module>
main()

File "/home/user/projects/my_project/my_package/x/y.py", line 97, in

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 450, in do_execute
cursor.execute(statement, parameters)
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 161, in execute
result = self._query(query)
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 317, in _query
conn.query(q)
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 835, in query
self._affected_rows =
self._read_query_result(unbuffered=unbuffered)
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1019, in _read_query_result
result.read()
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1302, in read
first_packet = self.connection._read_packet()
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 961, in _read_packet
packet_header = self._read_bytes(4)
File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013,
'Lost connection to MySQL server during query') [SQL: u'SELECT ...]

Process finished with exit code 1

> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/EOM7q4xY96c/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.

[Mike Bayer]

not really. Are you leaving a long-running transaction open and not
closing it (or rolling back / committing) ?

[Jeffrey Yunes]

Hi Mike and all,
So, mostly good news.

First to answer your question, I'm not aware of any long standing
queries. During the 10 minutes between queries, `SHOW PROCESSLIST`
indicates there are no queries running (two sleeping). The only remotely
suspicious thing in the query log is a `BEGIN (implicit)` with no
matching `END`.

Now the good news. According to `SHOW VARIABLES` wait_timeout was set to
the default of 28800. But as I'm sure you've heard before, I queried
that in an interactive terminal, where it was set to
interactive_timeout. My wait_timeout for a non-interactive terminal was
set to the default of 10 minutes. Embarrassingly, I've done that once
before.

Anyway, what's unresolved is why initializing an engine with
pool_recycle=300 does not refresh my session's underlying connection
after 5 minutes, when the timeout is 10 minutes. I'm using the session
object like this:

query = settings.alchemy_session.query(MyTable).filter(MyTable.name ==
name)
for row in query:
pass
[nothing else with settings.alchemy_session]

Is there something else I need to do so my session releases or refreshes
its connection from the pool?

Thanks!

[Dev Mukherjee]

On Wed, Jun 15, 2016 at 7:27 AM, Jeffrey Yunes <je...@yunes.us> wrote:

Hi all,
After successful queries and a 10 minute wait, I'm getting the popular "MySQL server has gone away." I have a single-threaded app and my pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love to know which part of the docs I've missed.

I'm using oursql, so my problem sounds related to: https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ

Any suggestions?

This might not have anything to do with your particular issue but I thought I'd share a particular instance we had the same things happening to us. And it turned out to have nothing to do with SQLAlchemy.

We had an app running on Apache + mod_wsgi on a VM and the Web server and MySQL server were on the same VM. The VM didn't have much resources assigned to it.

It turned out to be a disk IO on the VM host causing the MySQL to "go away". We have the same application running on AWS with hundreds of users and a shared MySQL backend and have had no issues.

Once again; this might have nothing to do with your issue but just throwing it out there.