OperationalError: too many SQL variables when requesting a huge number of events in GetEvents

[Markus Korn]

When I request all events in my database (there are about 3000 of them) I get an operational error:

In [1]: from zeitgeist.client import ZeitgeistDBusInterface

In [2]: iface = ZeitgeistDBusInterface()

In [3]: events = iface.FindEventIds((0,0), [], 2, 50000, 0)

In [4]: len(events)
Out[4]: 3716

In [5]: iface.GetEvents(events)
---------------------------------------------------------------------------
DBusException Traceback (most recent call last)

/media/devel/zeitgeist/trunk/<ipython console> in <module>()

/usr/lib/pymodules/python2.6/dbus/proxies.pyc in __call__(self, *args, **keywords)
    138 introspect_sig,
    139 args,
--> 140 **keywords)
    141
    142 def call_async(self, *args, **keywords):

/usr/lib/pymodules/python2.6/dbus/connection.pyc in call_blocking(self, bus_name, object_path, dbus_interface, method, signature, args, timeout, utf8_strings, byte_arrays)
    618 # make a blocking call

    619 reply_message = self.send_message_with_reply_and_block(
--> 620 message, timeout)
    621 args_list = reply_message.get_args_list(**get_args_opts)
    622 if len(args_list) == 0:

DBusException: org.freedesktop.DBus.Python.sqlite3.OperationalError: Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.6/dbus/service.py", line 702, in _message_cb
    retval = candidate_method(self, *args, **keywords)
  File "/media/devel/zeitgeist/trunk/zeitgeist/../_zeitgeist/engine/remote.py", line 65, in GetEvents
    events = _engine.get_events(event_ids)
  File "/media/devel/zeitgeist/trunk/zeitgeist/../_zeitgeist/engine/resonance_engine.py", line 337, in get_events
    """ % ",".join(["?" for id in ids]), ids).fetchall()
  File "/media/devel/zeitgeist/trunk/zeitgeist/../_zeitgeist/engine/resonance_engine.py", line 50, in execute
    return super(UnicodeCursor, self).execute(statement, parameters)
OperationalError: too many SQL variables

To fix this we have to find out what the limit for sql variables is, and either set a maximum for events which can be requested by GetEvents or do this SQL query in batches.

My personal opinion here is: raise a ValueError if there are this huge number of events requested, the client which is doing this is doing something wrong ;)

[Siegfried Gevatter (rainct)]

Fixed it with Markus.

revision <email address hidden> (1212)
Date: Mon 2009-12-07 12:27:46 +0100
Branch: zeitgeist-trunk
Bugs: https://launchpad.net/bugs/493529 fixed

    Change GetEvents() to use our own escaping instead
    of the one provided by sqlite3. This allows us to
    fetch more than 999 events at once.

But now, at least here, it's D-Bus failing with more than 1499 events.