This is probably because of a transition from MySQL to PostgreSQL. The automated migration script seems to sometimes mess up the primary key sequences.

To answer your question, I'd first need more information.

Get the database string to connect to the database in the next step (also described here: https://docs.openproject.org/installation-and-operations/operation/restoring/#restoring-the-database):

openproject config:get DATABASE_URL

With the output of that command, connect to the database:

psql [output of the previous command]

Then within the postgresql shell, type:

\d+ cost_objects

Please post the output of that last command back here, so I can take a look at the current naming. Please do not post the value of the DATABASE_URL, though.

Thank you Jens, we have transitioned from MySQL to PostgreSQL in a previous upgrade so that must be the cause.

Here's the info about the cost_objects table:

Should we just rename the primary_key? or there might be relations with more tables?

Note: I got locked out of my account and still can't login again (after waiting hours), so I'm using our team account.

Hi Rodrigo, 

yes, renaming the index should get the migration to run through. 

Issuing ALTER INDEX "idx_20357_primary" RENAME TO "cost_objects_pkey";  in the psql console will do the trick. 

We will also include a fix like this into 11.0.1 so you could also wait for it to be released. But if you want the latest and greatest now, this should get you going.

Just to add: I also needed to rename the index for cost_object_journals.

Thank you!

Thanks Hartwin for the feedback. 

We will release 11.0.1 shortly which should take care of that sort of inconsistencies automatically.

Come on guys, 
10.6.5 to 11.0.1 still contains this faulty behaviour.

You need to change the index for cost_object_journals_pkey, too:

ALTER INDEX "**idx_212437_primary**" RENAME TO "cost_object_journals_pkey";

To get the correct index-name (this can vary), please use this query:

SELECT 
	tablename, indexname, indexdef 
FROM     
	pg_indexes 
WHERE     
	tablename LIKE 'cost_object%' 
ORDER BY     
	tablename, indexname;

Cheers.

Tri

Does sudo openproject run bundle exec rake db:redo VERSION=20190502102512 do nothing on your database then?

It should rename all indices with a wrong name. We even put an extra check into the budgets migration which was supposed to catch this (see https://github.com/opf/openproject/blob/release/11.0/modules/budgets/db/migrate/20200810152654_rename_cost_object_to_budget.rb#L3). I'm not sure why this doesn't do anything in your case.

I was suspecting that the schema name might be an issue but since it's simply called 'public' in your case that will probably not be it.

What would also be of help would be the output of:

sudo openproject run bundle exec rails runner "ActiveRecord::Base.connection.select_one 'SELECT current_schema()'"

Thanks

Thanks for the quick answers, guys.

@Markus:
To be honest, we changed the affected index manually. After that, openproject configure performed flawlessly.
Maybe your solution/ hint might help others, reading this conversation.

@Jens: 
Just in case, if this helps debugging:

sudo openproject run bundle exec rails runner "ActiveRecord::Base.connection.select_one 'SELECT current_schema()'"

/opt/openproject/vendor/bundle/ruby/2.7.0/gems/nokogiri-1.10.10/lib/nokogiri/version.rb:94:in `block in <class:VersionInfo>': WARNING: Nokogiri was built against LibXML version 2.9.4, but has dynamically loaded 2.9.9 (StructuredWarnings::StandardWarning)

Ah sorry, Tri, I forgot to ask for a puts  so that would be 

sudo openproject run bundle exec rails runner "puts ActiveRecord::Base.connection.select_one 'SELECT current_schema()'"

My bad. It would also help if you know the schema name you expect to be working on.

@Jens: 

{"current_schema"=>"public"}

At the risk of annoying you Tri (I really want to fix this problem): What then is the name of the schema your openproject database is on?

=)
I'm happy to help.

The name of the schema is openproject... This seems to be the issue.

Thanks a ton, Tri. That should give us the information we need to fix this one.

U are welcome 

and GG ;)

Hello Tri,

since you did the renaming yourself to fix your problem in the end I don't quite know what the rake db:redo did in the end.
Do you happen to still have a pre-migration backup of which you could do a schema-only dump so I could test it myself, please?

Hi Markus, 
i was able to extract the schema from our october-backup.

Thank you so much!

Looking at the schema dump you have some tables in the public schema and some in the openproject schema.

That may very well explain the issues with the migration. It's probably best if you changed the schema to public for all tables.

There is a function for this defined here:

CREATE OR REPLACE FUNCTION move_schema_to_public(old_schema varchar) RETURNS void LANGUAGE plpgsql VOLATILE AS
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = quote_ident(old_schema)
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(old_schema) || '.' || quote_ident(row.tablename) || ' SET SCHEMA public;';
    END LOOP;
END;
$$;

You can just paste that into a SQL console and then run:

SELECT * FROM move_schema_to_public('openproject');

Hi Markus, 
thank you very much for this.
We've changed the schema to public with your help (look @ my alibi-screenshot).

I wasn't aware of the slight messup with the schemas before this.

You're welcome. That should all be correct for the future then.