Modify

Opened 5 years ago

Last modified 3 years ago

#13321 new defect

ProgrammingError: relation "custom_report" already exists

Reported by: Waqas Owned by: Russ Tyndall
Priority: normal Component: TimingAndEstimationPlugin
Severity: normal Keywords:
Cc: Trac Release: 1.0

Description

I am relatively new to TRacn and am facing an issue with TimingAndEstimationPlugin. This is apparently the same as Ticket 7446 but I already have the fix applied in my version. I am running Trac 1.0.9 on Ubuntu 16.04. I downloaded this plugin from here (1.5.2b). My database is PostgreSQL 9.6.2.

The time field is added to Tikets fine but when I visit Time Reports, I get the following error.

ProgrammingError: relation "custom_report" already exists
File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 554, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 247, in dispatch
  resp = chosen_handler.process_request(req)
File "/opt/trac/plugins/timingandestimationplugin-1.5.2b0-py2.7.egg/timingandestimationplugin/webui.py", line 129, in process_request
  mgr = CustomReportManager(self.env, self.log)
File "build/bdist.linux-x86_64/egg/timingandestimationplugin/reportmanager.py", line 22, in __init__
  self.upgrade()
File "build/bdist.linux-x86_64/egg/timingandestimationplugin/reportmanager.py", line 39, in upgrade
  "CREATE TABLE custom_report ("
File "/opt/trac/plugins/timingandestimationplugin-1.5.2b0-py2.7.egg/timingandestimationplugin/dbhelper.py", line 30, in execute_non_query
  execute_in_trans(env, (sql, params))
File "/opt/trac/plugins/timingandestimationplugin-1.5.2b0-py2.7.egg/timingandestimationplugin/dbhelper.py", line 67, in execute_in_trans
  raise e

I have checked and the table exists in my database. It has the following data.

id uuid maingroup subgroup version
9 b24f08c0-d41f-4c63-93a5-25e18a8513c2 Timing and Estimation Plugin Billing Reports 21
10 af13564f-0e36-4a17-96c0-632dc68d8d14 Timing and Estimation Plugin Billing Reports 18
11 7bd4b0ce-da6d-4b11-8be3-07e65b540d99 Timing and Estimation Plugin Billing Reports 18
12 8d785cdb-dcf5-43c9-b2a6-216997b0011a Timing and Estimation Plugin Ticket/Hour Reports 17
13 71e7c36d-e512-4d0b-b499-087d4d20ff0b Timing and Estimation Plugin Ticket/Hour Reports 17
14 5f33b102-e6a6-47e8-976c-ac7a6794a909 Timing and Estimation Plugin Ticket/Hour Reports 17
15 7816f034-a174-4a94-aed6-358fb648b2fc Timing and Estimation Plugin Ticket/Hour Reports 17
16 03815803-7688-4f3a-8e65-8d254cc1d1fb Timing and Estimation Plugin Ticket/Hour Reports 17
17 040c9025-7641-4d18-96ad-2b26b4095566 Timing and Estimation Plugin Ticket/Hour Reports 17

Please guide on how to fix this. Thanks.

Attachments (0)

Change History (5)

comment:1 Changed 5 years ago by Russ Tyndall

Apologies for the trouble you are experiencing. The most direct way for you to fix it, would be not call the create table by modifying timingandestimationplugin/branches/trac1.0-Permissions/timingandestimationplugin/reportmanager.py@14426#L36

 -   if not dbhelper.db_table_exists(self.env, 'custom_report'):
 +   if False and not dbhelper.db_table_exists(self.env, 'custom_report'):

It seems like the db_table_exists call is failing on your database (and has been problematic in the past, though stable for a while). You could look into debugging that (i will as well, but it may take me a while to get to it).

Last edited 5 years ago by Ryan J Ollos (previous) (diff)

comment:2 Changed 5 years ago by Waqas Khawaja <waqas.kh@…>

No worries and Thanks. You've a great plugin!

I made the change and it works good now. I may not be able to debug myself but I can offer any help you need in terms of info from my installation.

Thanks again.

