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