can't open/preview a song if db connection is interrupted (mysql)

[Dave Bevan]

Hi.

Discovered the following error if OpenLP looses a connection to MySQL.

Scenario: OpenLP configured to use MySQL instead of SQLite.
Environment: Ubuntu 10.04 LTS, localhost for MySQL.

On starting OpenLP, it holds open two connections to MySQL. These are seen as having growing 'sleep' durations in MySQL by executing "show process" whilst OpenLP is dormant/not using the DB.

If there is a connection issue, either due to a network timeout, or forced connection kill - of at least one of these two persistent connections, when opening/previewing a song in the GUI, the following debug is displayed:

Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.6/openlp/core/lib/mediamanageritem.py", line 474, in onDoubleClicked
    self.onPreviewClick()
  File "/usr/lib/pymodules/python2.6/openlp/core/lib/mediamanageritem.py", line 496, in onPreviewClick
    serviceItem = self.buildServiceItem()
  File "/usr/lib/pymodules/python2.6/openlp/core/lib/mediamanageritem.py", line 590, in buildServiceItem
    if self.generateSlideData(serviceItem, item, xmlVersion, remote):
  File "/usr/lib/pymodules/python2.6/openlp/plugins/songs/lib/mediaitem.py", line 501, in generateSlideData
    song = self.plugin.manager.get_object(Song, item_id)
  File "/usr/lib/pymodules/python2.6/openlp/core/lib/db.py", line 323, in get_object
    return self.session.query(object_class).get(key)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 507, in get
    return self._get(key, ident)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1500, in _get
    return q.all()[0]
  File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1267, in all
    return list(self)
  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 753, in execute
    clause, params or {})
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 824, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 872, in _execute_clauseelement
    parameters=params
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 938, in __create_execution_context
    return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py", line 170, in __init__
    self.cursor = self.create_cursor()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py", line 258, in create_cursor
    return self._connection.connection.cursor()
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 576, in connection
    raise exc.InvalidRequestError("Can't reconnect until invalid transaction is rolled back")
InvalidRequestError: Can't reconnect until invalid transaction is rolled back

Note that this was initially seen after OpenLP was left running, and therefore connected to MySQL, overnight. After the overnight period, an attempt was made to open/preview a song and the above error was displayed. At this stage I don't know why the connections were closed - the server was localhost, the db uptime is days, and there was no laptop suspend during the overnight period.

Thoughts on why it can't reconnect correctly?

To recreate, configure OpenLP to use a MySQL database (not forgetting, on ubuntu, to install the package python-mysqldb otherwise you get "module not found" errors), and configuring the [songs] section for mysql, as described on the FAQ wiki page.

Then start OpenLP and observe output of "show process", or use the "processes" tab if using phpmyadmin to observe the database server. Kill one or both connections. Attempt to open/preview a song, and the result is as above.

npf@pc-n993019:~$ dpkg --list|grep openlp
ii openlp 1.9.12+bzr2117-0ubuntu1~lucid1 Church lyrics projection application

--d.

[Raoul Snyman (raoul-snyman)]

MySQL has a connection timeout, you can read more about it in the MySQL server manual.

There is nothing much we can do about this, we already take it into account and try to do the best considering the circumstances.
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

[Dave Bevan (dave-bevan)]

Thanks for the quick reply.

So I guess the current msg is a tad misleading - "Can't reconnect until invalid transaction is rolled back" actually means "Sorry, a db connection was interrupted/lost. We don't have the ability to auto-re-connect. Please quit OpenLP and start again." ?

Cheers,
--d.

[Raoul Snyman (raoul-snyman)]

In a way, yes. In essence it means that OpenLP tried to reconnect to MySQL and between MySQL and OpenLP they couldn't figure out what the last thing being done was and what they needed to do next.
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.