Opened 7 years ago
Last modified 6 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:2 Changed 7 years ago by
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 6 years ago by
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 follow-up: 5 Changed 6 years ago by
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 1 from trac.db.api import DatabaseManager 1 2 import trac.db.pool 2 3 SqliteCon, PGCon, MySqlCon = None,None,None 3 4 try: … … def execute_in_nested_trans(env, name, *args): 85 86 result = True 86 87 c_sql =[None] 87 88 c_params = [None] 88 with self.env.db_transaction as db:89 with env.db_transaction as db: 89 90 cur = None 90 91 try: 91 92 cur = db.cursor() … … def _prep_schema(s): 118 119 for i in s.split(','))) 119 120 120 121 def 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] 132 138 133 139 def get_column_as_list(env, sql, col=0, *params): 134 140 data = get_all(env, sql, *params)[1] or ()
comment:5 Changed 6 years ago by
Replying to Jun Omae:
I consider we should use
current_schema()
function in PostgreSQL rather than thecurrent_schema()
in dbhelper.py.Patch (untested):
This worked for me (with a Postgres DB)
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
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).