No such table: subscriptions

[davidrkirk@…]

I've just installed AnnouncerPlugin and now when I go to any wiki page or a ticket I get the error

OperationalError: no such table: subscriptions

This is how I installed. I downloaded the zip file, unzipped it and built the egg file. I copied the egg to my plugins directory. I renamed [notifications] to [announcer]. I put this under [components]

announcerplugin.* = enabled
announcerplugin.subscribers.ticket_groups.* = disabled

Have I missed a step to update the database or something? I couldn't see anything about it in the doco.

I'm running Trac 0.11.5 and AnnouncerPlugin 0.2.

[davidrkirk@…]

If I turn off WatchSubscriber then the problem goes away, but obviously I can subscribe to anything.

[davidrkirk@…]

Ran trac-admin /var/trac upgrade and now it seems to be working.

If this is what fixed my problem, then maybe the documentation should be updated?

[anonymous]

I too receive this error with the Watchlist and Announcer plugins installed, running Trac 0.11.5 on Python 1.6.4 on Windows 2003.

Trac detected an internal error:

OperationalError: no such table: subscriptions

Python Traceback

Most recent call last:
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 444, in _dispatch_request
      Code fragment:
       439. try:
       440. if not env and env_error:
       441. raise HTTPInternalError(env_error)
       442. try:
       443. dispatcher = RequestDispatcher(env)
       444. dispatcher.dispatch(req)
       445. except RequestDone:
       446. pass
       447. resp = req._response or []
       448.  
       449. except HTTPException, e:
      Local variables:
      Name	Value
      after 	[u' except RequestDone:', u' pass', u' resp = ...
      before 	[u' try:', u' if not env and env_error:', u' raise ...
      dispatcher 	<trac.web.main.RequestDispatcher object at 0x01AA03B0>
      e 	OperationalError('no such table: subscriptions',)
      env 	<trac.env.Environment object at 0x0105D870>
      env_error 	None
      exc_info 	(<class 'sqlite3.OperationalError'>, OperationalError('no such table: ...
      filename 	'C:\\Python26\\lib\\site-packages\\trac-0.11.5-py2.6.egg\\trac\\web\\main.p ...
      frames 	[{'function': '_dispatch_request', 'lines_before': [u' try:', u' ...
      has_admin 	True
      line 	u' dispatcher.dispatch(req)'
      lineno 	443
      message 	u'OperationalError: no such table: subscriptions'
      req 	<Request "GET u'/ticket/14'">
      resp 	[]
      tb 	<traceback object at 0x01ADB0F8>
      tb_hide 	None
      traceback 	u'Traceback (most recent call last):\n File ...
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 216, in dispatch
      Code fragment:
       211. # Give the session a chance to persist changes
       212. req.session.save()
       213. req.display(template, content_type or 'text/html')
       214. else: # Genshi
       215. template, data, content_type = \
       216. self._post_process_request(req, *resp)
       217. if 'hdfdump' in req.args:
       218. req.perm.require('TRAC_ADMIN')
       219. # debugging helper - no need to render first
       220. from pprint import pprint
       221. out = StringIO()
      Local variables:
      Name	Value
      chosen_handler 	<trac.ticket.web_ui.TicketModule object at 0x01AA0C50>
      chrome 	<trac.web.chrome.Chrome object at 0x01ACDAD0>
      e 	OperationalError('no such table: subscriptions',)
      err 	(<class 'sqlite3.OperationalError'>, OperationalError('no such table: ...
      handler 	<trac.ticket.web_ui.TicketModule object at 0x01AA0C50>
      req 	<Request "GET u'/ticket/14'">
      resp 	('ticket.html', {'comment': None, 'change_preview': None, ...
      self 	<trac.web.main.RequestDispatcher object at 0x01AA03B0>
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 308, in _post_process_request
      Code fragment:
       303. # Trac 0.10, only filters with same arity gets passed real values.
       304. # Errors will call all filters with None arguments,
       305. # and results will not be not saved.
       306. extra_arg_count = arity(f.post_process_request) - 2
       307. if extra_arg_count == nbargs:
       308. resp = f.post_process_request(req, *resp)
       309. elif nbargs == 0:
       310. f.post_process_request(req, *(None,)*extra_arg_count)
       311. return resp
       312.  
       313.  
      Local variables:
      Name	Value
      args 	('ticket.html', {'comment': None, 'change_preview': None, ...
      extra_arg_count 	3
      f 	<tracwatchlist.plugin.WatchlistPlugin object at 0x01ACDEB0>
      nbargs 	3
      req 	<Request "GET u'/ticket/14'">
      resp 	('ticket.html', {'comment': None, 'change_preview': None, ...
      self 	<trac.web.main.RequestDispatcher object at 0x01AA03B0>
    * File "C:\Python26\lib\site-packages\tracwatchlistplugin-0.4.7094-py2.6.egg\tracwatchlist\plugin.py", line 517, in post_process_request
      Code fragment:
       512. resid=resid, realm=realm), title="Remove %s from watchlist" % realm)
       513. else:
       514. add_ctxtnav(req, "Watch", href=href('watchlist', action='watch',
       515. resid=resid, realm=realm), title="Add %s to watchlist" % realm)
       516. if self.gnotify and self.notifyctxtnav:
       517. if self.is_notify(req.session.sid, True, realm, resid):
       518. add_ctxtnav(req, "Do not Notify me", href=href('watchlist', action='notifyoff',
       519. resid=resid, realm=realm), title="No not notify me if %s changes" % realm)
       520. else:
       521. add_ctxtnav(req, "Notify me", href=href('watchlist', action='notifyon',
       522. resid=resid, realm=realm), title="Notify me if %s changes" % realm)
      Local variables:
      Name	Value
      content_type 	None
      data 	{'comment': None, 'change_preview': None, 'description_change': None, ...
      href 	<trac.web.href.Href object at 0x01E25110>
      msg 	[]
      parts 	[u'ticket', u'14']
      realm 	u'ticket'
      req 	<Request "GET u'/ticket/14'">
      resid 	u'14'
      self 	<tracwatchlist.plugin.WatchlistPlugin object at 0x01ACDEB0>
      template 	'ticket.html'
      user 	u'robert.morris'
    * File "build\bdist.win32\egg\announcerplugin\subscribers\watchers.py", line 77, in is_watching
      Local variables:
      Name	Value
      authenticated 	True
      cursor 	<trac.db.util.IterableCursor object at 0x01DFA450>
      db 	<trac.db.pool.PooledConnection object at 0x01E6B260>
      realm 	u'ticket'
      resource 	u'14'
      self 	<tracwatchlist.plugin.WatchlistPlugin object at 0x01ACDEB0>
      sid 	u'robert.morris'
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\util.py", line 59, in execute
      Code fragment:
        54. return self.cursor.execute(sql)
        55. except Exception, e:
        56. self.log.debug('execute exception: %r', e)
        57. raise
        58. if args:
        59. return self.cursor.execute(sql_escape_percent(sql), args)
        60. return self.cursor.execute(sql)
        61.  
        62. def executemany(self, sql, args=None):
        63. if self.log:
        64. self.log.debug('SQL: %r', sql)
      Local variables:
      Name	Value
      args 	(u'robert.morris', 1, 'watcher', u'ticket', '*', u'14')
      self 	<trac.db.util.IterableCursor object at 0x01DFA450>
      sql 	'\n SELECT id\n FROM subscriptions\n ...
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\sqlite_backend.py", line 58, in execute
      Code fragment:
        53. raise
        54. def execute(self, sql, args=None):
        55. if args:
        56. sql = sql % (('?',) * len(args))
        57. return self._rollback_on_error(sqlite.Cursor.execute, sql,
        58. args or [])
        59. def executemany(self, sql, args=None):
        60. if args:
        61. sql = sql % (('?',) * len(args[0]))
        62. return self._rollback_on_error(sqlite.Cursor.executemany, sql,
        63. args or [])
      Local variables:
      Name	Value
      args 	(u'robert.morris', 1, 'watcher', u'ticket', '*', u'14')
      self 	<trac.db.sqlite_backend.PyFormatCursor object at 0x01D2E540>
      sql 	'\n SELECT id\n FROM subscriptions\n ...
    * File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\sqlite_backend.py", line 50, in _rollback_on_error
      Code fragment:
        45. sqlite_version_string = '%d.%d.%d' % (_ver[0], _ver[1], int(_ver[2]))
        46.  
        47. class PyFormatCursor(sqlite.Cursor):
        48. def _rollback_on_error(self, function, *args, **kwargs):
        49. try:
        50. return function(self, *args, **kwargs)
        51. except sqlite.DatabaseError, e:
        52. self.cnx.rollback()
        53. raise
        54. def execute(self, sql, args=None):
        55. if args:
      Local variables:
      Name	Value
      args 	('\n SELECT id\n FROM subscriptions\n ...
      e 	OperationalError('no such table: subscriptions',)
      function 	<method 'execute' of 'sqlite3.Cursor' objects>
      kwargs 	{}
      self 	<trac.db.sqlite_backend.PyFormatCursor object at 0x01D2E540>

File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 444, in _dispatch_request
  dispatcher.dispatch(req)
File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 216, in dispatch
  self._post_process_request(req, *resp)
File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\web\main.py", line 308, in _post_process_request
  resp = f.post_process_request(req, *resp)
File "C:\Python26\lib\site-packages\tracwatchlistplugin-0.4.7094-py2.6.egg\tracwatchlist\plugin.py", line 517, in post_process_request
  if self.is_notify(req.session.sid, True, realm, resid):
File "build\bdist.win32\egg\announcerplugin\subscribers\watchers.py", line 77, in is_watchingFile "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\util.py", line 59, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\sqlite_backend.py", line 58, in execute
  args or [])
File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6.egg\trac\db\sqlite_backend.py", line 50, in _rollback_on_error
  return function(self, *args, **kwargs)

[doki_pen]

It is normal that any trac plugin that adds tables won't work without running trac-admin /path/to/env upgrade. It is not normal that you don't get a giant message telling you you need to run this command. I'll look into why and update the doc.

[anonymous]

Running trac-admin /path/to/env upgrade results in the message:

Database is up to date, no upgrade necessary.

[doki_pen]

    def environment_needs_upgrade(self, db):
        cursor = db.cursor()
        try:
            cursor.execute("select count(*) from subscriptions")
            cursor.fetchone()
            return False
        except:
            db.rollback()
            return True

I don't understand how this can result in no upgrade if the table doesn't exist! Did some api change?

[doki_pen]

I just tested with trac trunk by dropping the subscriptions table. I got the "trac needs upgrade" message as expected. Running trac upgrade created the table. I can't reproduce this error. What DB are you using? Is there a permission problem? What happens when you run the above query manually?

[anonymous]

Replying to doki_pen:

I don't understand how this can result in no upgrade if the table doesn't exist! Did some api change?

I just installed from scratch on Trac 0.12dev-r9098 and received the 'Database needs upgrade' notification as expected, so I don't think it is an API change.

[rjollos]

comment:11 was mine.

[anonymous]

It is still out of work.
When I go to prefs, subscriptions I get

Oops…
Trac detected an internal error:
OperationalError: no such table: subscription

When I try to upgrade database, I get AttributeError:

[rjollos]

Replying to anonymous:

It is still out of work.
When I go to prefs, subscriptions I get
[...]
When I try to upgrade database, I get AttributeError:

This sounds like the issue described in #9742. We don't have a fix yet, but hope to have one soon. Please follow that ticket if you are interested.

[hasienda]

First, thanks for the report, and all comments.

Second, as Ryan already pointed out, comment 14 is about another issue, that is rather unrelated. Everyone, please see and follow #9742 instead. Btw, that one is about a rather small Trac db API change.

Third, the issue here is about a major misconception of using Python exceptions with SQL queries since day one of this plugin. As discussed in ​t:#10451 SQL should be designed to succeed, especially in IEnvironmentSetupParticipant methods, where a single connection is passed around. One plugin can/will break the whole procedure. A fix is in Trac-1.0, but all plugins, that used the offending approach should be reworked as done for TagsPlugin lately.

Forth, priority and severity are agreeable after discussion, not to trigger it. And code rules, so the developers that are actually working on the code should be treated with due respected, and after all it's their ticket system, not a support forum (We have mailing-list for that, right?).

[hasienda]

Fifth and last for now, sorry for not taking this serious enough for much too long.

I confirm blocker status for that and will resolve the issue, but from the ground. It requires a major effort, that is WiP but will take yet another bit of time to fit in for misses and shortcomings in the past. Please bear with me.

[hasienda]

(In [12296]) TracAnnouncer: Part 1 of 7 - Move towards a versioned db schema for this plugin, refs #5774 and #9742.

Main feature of the code added here:

• schema version tracked in system Trac db table

Now we introduce the common, recommended approach of tracking plugin db schema
versions in Trac db table system, that doesn't require any guessing later on.

This changeset requires a database upgrade.

Make sure to backup your db before upgrading, especially if you put faith
into recovering user settings later on. While it may become technically
possible to recover parts of an older configuration, the value of such a
configuration is rather questionable in context of the current feature set,
and I guess that doing a conceptual reinitialization by starting from scratch
is what many (most?) users should consider anyway.

Stand back and wait, at least until it has survived more rigorous testing by
other developers and until enough data migration code has been developed to
preserve critical parts of your Trac applications configuration in production.

[hasienda]

(In [12297]) TracAnnouncer: Part 2 of 7 - Restore db-related plugin history, refs #5774 and #9742.

Main feature of the code added here:

• seamless schema upgrade by dedicated upgrade scripts - Trac core style

From the beginnung (see [3015]) this plugin's SQL driven schema check relied
on a SELECT raising an exeption for non-existing db table.
This has been discussed lately and found to be a flawed approach, that is even
known to break installations and interferes with db upgrades for Trac 0.13dev
and ultimately Trac 1.0 as well.

After introduction of the db schema version number for this plugin, table
existence testing is required one last time, current schema version is
registered, and only the registered schema version gets checked further on.

[hasienda]

(In [12298]) TracAnnouncer: Part 3 of 7 - Follow historic footsteps in schema development for this plugin, refs #5774 and #9742.

The schema discovery logic already leaks a bit about my recent research and
about the number of required follow-up changes to add an incremental upgrade
module for each discovered schema revision as well.

Futhermore, thanks to Jun Omae we utilize a db-specific, non-intrusive query
for retrieving the db table list used in any table existence check from now.

[hasienda]

(In [12299]) TracAnnouncer: Part 4 of 7 - Just taste an evolutionary moment, refs #5774 and #9742.

The switch to the current subscription interface was certainly a reasonable
and visionary step, but it has still some tasks pending for completion.

[hasienda]

(In [12300]) TracAnnouncer: Part 5 of 7 - Care for some schema adjustments, refs #5774 and #9742.

Early adopters beware: The long-awaited script for data migration from
subscriptions to subscription and subscription_attribute db tables
is still a pending issue, that requires even more investigation.

Currently the upgrade procedure just drops the old table irrevocably,
following corresponding historic schema changes, hereby labeled as revision 4.
Make sure to backup your db before upgrading, if you still put faith into
recovering some old user settings later on - you've been warned twice now.

[hasienda]

(In [12301]) TracAnnouncer: Part 6 of 7 - The best way great ideas can go: refinement, refs #5774 and #9742.

With a seemingly small, but essential db table change we're approaching at the
current db schema now.

[hasienda]

(In [12302]) TracAnnouncer: Part 7 of 7 - Finally: Go from present to future, refs #5774, #7975, #8065, #9742 and #10384.

Now we've bridged the gap and provide an upgrade path for each historic schema
revision of this plugin, while data migration is incomplete yet, especially
regarding subscription attributes stored in session_attribute (before v3).
Due to component name changes the conversion will be rather complicated, and
therefore corresponding research and development is postponed to a future date
and will largely depend on explicite demand as well.