[4.7] sql error after upgrading

Bug and problem reporting on Cyclos 4 version

Moderators: rmvanarkel, hugo, alexandre

Post Reply
simonjwoolf
Posts: 113
Joined: Mon May 28, 2012 8:49 am

[4.7] sql error after upgrading

Post by simonjwoolf »

After upgrading a Cyclos 4.6.2 system to 4.7, we see this error in catalina.out after restarting tomcat:

Code: Select all

Internal Exception: org.postgresql.util.PSQLException: ERROR: column "basic_profile_field_account_title" does not exist
I'm guessing the database upgrade didn't run properly. What is the best solution? Is there a way to force the database upgrade routine to run again?


Tomcat 7
Postgres 9.5
openJDK 1.8.0_91

ubuntu 14.04
---
Simon Woolf
Cyclos Consultant
alexandre
Posts: 950
Joined: Wed Sep 06, 2006 9:06 am

Re: [4.7] sql error after upgrading

Post by alexandre »

Hi,

You need to inspect the tomcat catalina.out logs to see what was the problem.

You can run this command to back your database to 4.6.2 and then upgrade again.
Open a postgresql client and execute the following command at your database:

update application set app_version='4.6', db_version = 46;

Regards
Alexandre Caurrinhos
Cyclos development team.
simonjwoolf
Posts: 113
Joined: Mon May 28, 2012 8:49 am

Re: [4.7] sql error after upgrading

Post by simonjwoolf »

Thanks Alexandre.

My database version is currently at 39 - isn't that a bit odd?

update: just tried as you said. After a restart, the app version is once again 4.7 but the database version has been reset back to 39!
---
Simon Woolf
Cyclos Consultant
simonjwoolf
Posts: 113
Joined: Mon May 28, 2012 8:49 am

Re: [4.7] sql error after upgrading

Post by simonjwoolf »

OK so the issue seems to be a bit more complicated...

All the db upgrades are failing, due to "ERROR: must be owner of relation custom_operations"

This seems to indicate some permissions problem, but I have no idea why that would be. Any thoughts?

Code: Select all

WARN DatabaseUpgrade - There were some errors while upgrading to version 37. However, it is possible that the database is still consistent, depending on the statement (for example, a 'drop table' that failed because the table does not exist)
14:07:41,046  INFO DatabaseUpgrade - Upgrading to database version 38
14:07:41,047 ERROR ScriptHelper - Error on statement: alter table operator_groups add column redeem_vouchers boolean
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table operator_groups add column redeem_vouchers boolean]; nested exception is org.postgresql.util.PSQLException: ERROR: must be owner of relation operator_groups

Code: Select all

WARN DatabaseUpgrade - There were some errors while upgrading to version 38. However, it is possible that the database is still consistent, depending on the statement (for example, a 'drop table' that failed because the table does not exist)
14:07:41,053  INFO DatabaseUpgrade - Upgrading to database version 39
14:07:41,054 ERROR ScriptHelper - Error on statement: alter table vouchers add column redeem_by_id bigint
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table vouchers add column redeem_by_id bigint]; nested exception is org.postgresql.util.PSQLException: ERROR: must be owner of relation vouchers

Code: Select all

ERROR ScriptHelper - Error on statement: alter table vouchers add constraint fk_vouchers_redeem_by_id foreign key (redeem_by_id) references users (id)
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [alter table vouchers add constraint fk_vouchers_redeem_by_id foreign key (redeem_by_id) references users (id)]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

Code: Select all

WARN DatabaseUpgrade - There were some errors while upgrading to version 39. However, it is possible that the database is still consistent, depending on the statement (for example, a 'drop table' that failed because the table does not exist)
14:07:46,784  WARN XmlWebApplicationContext - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.cyclos.impl.utils.IdMask': Invocation of init method failed; nested exception is javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "basic_profile_field_account_title" does not exist
---
Simon Woolf
Cyclos Consultant
alexandre
Posts: 950
Joined: Wed Sep 06, 2006 9:06 am

Re: [4.7] sql error after upgrading

Post by alexandre »

Hi,

Seems that the user that are set in cyclos.properties is not the owner of the tables.
You can fix this by listing all tables to have a look at the current owner.
Then you can fix running this command to reassign all to the correct user:

reassign owned by olduser to newuser;
alter database dbname owner to newuser;

after this roll back your database to cyclos 4.6 again with the command I sent on the other post and start the upgrade again.

regards
Alexandre Caurrinhos
Cyclos development team.
Post Reply