[PlatformHistorianAgent] OperationalError: near "order": syntax error

[timothykang]

Traceback (most recent call last):
  File "~/volttron/env/lib/python2.7/site-packages/gevent/greenlet.py", line 327, in run
    result = self._run(*self.args, **self.kwargs)
  File "~/.volttron/agents/44dc1e39-6ee4-465e-babe-1e19f28bf10a/platform_historianagent-0.1/platform_historian/base_historian.py", line 320, in _process_loop
    self._cleanup_successful_publishes()
  File "~/.volttron/agents/44dc1e39-6ee4-465e-babe-1e19f28bf10a/platform_historianagent-0.1/platform_historian/base_historian.py", line 408, in _cleanup_successful_publishes
    c.execute('DELETE FROM outstanding order by ts limit ?', (self._submit_size_limit,))
OperationalError: near "order": syntax error

According to [1], SQLite using ORDER BY and LIMIT clauses in DELETE statements requires the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. According to [2], the option is normally turned off and can only be used "when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website", which explains why I'm getting the exception on my Mac.

Are ORDER BY and LIMIT needed? Or are we going to require Windows and Mac users to compile SQLite from source with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option?

[1] https://www.sqlite.org/lang_delete.html [2] https://www.sqlite.org/compile.html

[kmonson]

This is needed so the base historian can batch up what it passes to the historian implementation. I can do a workaround if needed.

[timothykang]

Fixed, closing.