trac-admin upgrade with PostgreSQL fails due, some specific SQL syntax required

[anonymous]

I'm forced to do a --no-backup upgrade during the installation, since we don't use SQLite (we use PostgreSQL).

The install then fails thusly:

dev trac # trac-admin ./SV upgrade --no-backup
Traceback (most recent call last):
  File "/usr/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11.2', 'console_scripts', 'trac-admin')()
  File "//usr/lib/python2.5/site-packages/trac/admin/console.py", line 1294, in run
    return admin.onecmd(command)
  File "//usr/lib/python2.5/site-packages/trac/admin/console.py", line 123, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "//usr/lib/python2.5/site-packages/trac/admin/console.py", line 1144, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "//usr/lib/python2.5/site-packages/trac/env.py", line 449, in upgrade
    participant.upgrade_environment(db)
  File "build/bdist.linux-i686/egg/tracforms/tracdb.py", line 137, in upgrade_environment
  File "build/bdist.linux-i686/egg/tracforms/formdb.py", line 208, in dbschema_2008_06_15_0000
  File "build/bdist.linux-i686/egg/tracforms/tracdb.py", line 87, in __call__
  File "build/bdist.linux-i686/egg/tracforms/tracdb.py", line 42, in execute
psycopg2.ProgrammingError: syntax error at or near "AUTOINCREMENT"
LINE 3: ...              tracform_id     INTEGER PRIMARY KEY AUTOINCREM...
                                                             ^

I tried download zip's of 0.2 and 0.3.

Setting severity to 'blocker' since the plugin is completely uninstallable at this point.

[dicianno@…]

Ack -- not trying to be anonymous!

[Peter]

Apparently this happens because Postgres does not support (MySQL-specific) AUTOINCREMENT keyword. Yikes!

[Ryan J Ollos]

See #5667.

[Steffen Hoffmann]

Anyone able and willing to test the recommended changes​?

Your feedback would significantly speedup resolving this issue. Thanks in advance.

[Adrian Fritz]

Will test them.

[Adrian Fritz]

Asked to and tested by Nelso (from Boreste). Reported at ​http://trac-hacks.org/ticket/5667#comment:8

[Steffen Hoffmann]

Thanks for forwarding my request. Seems like we're making good progress now. I rejoice when joining with interested developers and users that share ideas, pushing code to the next level.

[Steffen Hoffmann]

(In [10168]) TracFormsPlugin: Add compatibility SQL statements for PostgreSQL, refs #5608 and #5667.

This has been developed from a formdb.py of tracforms-0.2 modified by

19. Noack, further testing done by Nelso. Thanks for your contribution.

Despite remarkable progress this is only nearly working. After some hours of try-n-error I decided to not follow this path any longer. As a matter of fact the whole db code of this plugin is broken by design. I've already straightened it a bit, but the custom db cursor implementation seems evil. Reinventing connection wrapper code by own compatibility was never smart and is bound to be finally brocken after upcoming switch from env.get_db_cnx to the new with_transaction, that has much more intelligent transaction handling in general.

[Steffen Hoffmann]

(In [10216]) TracFormsPlugin: Releasing version 0.4, pushing development to 0.5, closes #5318, #5608, #5667.

Codename 'Nelso'. ;-) This is the first release supporting PostgreSQL for storing forms data, effectively working equally well with all db backends, that are currently supported by Trac itself. Another big step towards a stable plugin. Thanks to all the kind people, who helped with their contributions in many ways. It was another rewarding experience to find the kinks and iron them out. Now everybody enjoy the new Trac power under the hood.