Modify

Opened 5 years ago

Last modified 22 months ago

#5774 new defect

No such table: subscriptions

Reported by: davidrkirk@… Owned by: hasienda
Priority: high Component: AnnouncerPlugin
Severity: blocker Keywords: ProgrammingError upgrade db
Cc: trac@…, rjollos, doki_pen Trac Release: 0.11

Description

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.

Attachments (0)

Change History (24)

comment:1 Changed 5 years ago by davidrkirk@…

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

comment:2 Changed 5 years ago by rjollos

  • Summary changed from no such table: subscriptions to No such table: subscriptions

comment:3 Changed 5 years ago by 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?

comment:4 Changed 5 years ago by anonymous

  • Cc trac@… added
  • Severity changed from normal to blocker

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)

comment:5 Changed 5 years ago by 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.

comment:6 Changed 5 years ago by doki_pen

  • Priority changed from normal to low
  • Severity changed from blocker to minor

comment:7 Changed 5 years ago by anonymous

  • Priority changed from low to high
  • Severity changed from minor to blocker

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

Database is up to date, no upgrade necessary.

comment:8 Changed 5 years ago by anonymous

  • Priority changed from high to highest

comment:9 follow-up: Changed 5 years ago by 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?

comment:10 Changed 5 years ago by 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?

comment:11 in reply to: ↑ 9 Changed 5 years ago by 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.

comment:12 Changed 5 years ago by rjollos

comment:11 was mine.

comment:13 Changed 5 years ago by anonymous

  • Resolution set to worksforme
  • Status changed from new to closed

comment:14 follow-up: Changed 22 months ago by 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:

comment:15 in reply to: ↑ 14 Changed 22 months ago by 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.

comment:16 Changed 22 months ago by hasienda

  • Keywords ProgrammingError upgrade db added
  • Priority changed from highest to high
  • Resolution worksforme deleted
  • Status changed from closed to reopened

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

comment:17 Changed 22 months ago by hasienda

  • Cc rjollos doki_pen added
  • Owner changed from doki_pen to hasienda
  • Status changed from reopened to new

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.

comment:18 Changed 22 months ago by 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.

comment:19 Changed 22 months ago by 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.

comment:20 Changed 22 months ago by 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.

comment:21 Changed 22 months ago by 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.

comment:22 Changed 22 months ago by 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.

comment:23 Changed 22 months ago by 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.

comment:24 Changed 22 months ago by 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.

Add Comment

Modify Ticket

Action
as new .
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.