SQLite database ugprade issue on migration from 0.11.x to 0.12, pysqlite < 2.5.5 fails

[caleb@…]

Preface: This is likely an installation / packaging / dependency issue, but possibly something trac could identify and warn about rather than just failing. I have been unable to trace the exact cause.

Background: Upgrade from 0.11.7 to 0.12. OS: PLD-Linux Packages: python 2.6.5, sqlite 3.6.23, pysqlite 2.5.4, genshi 0.6, Babel 0.9.5

I am trying to upgrade our distribution packages for trac to 0.12. After what seems like a successful package build and install, I was prompted by trac that my project databases needed upgrading. No problem just run trac-admin /path/to/project upgrade right? Here is the error I get when doing that:

InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from.

The database is backed up and can be restored for use with 0.11.x but in the resulting state it is usable by neither version.

[Remy Blank]

Could you please provide the full traceback for the error above (from the log)?

Also, do you have any plugins installed? Could you please try upgrading with all plugins disabled (on a copy of your environment)?

[anonymous]

This appears to be a problem with the pysqlite 2.5.4 bindings. I upgraded to 2.6.0 and that particular error goes away (no other changes to the system above). I committed the upgraded pysqlite package, but there is still the compatibility issue with 2.5.x to think about:

The trac website states 2.5.x is not only supported but preferred. Perhaps this should be updated in the docs and properly tested for during packaging or the problem with 2.5.x fixed.

[anonymous]

Replying to rblank:

Could you please provide the full traceback for the error above (from the log)?

There is no log file, just an empty ./log directory. Is this something I should be enabling somewhere?

Also, do you have any plugins installed? Could you please try upgrading with all plugins disabled (on a copy of your environment)?

The projects I tested first question had no extra plugins besides web-admin enabled.

[Christian Boos]

Reproduced, exactly as you said.

With pysqlite 2.5.4:

13:55:34 Trac[env] INFO: Trac database schema version is 21, should be 26
13:55:34 Trac[env] WARNING: Component <trac.env.EnvironmentSetup object at 0x2ac654fa81b0> requires environment upgrade
13:55:34 Trac[env] INFO: Trac database schema version is 21, should be 26
13:55:34 Trac[env] INFO: trac.env.EnvironmentSetup upgrading...
13:55:34 Trac[env] INFO: Upgraded database version from 21 to 22
InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from.
13:55:35 Trac[console] ERROR: Exception in trac-admin command:
Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/admin/console.py", line 107, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/opt/pythond-2.6.5/lib/python2.6/cmd.py", line 218, in onecmd
    return self.default(line)
  File "build/bdist.linux-x86_64/egg/trac/admin/console.py", line 257, in default
    return cmd_mgr.execute_command(*args)
  File "build/bdist.linux-x86_64/egg/trac/admin/api.py", line 123, in execute_command
    return f(*fargs)
  File "build/bdist.linux-x86_64/egg/trac/env.py", line 790, in _do_upgrade
    self.env.upgrade(backup=no_backup is None)
  File "build/bdist.linux-x86_64/egg/trac/env.py", line 533, in upgrade
    with_transaction(self)(participant.upgrade_environment)
  File "build/bdist.linux-x86_64/egg/trac/db/api.py", line 77, in transaction_wrapper
    fn(ldb)
  File "build/bdist.linux-x86_64/egg/trac/env.py", line 601, in upgrade_environment
    script.do_upgrade(self.env, i, cursor)
  File "build/bdist.linux-x86_64/egg/trac/upgrades/db23.py", line 40, in do_upgrade
    cursor.execute("DROP TABLE rev_old")
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 79, in execute
    self.rows = PyFormatCursor.fetchall(self)
InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from.

Re-starting the procedure with 2.6.0, it works.

[Christian Boos]

With 2.5.5 it works as well, must be between 2.5.4 and 2.5.5… The fix must have been pysqlite:changeset:641bee/hg.

[Christian Boos]

And the "problem" has likely been introduced by pysqlite:changeset:35c748, i.e. 2.5.2.

So when using pysqlite versions 2.5.2, 2.5.3 and 2.5.4, one can't upgrade a Trac 0.11 environment, as step 23​ will fail.

The pysqlite versions bundled with Python itself are 2.4.1 (for Python 2.6.5) and I also verified that this version work (Python 2.7rc1 has 2.6.0), so the problem is not that critical.

The question remains whether we should fix it (annoying - we'd need to recreate cursors after every DDL statement in db upgrade scripts), or simply advise people to upgrade. easy_install pysqlite will install 2.5.5, not the latest, but as already mentioned, that version is fine.

[Remy Blank]

I vote for the latter (advise people not to use 2.5.2 - 2.5.4).

[Caleb Maclennan <caleb@…>]

I have noted that pysqlite ≥ 2.5.5 is required in TracDev/ReleaseNotes/0.12. Is there a test for this in the build / install system? Are there other official lists of minimum requirements?

I have also updated the ​PLD specs to reflect this requirement.

[Christian Boos]

Replying to rblank:

I vote for the latter (advise people not to use 2.5.2 - 2.5.4).

Good, that's also my preferred solution. Let's add this info in a few places, TracInstall, TracUpgrade#KnownIssues, PySqlite (caleb already did this for the release notes, I noticed).

[Caleb Maclennan <caleb@…>]

Another place to update: milestone:0.12#InstallationPrerequisites

[Christian Boos]

Replying to Caleb Maclennan <caleb@…>:

I have noted that pysqlite ≥ 2.5.5 is required in TracDev/ReleaseNotes/0.12.

Thanks! Could you also please take care of the other places I mentioned?

Is there a test for this in the build / install system? Are there other official lists of minimum requirements?

We could issue a warning, or simply error out in source:trunk/trac/db/sqlite_backend.py​. I wonder if the problem can manifest itself in other places, like during repository sync(), as there we keep reusing the same cursor after an explicit commit.

[Remy Blank]

Replying to cboos:

or simply error out in source:trunk/trac/db/sqlite_backend.py​.

I would do that, and provide a link to comment:6.

[Caleb Maclennan <caleb@…>]

Replying to cboos:

Thanks! Could you also please take care of the other places I mentioned?

I have fixed TracDev/ReleaseNotes/0.12, TracInstall, TracUpgrade#KnownIssues and PySqlite.

Without the right authorization I cannot update milestone:0.12#instalationPrerequisites. Somebody with a real login will have to do that.

[Remy Blank]

A check for 2.5.2 - 2.5.4 has been added in [10065].