Skip to content

MySQL Server has gone away error occurs in multithreaded envirionment #387

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
sqlalchemy-bot opened this issue Dec 8, 2006 · 12 comments
Closed

MySQL Server has gone away error occurs in multithreaded envirionment #387

sqlalchemy-bot opened this issue Dec 8, 2006 · 12 comments
Labels
bug Something isn't working connection pool
Milestone
0.3.2

Comments

@sqlalchemy-bot
Copy link
Collaborator

Migrated issue, originally created by Michael Bayer (@zzzeek)

thought this could not be fixed, but 0.2.7's pool does not have the error....the fix may be incompatible with the connection pool's "cursor cleanup" function which was also meant to appease MySQL....

#!/usr/bin/python

from sqlalchemy import *
import sqlalchemy.pool as pool
import thread
from sqlalchemy import exceptions

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.pool').setLevel(logging.INFO)

threadids = set()
#meta = BoundMetaData('postgres://scott:tiger@127.0.0.1/test')

#meta = BoundMetaData('mysql://scott:tiger@localhost/test', poolclass=pool.SingletonThreadPool)
meta = BoundMetaData('mysql://scott:tiger@localhost/test')
foo = Table('foo', meta, 
    Column('id', Integer, primary_key=True),
    Column('data', String(30)))

meta.drop_all()
meta.create_all()

data = [x in range(1,500):
    data.append({'id':x,'data':"this is x value %d" % x})
foo.insert().execute(data)

class Foo(object):
    pass

mapper(Foo, foo)

root = './'
port = 8000

def serve(environ, start_response):
    sess = create_session()
    l = sess.query(Foo).select()
            
    start_response("200 OK", [('Content-type','text/plain')](]
for))
    threadids.add(thread.get_ident())
    print "sending response on thread", thread.get_ident(), " total threads ", len(threadids)
    return [for x in l]("\n".join([x.data))]

        
if __name__ == '__main__':
    from wsgiutils import wsgiServer
    server = wsgiServer.WSGIServer (('localhost', port), {'/': serve})
    print "Server listening on port %d" % port
    server.serve_forever()
@sqlalchemy-bot
Copy link
Collaborator Author

Michael Bayer (@zzzeek) wrote:

Ok, actually, the problem seems like it might be the rollback() call in connection._close(), removing it seems like 0.3.1 might work as well. but 0.2.7 has the same step. not sure whats different.

@sqlalchemy-bot
Copy link
Collaborator Author

Michael Bayer (@zzzeek) wrote:

tentatively resolving in changeset:2133. the _close method on ConnectionFairy was not dereferencing the connection object. when del was called on the CF, it would again try calling rollback() on the referenced connection; even though that connection had already been allocated by a CF in a different thread, producing the invalid state.

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Michael Bayer (@zzzeek):

  • changed status to closed

@sqlalchemy-bot
Copy link
Collaborator Author

Anonymous wrote:

This problem has resurfaced in:

  • mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using readline 4.3
  • RedHat EL 4, 2.6.9-22.0.2.ELsmp !document arguments for table, column, sequence, etc. #1 SMP Thu Jan 5 17:13:01 EST 2006 i686 i686 i386 GNU/Linux
  • python2.5
  • SQLAlchemy-0.3.8-py2.5.egg
  • MySQL_python-1.2.2-py2.5-linux-i686.egg
  • running inside a wsgiref simple server
  • ORM is not used

It happens at irregular intervals, after a day or two. Restarting the process makes it go away.

  File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 1175, in execute
    return self.compile(engine=self.engine, parameters=compile_params).execute(*multiparams, **params)
  File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 1064, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 783, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 571, in execute_compiled
    self._execute_raw(context)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 584, in _execute_raw
    self._execute(context)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 602, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
SQLError: (OperationalError) (2013, 'Lost connection to MySQL server during query') u'SELECT bundle.bundle_id, bundle.title, bundle.description, cover.name AS cover_url \nFROM bundle LEFT OUTER JOIN cover ON cover.cover_id = bundle.cover_id \nWHERE bundle.bundle_id = %s' [20](20)

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Anonymous:

  • changed status to reopened

@sqlalchemy-bot
Copy link
Collaborator Author

Michael Bayer (@zzzeek) wrote:

the error you've posted is not the error this ticket describes. this is a problem with your MySQL server/environment, and/or a lack of an appropriate pool_recycle on your connection pool;. SQLAlchemy doesn't do anything that would case MySQL to lose a connection. its possible you just need to specify pool_recycle on your engine so that MySQL's normal timeout of 8 hours on a connection is never reached within SA's connection pool.

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Michael Bayer (@zzzeek):

  • removed labels: blocker
  • changed status to closed

@sqlalchemy-bot
Copy link
Collaborator Author

Anonymous wrote:

So it is correct that if whatever error happens that invalidates a connection to a database, sqlalchemy keeps this connection in the pool?

@sqlalchemy-bot
Copy link
Collaborator Author

Anonymous wrote:

That's not a good thing. That would mean that every user would have to check if connections are dead all the time on it's own. By now I thought that SQLAlchemy recovers closed connections automatically.

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Anonymous:

  • changed status to reopened

@sqlalchemy-bot
Copy link
Collaborator Author

Anonymous wrote:

(original author: ants) This is actually #625.

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Anonymous:

  • changed status to closed

@sqlalchemy-bot sqlalchemy-bot added connection pool bug Something isn't working labels Nov 27, 2018
@sqlalchemy-bot sqlalchemy-bot added this to the 0.3.2 milestone Nov 27, 2018
@sqlalchemy-bot sqlalchemy-bot mentioned this issue Nov 27, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working connection pool
Projects
None yet
Milestone
0.3.2
Development

No branches or pull requests
1 participant
@sqlalchemy-bot