OperationalError: no such table: revision

[dimdroll@…]

After upgrading from the version 0.11 to version 0.12 i`m receiving error when im trying to open trac url:

Available Projects
    * trac-svn: Error
      (The Trac Environment needs to be upgraded. Run "trac-admin /services/trac-svn upgrade")

When im trying to make trac-admin /services/trac-svn upgrade i`m receiving the following error:

2010-08-30 15:11:08,990 Trac[env] WARNING: Component <trac.env.EnvironmentSetup object at 0x4094eaac> requires environment upgrade
2010-08-30 15:11:08,992 Trac[env] INFO: Trac database schema version is 21, should be 26
2010-08-30 15:11:09,153 Trac[env] INFO: trac.env.EnvironmentSetup upgrading...
2010-08-30 15:11:09,885 Trac[env] INFO: Upgraded database version from 21 to 22
2010-08-30 15:11:12,863 Trac[console] ERROR: Exception in trac-admin command:
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 107, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/local/lib/python2.5/cmd.py", line 218, in onecmd
    return self.default(line)
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 257, in default
    return cmd_mgr.execute_command(*args)
  File "build/bdist.linux-i686/egg/trac/admin/api.py", line 123, in execute_command
    return f(*fargs)
  File "build/bdist.linux-i686/egg/trac/env.py", line 790, in _do_upgrade
    self.env.upgrade(backup=no_backup is None)
  File "build/bdist.linux-i686/egg/trac/env.py", line 533, in upgrade
    with_transaction(self)(participant.upgrade_environment)
  File "build/bdist.linux-i686/egg/trac/db/api.py", line 77, in transaction_wrapper
    fn(ldb)
  File "build/bdist.linux-i686/egg/trac/env.py", line 601, in upgrade_environment
    script.do_upgrade(self.env, i, cursor)
  File "build/bdist.linux-i686/egg/trac/upgrades/db23.py", line 38, in do_upgrade
    cursor.execute("INSERT INTO revision (repos,rev,time,author,message) "
  File "build/bdist.linux-i686/egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
IntegrityError: columns repos, rev are not unique

Trac	0.12
setuptools	0.6c8
Genshi	0.6
mod_wsgi	3.3
pysqlite	2.4.1
Pygments	0.9
Python	2.5.5
sqlite	3.5.7
subversion	1.6.12

trac configured as standalone server(before upgrade it was launched under apache 2.2.8).

if will try to upgrade svn db again i will receive this:

2010-08-30 15:54:40,310 Trac[env] WARNING: Component <trac.env.EnvironmentSetup object at 0x4094eaac> requires environment upgrade
2010-08-30 15:54:40,311 Trac[env] INFO: Trac database schema version is 22, should be 26
2010-08-30 15:54:40,468 Trac[env] INFO: trac.env.EnvironmentSetup upgrading...
2010-08-30 15:54:41,270 Trac[console] ERROR: Exception in trac-admin command:
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 107, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/local/lib/python2.5/cmd.py", line 218, in onecmd
    return self.default(line)
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 257, in default
    return cmd_mgr.execute_command(*args)
  File "build/bdist.linux-i686/egg/trac/admin/api.py", line 123, in execute_command
    return f(*fargs)
  File "build/bdist.linux-i686/egg/trac/env.py", line 790, in _do_upgrade
    self.env.upgrade(backup=no_backup is None)
  File "build/bdist.linux-i686/egg/trac/env.py", line 533, in upgrade
    with_transaction(self)(participant.upgrade_environment)
  File "build/bdist.linux-i686/egg/trac/db/api.py", line 77, in transaction_wrapper
    fn(ldb)
  File "build/bdist.linux-i686/egg/trac/env.py", line 601, in upgrade_environment
    script.do_upgrade(self.env, i, cursor)
  File "build/bdist.linux-i686/egg/trac/upgrades/db23.py", line 36, in do_upgrade
    cursor.execute(stmt)
  File "build/bdist.linux-i686/egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: table repository already exists

and if i will run it one more time:

2010-08-30 15:55:36,080 Trac[env] WARNING: Component <trac.env.EnvironmentSetup object at 0x4094eaac> requires environment upgrade
2010-08-30 15:55:36,081 Trac[env] INFO: Trac database schema version is 22, should be 26
2010-08-30 15:55:36,260 Trac[env] INFO: trac.env.EnvironmentSetup upgrading...
2010-08-30 15:55:36,265 Trac[console] ERROR: Exception in trac-admin command:
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 107, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/local/lib/python2.5/cmd.py", line 218, in onecmd
    return self.default(line)
  File "build/bdist.linux-i686/egg/trac/admin/console.py", line 257, in default
    return cmd_mgr.execute_command(*args)
  File "build/bdist.linux-i686/egg/trac/admin/api.py", line 123, in execute_command
    return f(*fargs)
  File "build/bdist.linux-i686/egg/trac/env.py", line 790, in _do_upgrade
    self.env.upgrade(backup=no_backup is None)
  File "build/bdist.linux-i686/egg/trac/env.py", line 533, in upgrade
    with_transaction(self)(participant.upgrade_environment)
  File "build/bdist.linux-i686/egg/trac/db/api.py", line 77, in transaction_wrapper
    fn(ldb)
  File "build/bdist.linux-i686/egg/trac/env.py", line 601, in upgrade_environment
    script.do_upgrade(self.env, i, cursor)
  File "build/bdist.linux-i686/egg/trac/upgrades/db23.py", line 5, in do_upgrade
    cursor.execute("CREATE TEMPORARY TABLE rev_old "
  File "build/bdist.linux-i686/egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such table: revision

[Remy Blank]

Something went thoroughly wrong here, it seems your revision table had duplicate rows for a given revision. The follow-up errors are due to the aborted upgrade, as table creation and destruction are not transaction-safe. How this can happen is mysterious, as the rev column is the primary key of the revision table.

Ideally, you should restore your database to the state prior to the upgrade, then remove the duplicate rows from the revision table (that is, find the rows that have the same rev value and leave only one of them), and finally re-try the upgrade.

If that's not possible, it gets tricky. The good news is, there's nothing "important" in these tables that can't be restored from the repository. However, getting back to a consistent state is… tedious. Make sure you have a backup now.

• First, remove all repository-related tables and temporary tables:

  DROP TABLE repository;
  DROP TABLE revision;
  DROP TABLE node_change;
  DROP TABLE rev_old;
  DROP TABLE nc_old;
  

• Then, re-create empty revision and node_change tables:

  CREATE TABLE revision (
      rev text,
      time integer,
      author text,
      message text,
      UNIQUE (repos,rev)
  );
  CREATE INDEX revision_repos_time_idx ON revision (repos,time);
  CREATE TABLE node_change (
      rev text,
      path text,
      node_type text,
      change_type text,
      base_path text,
      base_rev text,
      UNIQUE (repos,rev,path,change_type)
  );
  CREATE INDEX node_change_repos_rev_idx ON node_change (repos,rev);
  

• At that point, you should be able to proceed with the upgrade:

  trac-admin /services/trac-svn upgrade
  

• Finally, resync your repository:

  trac-admin /services/trac-svn repository resync ""
  

This should get up up and running again.

[DimeDroll <dimdroll@…>]

yes, that resolves my problem, thank you very much for your answer. one only notice, there is no column repos in both tables.

but, also this lead me to new problem:

2010-08-31 11:40:48,677 Trac[svn_fs] INFO: Failed to load Subversion bindings
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/trac/versioncontrol/svn_fs.py", line 265, in __init__
    _import_svn()
  File "build/bdist.linux-i686/egg/trac/versioncontrol/svn_fs.py", line 68, in _import_svn
    from svn import fs, repos, core, delta
  File "/usr/local/lib/svn-python/svn/fs.py", line 19, in <module>
    from libsvn.fs import *
  File "/usr/local/lib/svn-python/libsvn/fs.py", line 7, in <module>
    import _fs
ImportError: /usr/local/lib/libsvn_ra_neon-1.so.0: undefined symbol: X509_verify_cert_error_string

Looks like there is something wrong with neon or mod_dav_fs? should i have mod_dav_fs enabled? should i create new ticket?

thank you.

Best regards, Dmitriy

[Remy Blank]

Replying to DimeDroll <dimdroll@…>:

one only notice, there is no column repos in both tables.

The repos column should be added by the upgrade procedure.

but, also this lead me to new problem:

This is most probably an issue with the Python bindings for Subversion. Please take this to the MailingList or IrcChannel, where you should get more help for this InstallationIssue.

[DimeDroll <dimdroll@…>]

thank you, i`m appreciate your involving into this.

maybe this will be helpful for you, my team mate had mostly the same problem few years ago, here is the mailing list about it: ​http://svn.haxx.se/users/archive-2008-04/0870.shtml so i suggested that this problem maybe still not resolved?

anyway, i will try to ask for the assistant in MailingList and IrcChannel.

[Remy Blank]

As mentioned in comment:5, this is an InstallationIssue, not a bug in Trac. More precisely, it's an installation issue with the Python bindings for Subversion. We can't fix that for you, hence closing as "cantfix".

[DimeDroll <dimdroll@…>]

ok, i see. thank you. i will look for help in IRC and Mailing lists.

[anonymous]

CREATE TABLE revision (
    rev text,
    time integer,
    author text,
    message text,
    UNIQUE (repos,rev)
);

wtf ? Error: table revision has no column named repos

[Remy Blank]

Replying to anonymous:

Error: table revision has no column named repos

Right, that was a typo. Remove the repos names from the UNIQUE clauses and indexes.