'relation "custom_report" does not exist' running trac-admin ... upgrade

[anonymous]

This is the relevant fragment of the log file:

2010-08-02 16:53:43,178 Trac[api] DEBUG: T&E NEEDS UP?: sys:False, rep:True, stats:True, fields:True, man:True
2010-08-02 16:53:43,178 Trac[env] WARNING: Component <timingandestimationplugin.api.TimeTrackingSetupParticipant object at 0x25106d0> requires environment upgrade
2010-08-02 16:53:43,183 Trac[reportmanager] DEBUG: T&E Starting Report Schema Upgrade
2010-08-02 16:53:43,201 Trac[reportmanager] DEBUG: T&E Ending Report Schema Upgrade
2010-08-02 16:53:43,204 Trac[dbhelper] ERROR: There was a problem executing sql:SELECT custom_report.subgroup,report.id,report.title, custom_report.version, custom_report.uuid FROM custom_report LEFT JOIN report ON custom_report.id=report.id WHERE custom_report.maingroup=%s ORDER BY custom_report.subgroup,custom_report.ordering 
 with parameters:('Timing and Estimation Plugin',)
Exception:relation "custom_report" does not exist
LINE 1: ...e, custom_report.version, custom_report.uuid FROM custom_rep...
                                                             ^
Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/timingandestimationplugin/dbhelper.py", line 9, in get_all
    cur.execute(sql, params)
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: relation "custom_report" does not exist
LINE 1: ...e, custom_report.version, custom_report.uuid FROM custom_rep...

My database backend is postgresql, I'm using the most recent version of plugin from SVN.

[Russ Tyndall]

I cannot recreate this error.

I just installed a fresh trac with a copy of the latest version of the plugin. I also use postgres so I don't see that as being an issue probably. I probably wont be able to solve it without being able to recreate the bug on my end first.

These lines indicate that the plugin at least thought it did a good job of upgrading the report schema.

2010-08-02 16:53:43,183 Trac[reportmanager] DEBUG: T&E Starting Report Schema Upgrade
2010-08-02 16:53:43,201 Trac[reportmanager] DEBUG: T&E Ending Report Schema Upgrade

Please provide accurate version numbers for most of the important stuff (db, trac, plugin). Also if you can query your trac database's system table and put the versions listed there on the ticket, it might help.

Thanks and sorry it didnt work right for you, hopefully we can resolve this soon.

Russ

ps:

If you just want it to work, you might get away with running the following sql then retrying the upgrade, though this is not ideal for solving the problem more generally.

CREATE TABLE custom_report (
  id         INTEGER,
  uuid       VARCHAR(64),
  maingroup  VARCHAR(255),
  subgroup   VARCHAR(255),
  version    INTEGER,
  ordering   INTEGER)

[anonymous]

I already got away with commenting out the line where it checks version in the report manager and putting version=0 in there, running the upgrade and then putting everything back to how it was.

I'm using postgres 8.4.4 database, which was migrated from sqlite3, the released trac 0.12 and the code checked out yesterday from SVN for the plugin.

The database was previously migrated from trac 0.11 and I suspect it had some leftover tables from when the previous admin was taking care of it -- it is even possible that it had this plugin installed previously and later uninstalled.

Unfortunately that trac is not public, so I can't provide a database dump. I will let you know if I stumble upon that problem again with one of the public tracs.

I'm sorry for the trouble and thanks for working on this plugin, it's awesome.

[anonymous]

This is probably the result of all sql commands after the initial failure with custom_reports table being ignored: when I try to set a billable date, I get:

ProgrammingError: relation "bill_date" does not exist LINE 2: INSERT INTO bill_date (time, set_when, str_value) ^

I think I will need to clean that database up a little...

[Russ Tyndall]

Unfortunately the trac 12 release, made handling errors during database setup harder.

These should be the only other database schema changes you might need to worry about. There should also be some data inserted to get the reports into the correct tables. Hopefully an upgrade with the database schemas already up to date, can get you to where you need to be and get the reports inserted (if they have not been)

            UPDATE system SET value='1' WHERE name='custom_report_manager_version';

            CREATE TABLE bill_date (
            time integer,
            set_when integer,
            str_value text
            );

            UPDATE system SET value = '6' WHERE name= 'TimingAndEstimationPlugin_Db_Version';

