ProgrammingError: relation "custom_report" already exists

[Waqas]

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.

[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).

[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.

[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

[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

@@ +1 @@
+from trac.db.api import DatabaseManager
 import trac.db.pool
 SqliteCon, PGCon, MySqlCon = None,None,None
 try:
@@ -85 +86 @@
     result = True
     c_sql =[None]
     c_params = [None]
-    with self.env.db_transaction as db:
+    with env.db_transaction as db:
         cur = None
         try:
             cur = db.cursor()
@@ -118 +119 @@
                      for i in s.split(',')))
 
 def db_table_exists(env,  table):
-    cnt = 0
-    with env.db_query as db:
-        if is_db_type(db, SqliteCon):
-            sql = "select count(*) from sqlite_master where type = 'table' and name = %s"
-            cnt = get_scalar(env, sql, 0, table)
-        else:
-            sql = """SELECT count(*) FROM information_schema.tables
-                     WHERE table_name = %%s and table_schema in (%s)
-                  """ % _prep_schema(current_schema(env))
-            cnt = get_scalar(env, sql, 0, table)
-    return cnt
+    connection_uri = DatabaseManager(env).connection_uri
+    if connection_uri.startswith('sqlite:'):
+        sql = """SELECT COUNT(*) FROM sqlite_master
+                 WHERE type = 'table' and name = %s"""
+    elif connection_uri.startswith('postgres:'):
+        sql = """SELECT COUNT(*) FROM information_schema.tables
+                 WHERE table_schema = current_schema()
+                 AND table_name = %s"""
+    elif connection_uri.startswith('mysql:'):
+        sql = """SELECT COUNT(*) FROM information_schema.tables
+                 WHERE table_name = %%s AND table_schema IN (%s)
+              """ % _prep_schema(current_schema(env))
+    else:
+        raise NotImplementedError()
+    rows = env.db_query(sql, [table])
+    return rows[0][0]
 
 def get_column_as_list(env, sql, col=0, *params):
     data = get_all(env, sql, *params)[1] or ()

[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)