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)

[Robert Corsaro]

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.

[Robert Corsaro]

    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?

[Robert Corsaro]

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.

[Ryan J Ollos]

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:

[Ryan J Ollos]

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.

[Steffen Hoffmann]

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?).

[Steffen Hoffmann]

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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[Steffen Hoffmann]

(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.

[shorif2000]

I am getting this problem too with bloodhound 0.7 when i run command

(bloodhound)[root@cobra environments]# trac-admin main upgrade
The upgrade failed. Please fix the issue and try again.

OperationalError: no such table: @_subscription_attribute

see log

2014-08-20 15:23:53,607 Trac[env] INFO: -------------------------------- environment startup [Trac 1.0.1] --------------------------------
...
2014-08-20 15:23:53,954 Trac[default_workflow] DEBUG: Workflow actions at initialization: {u'resolve': {u'operations': [u'set_resolution'], 'name': u'resolve', 'default': 0, 'newstate': u'closed', 'oldstates': [u'new', u'assigned', u'accepted', u'reopened'], u'permissions': [u'TICKET_MODIFY']}, u'accept': {u'operations': [u'set_owner_to_self'], 'name': u'accept', 'default': 0, 'newstate': u'accepted', 'oldstates': [u'new', u'assigned', u'accepted', u'reopened'], u'permissions': [u'TICKET_MODIFY']}, u'leave': {u'operations': [u'leave_status'], 'name': u'leave', u'default': 1, 'newstate': u'*', 'oldstates': [u'*'], 'permissions': []}, u'reopen': {u'operations': [u'del_resolution'], 'name': u'reopen', 'default': 0, 'newstate': u'reopened', 'oldstates': [u'closed'], u'permissions': [u'TICKET_CREATE']}, u'reassign': {u'operations': [u'set_owner'], 'name': u'reassign', 'default': 0, 'newstate': u'assigned', 'oldstates': [u'new', u'assigned', u'accepted', u'reopened'], u'permissions': [u'TICKET_MODIFY']}, '_reset': {'operations': ['reset_workflow'], 'name': 'reset', 'default': 0, 'newstate': 'new', 'oldstates': [], 'permissions': []}}

2014-08-20 15:23:53,973 Trac[api] DEBUG: TracAnnouncer needs to register schema version
2014-08-20 15:23:53,973 Trac[api] INFO: TracAnnouncer db schema version is 5, should be 6
2014-08-20 15:23:53,973 Trac[env] WARNING: component announcer.api.AnnouncementSystem requires environment upgrade in environment <multiproduct.env.Environment object at 0x22f2fd0>...
2014-08-20 15:23:54,009 Trac[env] DEBUG: Log type 'file' for product '@'
2014-08-20 15:23:54,010 Trac[env] WARNING: Inheriting parent logger for product '@'
2014-08-20 15:23:54,010 Trac[env] INFO: -------------------------------- product @ environment startup [Trac 1.0.1] --------------------------------
2014-08-20 15:23:54,018 Trac[api] DEBUG: TracAnnouncer needs to register schema version
2014-08-20 15:23:54,018 Trac[api] INFO: TracAnnouncer db schema version is 5, should be 6
2014-08-20 15:23:54,019 Trac[env] WARNING: component announcer.api.AnnouncementSystem requires environment upgrade in environment <multiproduct.env.Environment object at 0x22f2fd0>...
2014-08-20 15:23:54,058 Trac[api] DEBUG: TracAnnouncer needs to register schema version
2014-08-20 15:23:54,058 Trac[api] INFO: TracAnnouncer db schema version is 5, should be 6
2014-08-20 15:23:54,058 Trac[env] INFO: announcer.api.AnnouncementSystem needs upgrade in environment <multiproduct.env.Environment object at 0x22f2fd0>...
2014-08-20 15:23:54,068 Trac[console] ERROR: Exception in trac-admin command: 
Traceback (most recent call last):
  File "/opt/html/apache-bloodhound-0.7/trac/trac/admin/console.py", line 109, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib64/python2.6/cmd.py", line 218, in onecmd
    return self.default(line)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/admin/console.py", line 285, in default
    return cmd_mgr.execute_command(*args)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/admin/api.py", line 124, in execute_command
    return f(*fargs)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/env.py", line 984, in _do_upgrade
    self.env.upgrade(backup=no_backup is None)
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/env.py", line 221, in upgrade
    product_upgraders = upgraders_for_product_envs()
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/env.py", line 210, in upgraders_for_product_envs
    return [u for u in upgraders if u]
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/env.py", line 209, in <genexpr>
    for env in [self] + self.all_product_envs())
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/env.py", line 198, in upgrader_for_env
    if participant.environment_needs_upgrade(db):
  File "/usr/lib/python2.6/site-packages/TracAnnouncer-1.0dev_r13984-py2.6.egg/announcer/api.py", line 412, in environment_needs_upgrade
    schema_ver = self.get_schema_version(db)
  File "/usr/lib/python2.6/site-packages/TracAnnouncer-1.0dev_r13984-py2.6.egg/announcer/api.py", line 443, in get_schema_version
    cursor.execute("SELECT * FROM subscription_attribute")
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/dbcursor.py", line 143, in execute
    return self.cursor.execute(sql, args=args)
  File "/opt/html/apache-bloodhound-0.7/bloodhound_multiproduct/multiproduct/dbcursor.py", line 83, in execute
    return super(BloodhoundIterableCursor, self).execute(translate_sql(self.env, sql), args=args)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/opt/html/apache-bloodhound-0.7/trac/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/opt/html/apache-bloodhound-0.7/trac/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such table: @_subscription_attribute

[Jun Omae]

Replying to shorif2000:

I am getting this problem too with bloodhound 0.7 when i run command

(bloodhound)[root@cobra environments]# trac-admin main upgrade
The upgrade failed. Please fix the issue and try again.

OperationalError: no such table: @_subscription_attribute

That is a Bloodhound issue. Please report to ​https://issues.apache.org/bloodhound.