• Sign In
  1. Messages

database upgrade

Installation Forum (Inactive)

View Message

view list print
database upgrade tgal  2012-02-10 09:18
Status: Closed
 
Hi,

I was upgrading our labkey installation from version 10.1 to version 11.3 and got the following error:

500: Unexpected server error

A failure occurred during LabKey Server startup.

org.labkey.api.data.SqlScriptRunner$SqlScriptException: exp-10.20-10.30.sql : ERROR: cannot alter type of a column used by a view or rule
  Detail: rule _RETURN on view cabig.experiment depends on column "hypothesis"
       at org.labkey.api.data.SqlScriptManager.runScript(SqlScriptManager.java:132)
       at org.labkey.api.data.SqlScriptRunner.runScripts(SqlScriptRunner.java:103)
       at org.labkey.api.module.DefaultModule.versionUpdate(DefaultModule.java:254)
       at org.labkey.api.module.ModuleUpgrader.upgrade(ModuleUpgrader.java:59)
       at org.labkey.api.module.ModuleUpgrader$1.run(ModuleUpgrader.java:75)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot alter type of a column used by a view or rule
  Detail: rule _RETURN on view cabig.experiment depends on column "hypothesis"
       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
       at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
       at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:260)
       at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:260)
       at org.labkey.api.data.dialect.StatementWrapper.execute(StatementWrapper.java:979)
       at org.labkey.api.data.Table.execute(Table.java:204)
       at org.labkey.api.data.Table.execute(Table.java:428)
       at org.labkey.api.data.SqlScriptParser$Block.execute(SqlScriptParser.java:164)
       at org.labkey.api.data.SqlScriptParser.execute(SqlScriptParser.java:61)
       at org.labkey.core.dialect.PostgreSql83Dialect.runSql(PostgreSql83Dialect.java:750)
       at org.labkey.core.dialect.PostgreSql84Dialect.runSql(PostgreSql84Dialect.java:24)
       at org.labkey.api.data.SqlScriptManager.runScript(SqlScriptManager.java:127)
       ... 4 more

We are on Ubuntu Linux server, tomcat6 and pstgres8.4.

Thanks.
 
 
Brian Connolly responded:  2012-02-10 09:35
Tamas,

I am sorry you are having this problem. To diagnose this problem, we will need more information. Can you send us the tomcat and labkey log files. The files I am interested in are

- labkey.log
- labkey-error.log
- catalina.out

These files will be in the logs sub-directory of the TOMCAT installation directory. If you can zip up these log files and attach them to your response it would be great.


-Brian
 
tgal responded:  2012-02-10 12:29
Thanks Brian.

To eliminate unrelated issues I did a clean install on a brand new VM. I copied the original production database and pointed the new labkey install to the new db copy. I got the same results. Please find attached the log files.

Thanks,
Tamas
 
Brian Connolly responded:  2012-02-10 13:02
Tamas,

Thank you for sending the log files. I have attached a SQL script which you will need to run. This script will remove the caBig views that are causing these problems.

To fix the problem, you will need to

1) Stop Tomcat server
2) If you have not taken a backup of your database prior to upgrading, I recommend taking a database backup now.
3) Using either pgAdmin or psql run the attached script on the labkey database.

 - if you want to use psql. Log onto your PostgreSQL server and the command will look similar to

  psql -d labkey -U USERNAME -h 127.0.0.1 -f cabig-drop.sql
    
   - where USERNAME is the username specified in the LabKey server's configuration file (labkey.xml)
   - you will most likely be asked to enter a password, use the password specified in the LabKey server's configuration file (labkey.xml)
4) Start the tomcat server

The upgrade process should start again and should complete successfully.

-Brian
 
tgal responded:  2012-02-10 13:31
Thanks Brian,

The script helped but I got another error this time (attached).

Thanks,
Tamas
 
Brian Connolly responded:  2012-02-10 17:44
Tamas,

I am sorry for the delay in getting back to you. We have reviewed the logs and looks like you have hit a bug in our installation process. We are investigating the fix to this bug and should have a solution by first thing Monday morning. I will keep you updated with the progress.

Did you make a backup of your database before the upgrade began? If so, please restore that database backup.

-Brian
 
tgal responded:  2012-02-12 18:51
Brian,

I was able to upgrade to version 11.2 and from there to 11.3. These upgrades went through without errors so I'm in good shape now.

Thanks for your help.
Tamas
 
Brian Connolly responded:  2012-02-13 08:36
Tamas,

That is great news. We were going to suggest a similar workaround to the problem for you.

I am glad you are up and running again and sorry for any hassles this might have caused.

-Brian