CODEWITHSUNDEEP
pythonmysqlsqlalchemy
lima
lima

Reputation: 361

SQLAlchemy: Lost connection to MySQL server during query

There are a couple of related questions regarding this, but in my case, all those solutions is not working out. Thats why I thought of asking again. I am getting this error while I am firing below query using sqlalchemy orm.

Traceback (most recent call last):
File "MyFile.py", line 1010, in <module>
handler.handle(line.split('\t'))
File "MyFile.py", line 849, in handle
self.getRecord(whatIfFlag, id)
File "MyFile.py", line 143, in getRecord
newRecord = self.recordSearcher.getRecordByParams(name, pId)
File "abc.py", line 67, in getRecord
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2361, in one
ret = list(self)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2404, in __iter__
return self._execute_and_instances(context)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2419,  in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 720, in execute
return meth(self, multiparams, params)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
compiled_sql, distilled_params
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 947, in _execute_context
context)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
exc_info
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py", line 185, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 940, in _execute_context
context)
File "/opt/product/python/prod/bin/2.7.6/lib/python2.7/site-packages/SQLAlchemy-0.9.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 435, in do_execute
cursor.execute(statement, parameters)
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 205, in execute
File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
sqlalchemy.exc.OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') ....


query = self.__session.query(MyTable).filter(and_(MyTable.NAME == name,MyTable.P_ID == p_id)
try:
    record = query.one()
except NoResultFound:
    new_record = MyTable(params)
    self.__session.add(new_record)
    self.__session.commit()
    self.__session.close()

It is expected to return only one record. This is how I create my session.

sqlEngine = sqlalchemy.create_engine(self.getMySQLURI(), pool_recycle=10800, echo=False, echo_pool=False) 
session = scoped_session(sessionmaker(autoflush=True,
                                      autocommit=False,
                                      bind=sqlEngine,
                                      expire_on_commit=False))

These are my mysql configurations: interactive_timeout and wait_timeout is set to 28800 ~ 8 hours. net_write_timeout is set to 3600 ~ 60 mins and net_read_timeout is set 300 ~ 5 mins.

Any help is highly appreciated.

Upvotes: 0

Views: 4055

Answers (1)

lima
lima

Reputation: 361

It is turned out to be problem with the tcp_connect_timeout between the application server and the database server. The tcp connect timeout was default of 1 hour and my pool recycle settings was 3 hrs. So anything between 1 and 3 were failing. Posting the answer to help others who might face this later.

Upvotes: 2

Related Questions