Modify

Opened 7 years ago

Closed 7 years ago

#2097 closed defect (fixed)

"User" is a reserved keyword in Postgres.

Reported by: anonymous Owned by: coling
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 bda 7 years ago.
SQL changes required for standards-compliant databases.
worklog.patch (4.6 KB) - added by ecdpalma@… 7 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 7 years ago by bda

SQL changes required for standards-compliant databases.

comment:1 Changed 7 years ago by fecht

  • Priority changed from normal to high
  • Severity changed from normal to blocker

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 7 years ago by ecdpalma@…

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 7 years ago by ecdpalma@…

  • Cc ecdpalma@… added; anonymous removed
  • Resolution set to fixed
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Action
as closed The owner will remain coling.
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.