Connection to remote mysql database crashes

[agnaramasi]

I would like to draw your attention to the following bug report on launchpad: https://bugs.launchpad.net/griffith/+bug/574370

For me, the problem is that I have two clients connected to a mysql database on a server. One can connect to and interact with the database as expected. The other has problems connecting, however. It obviously can read the database to some extent, as it is sometimes able to briefly show the total number of records in the status bar. But none of the actual records are ever displayed in the list, and the program crashes eventually with the following terminal error:

Code:

Traceback (most recent call last):
  File "/usr/bin/griffith", line 1482, in <module>
    griffith = Griffith(home_dir, config)
  File "/usr/bin/griffith", line 132, in __init__
    self.populate_treeview()
  File "/usr/bin/griffith", line 976, in populate_treeview
    main_treeview.populate(self, statement, where)
  File "/usr/share/griffith/lib/main_treeview.py", line 489, in populate
    filename = gutils.get_image_fname(movie.poster_md5, self.db, "s")
  File "/usr/share/griffith/lib/gutils.py", line 736, in get_image_fname
    if not os.path.isfile(file_name) and not create_image_cache(md5sum, gsql):
  File "/usr/share/griffith/lib/gutils.py", line 666, in create_image_cache
    poster = session.query(db.Poster).filter_by(md5sum=md5sum).first()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1300, in first
    ret = list(self[0:1])
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1221, in __getitem__
    return list(res)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1361, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1364, in _execute_and_instances
    result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none())
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line 752, in execute
    return self.__connection(engine, close_with_result=True).execute(
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line 717, in __connection
    return self.transaction._connection_for_bind(engine)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line 329, in _connection_for_bind
    conn = bind.contextual_connect()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 1229, in contextual_connect
    return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in connect
    return _ConnectionFairy(self).checkout()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in __init__
    rec = self._connection_record = pool.get()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in get
    return self.do_get()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 631, in do_get
    raise exc.TimeoutError("QueuePool limit of size %d overflow %d reached, connection timed out, timeout %d" % (self.size(), self.overflow(), self._timeout))
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

Both clients are running Ubuntu 10.04. The server is an arm-based NAS running mysql 5.1.34.

[kilroy]

Do you have all the required libraries on the Ubuntu boxes? What OS is the NAS running? On the NAS is there a firewall, if so do you have the correct ports/permissions for both machines?

Are you trying to connect to Localhost, or do you have the MySQL setup to use a bind address?

Thanks
Andrew

[mike]

I think everything is correctly installed.
It looks for me like that annoying bug which lets connection objects stay in an opened and in-use state.
But there is a limitted number of simultanous open connection objects in sqlalchemy (a library used by griffith).

I don't know exactly why but I can't attach any file here. So I describe some changes you can make and check out. Perhaps
that will fix the error:

• open the file sql.py
• change the line

Code:

from sqlalchemy.orm import sessionmaker

to

Code:

from sqlalchemy.orm import scoped_session, sessionmaker

• add the line

Code:

Session = scoped_session(sessionmaker())

after the line

Code:

log = logging.getLogger("Griffith")

[mike]

My solution isn't fully correct. Now the better and working fix/workaround:

• open the file sql.py
• change the line

Code:

from sqlalchemy.orm import sessionmaker

to

Code:

from sqlalchemy.orm import scoped_session, sessionmaker

• look for the line

Code:

self.Session = sessionmaker(bind=engine) ...

• change it to

Code:

self.Session = scoped_session(sessionmaker(bind=engine)) ...

[agnaramasi]

The solution in reply number 3 works like a charm. Thanks a million!