HTH, Russ

[Russ Tyndall]

I think on another ticket, he said he had a different version / plugin installed previously that was conflicting. I think he got it fixed with manual cleanup.

​http://trac-hacks.org/ticket/7448#comment:1

If I am wrong about this feel free to reopen

[Brad Fritz]

There may still be a legitimate bug here in dbhelper.py's current_schema() method​ as of r11257.

I ran into the same error when upgrading from Server A:

• Trac 0.11.1
• Python 2.5
• Postgres 8.3.15
• TimingAndEstimationPlugin 0.6.9

to Server B:

• Trac 0.12.3rc1
• Python 2.6.6
• Postgres 8.4.8
• TimingAndEstimationPlugin SVN r11257

Upgrade process:

1. dump Trac database from A
2. restore database on B
3. run trac-admin /path/to/trac upgrade --no-backup
4. install and enable T&E plugin
5. re-run trac-admin /path/to/trac upgrade --no-backup

In my case the custom_report table existed on A and was restored on B in step 2 above. But I still see this in the debug logs:

Feb  6 16:34:49 projects Trac[api] DEBUG: T&E BEGIN Reports need an upgrade check
Feb  6 16:34:49 projects Trac[reportmanager] DEBUG: T&E Checking for custom_report upgrade

followed by the ProgrammingError: relation "custom_report" already exists error.

From the postgres logs:

2012-02-06 16:34:49 EST LOG:  duration: 0.279 ms  statement: SET client_encoding TO 'UNICODE'
2012-02-06 16:34:49 EST LOG:  duration: 0.070 ms  statement: BEGIN
2012-02-06 16:34:49 EST LOG:  duration: 2.095 ms  statement: SELECT value FROM system WHERE name=E'TimingAndEstimationPlugin_Db_Version'
2012-02-06 16:34:49 EST LOG:  duration: 0.121 ms  statement: SELECT value FROM system WHERE name='database_version'
2012-02-06 16:34:49 EST LOG:  duration: 0.141 ms  statement: SELECT value FROM system WHERE name=E'custom_report_manager_version'
2012-02-06 16:34:49 EST LOG:  duration: 0.091 ms  statement: SHOW search_path;
2012-02-06 16:34:49 EST LOG:  duration: 3.668 ms  statement: SELECT count(*) FROM information_schema.tables 
	                 WHERE table_name = E'custom_report' and table_schema=E'"$user",public'
2012-02-06 16:34:49 EST ERROR:  relation "custom_report" already exists
2012-02-06 16:34:49 EST STATEMENT:  CREATE TABLE custom_report (id         INTEGER,uuid       VARCHAR(64),maingroup  VARCHAR(255),subgroup   VARCHAR(255),version    INTEGER,ordering   INTEGER)
2012-02-06 16:34:49 EST LOG:  duration: 0.026 ms  statement: ROLLBACK

The root of the problem seems to be current_schema() not properly parsing the SHOW search_path; output:

trac=> SHOW search_path;

  search_path   
----------------
 "$user",public
(1 row)

so the db_table_exists() call checking for custom_report returns False instead of True. Looks like this is related to #9629.

[Russ Tyndall]

Replying to bradfritz:

There may still be a legitimate bug here in dbhelper.py's current_schema() method​ as of r11257.

Thanks for the detailed error report. When you specify a schema in the trac connection string it comes through as that single schema name. Since that change was written in an environment where we use many trac databases inside a single postgres db, using schemas, we were always seeing a single schema name there.

[trac]
database = postgres://trac:traciestTRACthatEvarTr4cced@/trac?schema=test

Please try version 1.2.5 and see if this resolves the error for you.

Cheers, Russ

[Russ Tyndall]

(In [11260]) fixing bugs in single instance postgres with schemas and db_table_exists fix #7446

[Brad Fritz]

Confirmed fixed. The new print output produces:

SELECT count(*) FROM information_schema.tables 
                 WHERE table_name = %s and table_schema in ('"$user"','public')

...every time trac-post-commit.py is called. :-)

Thank you!

[Russ Tyndall]

(In [11261]) removed extraneous print that shouldnt have been committed re #7446

[Russ Tyndall]

Can't believe I committed my debug print statement. Oh well, glad that resolved the issues you were experiencing. Hopefully this wont mess up any other random configuration.