Problem upgrading trac_env

[ma2enator@…]

I just installed TracDiscussion 0.5 and tried to upgrade my trac environment. it stops with the error message:

Command failed: near "ALTER": syntax error

maybe you can help?

[Radek Bartoň]

You have old sqlite or pysqlite version. Please update them.

[osimons]

I also got an error - much like the ticket description. Likely the same statement that gives the error:

bash: > trac-admin discussion-test upgrade
Command failed: near "ADD": syntax error

I have looked at the upgrade code, and tested this a number of times by dropping tables and deleting the system entry. It is quite clear that it is the upgrade 2 that fails.

When I do upgrade 2 'manually' by copying each sql and pasting into sqlite3 (3.3.7) to execute, it all works fine.

However, when I use an a Python shell for testing, it fails:

>>> from trac.env import Environment
>>> myenv = Environment('/Users/trac/dev/discussion-test')
>>> con = myenv.get_db_cnx()
>>> cur = con.cursor()
>>> cur.execute('ALTER TABLE forum ADD COLUMN forum_group integer;')
Traceback (most recent call last):
  File "<input>", line 1, in ?
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/trac/db/sqlite_backend.py", line 44, in execute
    args or [])
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/trac/db/sqlite_backend.py", line 36, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near "ADD": syntax error

I use py-sqlite 2.3.2, and an up-to-date version of Trac trunk (0.10dev/beta1) running on OSX. I am sure my setup is correct.

[Radek Bartoň]

Doesn't it somehow possible that your pysqlite is using sqlite version 2?

[Radek Bartoň]

There is certainly not problem in the code in meaning of bug. There was a few similar tickets in the past that was always invalid because of wrong sqlite or pysqlite installation. If you won't find the problem I could rewrite code so it won't be using ALTER TABLE ADD COLLUMN statement but temporal table for copying old one using ALTER TABLE RENAME TO statement but I'm not sure if this statement was supported in old sqlite implementation. Or you can create all tables manually using sqlite3 console and updating discussion_version value in system table to 2.

[osimons]

Continuing the interactive session above (ie. same cursor object), here is some more digging about versions:

>>> cur.__module__
'trac.db.sqlite_backend'
>>> import trac.db.sqlite_backend
>>> trac.db.sqlite_backend._ver
(3, 1, 3)
>>> trac.db.sqlite_backend.have_pysqlite
2
>>> trac.db.sqlite_backend.sqlite.version
'2.3.2'

That means pysqlite is compiled against the default install in OSX 10.4.7, as /usr/bin/sqlite3 gives me version 3.1.3 - while everything else I have is using DarwinPorts. Looking at py-sqlite source setup.cfg also confirms this as the include and lib paths all point to default OSX. Running the ALTER TABLE... ADD COLUMN... gives the same error using 3.1.3.

Sqlite.org changelog confirms why - support added for 3.2.0: ​http://www.sqlite.org/changes.html

Anyway, I got this working for me - and a spur to fix something I never thought was broken, as it has given me no problem with any other Trac related use or research in recent memory...

Doing the upgrade by copy to temp, drop old, recreate with data copy should likely be the most stable and generally supported way even for old (and still supported) versions. I see that it is the method used for all Trac upgrade scripts.

[Radek Bartoň]

OK, I'll make it that way then.

[Radek Bartoň]

Changed in changeset 1217.

[osimons]

Thanks a bunch! Works flawlessly.

Looking at the latest Trac upgrade script, I see that you could actually select directly into one table from another - and it would make the logic a bit simpler:

​http://trac.edgewall.org/browser/trunk/trac/upgrades/db19.py

[Radek Bartoň]

If you mean lines 20-22, yes it's easier, thank you for note.