comment:3 Changed 3 years ago by mdaskalo@…

I have a similar problem to this, which I believe is specific to PostgreSQL database (if this is also your case). I believe the problem lies within the function of current schema. This prevents the plugin to find that the table custom_reports is already existing, because of the way it tries to find the schema where the table is expected to be located. Specifically for PostgreSQL database the following is part of the configuration and is probably not the best possible implementation.

SHOW search_path;

I am speaking about this timingandestimationplugin/branches/trac1.0/timingandestimationplugin/dbhelper.py#L114

The default search path on my PostgreSQL is returned as:

# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

If this is the problem you can workaround it by changing the default search_path (i.e. for the whole database or for the user, etc.).

For example:

alter database YourTracDB set search_path to public;

N.B. When playing with PostgreSQL search_path check also https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

comment:4 Changed 3 years ago by Jun Omae

It seems that the issue is raised when schema is not used on PostgreSQL database. The schema can be specified schema=xxxx parameter in [trac] database option (e.g. database = postgres://tracuser:password@localhost/trac?schema=projname).

I consider we should use current_schema() function in PostgreSQL rather than the current_schema() in dbhelper.py.

Patch (untested):

  • timingandestimationplugin/branches/trac1.0-Permissions/timingandestimationplugin/dbhelper.py

    diff --git a/timingandestimationplugin/branches/trac1.0-Permissions/timingandestimationplugin/dbhelper.py b/timingandestimationplugin/branches/trac1.0-Permissions/timingandestimationplugin/dbhelper.py
    index 362ce21ab..7422d27a6 100644
    a b  
     1from trac.db.api import DatabaseManager
    12import trac.db.pool
    23SqliteCon, PGCon, MySqlCon = None,None,None
    34try:
    def execute_in_nested_trans(env, name, *args): 
    8586    result = True
    8687    c_sql =[None]
    8788    c_params = [None]
    88     with self.env.db_transaction as db:
     89    with env.db_transaction as db:
    8990        cur = None
    9091        try:
    9192            cur = db.cursor()
    def _prep_schema(s): 
    118119                     for i in s.split(',')))
    119120
    120121def db_table_exists(env,  table):
    121     cnt = 0
    122     with env.db_query as db:
    123         if is_db_type(db, SqliteCon):
    124             sql = "select count(*) from sqlite_master where type = 'table' and name = %s"
    125             cnt = get_scalar(env, sql, 0, table)
    126         else:
    127             sql = """SELECT count(*) FROM information_schema.tables
    128                      WHERE table_name = %%s and table_schema in (%s)
    129                   """ % _prep_schema(current_schema(env))
    130             cnt = get_scalar(env, sql, 0, table)
    131     return cnt
     122    connection_uri = DatabaseManager(env).connection_uri
     123    if connection_uri.startswith('sqlite:'):
     124        sql = """SELECT COUNT(*) FROM sqlite_master
     125                 WHERE type = 'table' and name = %s"""
     126    elif connection_uri.startswith('postgres:'):
     127        sql = """SELECT COUNT(*) FROM information_schema.tables
     128                 WHERE table_schema = current_schema()
     129                 AND table_name = %s"""
     130    elif connection_uri.startswith('mysql:'):
     131        sql = """SELECT COUNT(*) FROM information_schema.tables
     132                 WHERE table_name = %%s AND table_schema IN (%s)
     133              """ % _prep_schema(current_schema(env))
     134    else:
     135        raise NotImplementedError()
     136    rows = env.db_query(sql, [table])
     137    return rows[0][0]
    132138
    133139def get_column_as_list(env, sql, col=0, *params):
    134140    data = get_all(env, sql, *params)[1] or ()
Last edited 3 years ago by Jun Omae (previous) (diff)

comment:5 in reply to:  4 Changed 3 years ago by anonymous

Replying to Jun Omae:

I consider we should use current_schema() function in PostgreSQL rather than the current_schema() in dbhelper.py.

Patch (untested):

This worked for me (with a Postgres DB)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The owner will remain Russ Tyndall.

Add Comment


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

 
Note: See TracTickets for help on using tickets.