Modify

Opened 16 years ago

Closed 16 years ago

Last modified 16 years ago

#640 closed defect (fixed)

Problem upgrading trac_env

Reported by: ma2enator@… Owned by: Radek Bartoň
Priority: low Component: DiscussionPlugin
Severity: blocker Keywords:
Cc: Trac Release: 0.9

Description

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?

Attachments (0)

Change History (9)

comment:1 Changed 16 years ago by Radek Bartoň

Status: newassigned

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

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

comment:3 Changed 16 years ago by Radek Bartoň

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

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

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

comment:6 Changed 16 years ago by Radek Bartoň

OK, I'll make it that way then.

comment:7 Changed 16 years ago by Radek Bartoň

Resolution: fixed
Status: assignedclosed

Changed in changeset 1217.

comment:8 Changed 16 years ago by 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

comment:9 Changed 16 years ago by Radek Bartoň

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Radek Bartoň.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


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

 
Note: See TracTickets for help on using tickets.