Modify

Opened 10 years ago

Closed 9 years ago

#2097 closed defect (fixed)

"User" is a reserved keyword in Postgres.

Reported by: anonymous Owned by: Colin Guthrie
Priority: high Component: WorkLogPlugin
Severity: blocker Keywords:
Cc: ecdpalma@… Trac Release: 0.10

Description

The work_log table contains a field named user. This is an SQL reserved keyword:

This means that when will not be able to upgrade the trac database after enable worklog, if you are using Postgres.

The upgrade pretends to succeed but is in fact failing silently:

[20071019-18:46:55]:[root@bugs]:[~]# trac-admin /var/trac/systems upgrade --no-backup
Worklog needs an upgrade
Upgrading Database
Creating work_log table
Done upgrading Worklog
Upgrade done.
[20071019-18:47:05]:[root@bugs]:[~]# trac-admin /var/trac/systems upgrade --no-backup
Worklog needs an upgrade
Upgrading Database
Creating work_log table
Done upgrading Worklog
Upgrade done.

However:

systems=# CREATE TABLE work_log (
systems(#   user       TEXT,
systems(#   ticket     INTEGER,
systems(#   lastchange INTEGER,
systems(#   starttime  INTEGER,
systems(#   endtime    INTEGER,
systems(# );
ERROR:  syntax error at or near "user" at character 27
LINE 2:   user       TEXT,
          ^
systems=# 

Attachments (2)

worklog-postgres_schema_update.diff (10.3 KB) - added by Bryan Allen 10 years ago.
SQL changes required for standards-compliant databases.
worklog.patch (4.6 KB) - added by ecdpalma@… 9 years ago.
Corrects the problem with the "user" column name and the creation of the work_log table, that was being affected by a previous statement.

Download all attachments as: .zip

Change History (4)

Changed 10 years ago by Bryan Allen

SQL changes required for standards-compliant databases.

comment:1 Changed 9 years ago by Carsten Fechtmann

Priority: normalhigh
Severity: normalblocker

Patch above is not applicable for 0.11b1 and postgres-users at least not at revision r2741

Plugin is broken as is when using postgress as DB, and even manages to bring trac down, unless it is disabled again in trac.ini by hand. Also see ticket #2106

I tried to apply the patch manually (as in changing all the user -> username) but there seems to be more than that wrong. E.g. work_log table is not created at all, WorklogPlugin_Db_Version in system is not set / changed etc.

Even when work_log table and system entry are created separately, and although trac-admin upgrade stops complaining, work-log is still not working, i.e. only producing an error message.

I hope someone else got an idea how to solve this, as I just plain run out of ideas ... (or at least SQL knowledge)

Changed 9 years ago by ecdpalma@…

Attachment: worklog.patch added

Corrects the problem with the "user" column name and the creation of the work_log table, that was being affected by a previous statement.

comment:2 Changed 9 years ago by ecdpalma@…

Cc: ecdpalma@… added; anonymous removed
Resolution: fixed
Status: newclosed

I discovered that if I surround user with double quotes, it works. Here goes a patch that corrects another things also. I found that in api.py, the statement

cursor.select(SELECT * FROM work_log LIMIT 1)

leads to a strange state, so the table could not be created in the next statement. So I put a rollback in the exception.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Colin Guthrie.
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.