Re: [sqlalchemy] MySQL server has gone away

Tue, 14 Jun 2016 16:55:25 -0700 

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!

On 14 Jun 2016, at 16:28, Mike Bayer wrote:


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




On 06/14/2016 07:06 PM, Jeffrey Yunes wrote:

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 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/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

On 14 Jun 2016, at 15:15, Mike Bayer wrote:


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.



On 06/14/2016 05:27 PM, Jeffrey Yunes 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?

Thanks!

Here are the details:

|
importsqlalchemy

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:
|
definit():
  globalalchemy_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


--
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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--

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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.




--

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+unsubscr...@googlegroups.com.

To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.






















					Reply via email to