problem upgrading 15.2 to 15.3

Installation Forum (Inactive)
problem upgrading 15.2 to 15.3 pablo escobarlopez  2016-01-27 09:13
Status: Closed
 
Hi

I am trying to upgrade 15.2 to 15.3 in our devel instance. I have done some upgrades before without issues but now I am getting a weird error. This is my environment running in centos6:

Core Database Configuration
Server URL jdbc:postgresql://127.0.0.1/labkey
Product Name PostgreSQL
Product Version 9.2.8
JDBC Driver Name PostgreSQL Native Driver
JDBC Driver Version PostgreSQL 9.4 JDBC4.1 (build 1201)
JDBC Driver Location file:/opt/apache-tomcat-7.0.53/lib/postgresql.jar

Runtime Information
Mode Production
Asserts disabled
Servlet Container Apache Tomcat/7.0.53
Java Runtime 1.8.0_40
Java Home /opt/jdk1.8.0_40/jre
Username labkey
User Home Dir /home/labkey
Webapp Dir /opt/LabKey15.2-39071.18-bin/labkeywebapp
OS Linux
Working Dir /home/labkey
Server GUID 26b65700-c331-1031-a374-504196604ae0
Server Time 27/01/2016 17:40

but when doing the post-upgrade steps in the web interface I get this in the logs:
https://gist.github.com/pescobar/11468150968f960f16ed

I think the interesting lines are these ones:
https://gist.github.com/pescobar/11468150968f960f16ed#file-gistfile1-txt-L196
https://gist.github.com/pescobar/11468150968f960f16ed#file-gistfile1-txt-L243
https://gist.github.com/pescobar/11468150968f960f16ed#file-gistfile1-txt-L334
https://gist.github.com/pescobar/11468150968f960f16ed#file-gistfile1-txt-L383

Trying to figure out what's going on I went to the admin console >> diagnostic >> database check >> do database check and then I get this error:

Checking Container Column References...

Checking PropertyDescriptor and DomainDescriptor consistency...
   ERROR: Inconsistent project ids found for exp.domaindescriptor in folder /Shared/Demo Clinical Study Published
   ERROR: Inconsistent project ids found for exp.domaindescriptor in folder /home/Security Tutorial/Study
   ERROR: Inconsistent project ids found for exp.domaindescriptor in folder /home/Security Tutorial/Lab A
   ERROR: Inconsistent project ids found for exp.domaindescriptor in folder /home/Security Tutorial/Lab B
   ERROR: Inconsistent project ids found for exp.domaindescriptor in folder /Shared/Demo Clinical Study Published

When I try "click here to attempt recovery ." I get this error:

500: Unexpected server error
SqlExecutor.execute(); bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "domainid" does not exist Position: 52

An this is the complete log when trying the recover
https://gist.github.com/pescobar/d8ed83836a5bb2967d30

I have also notice that the postgres driver jar is "PostgreSQL 9.4 JDBC4.1 (build 1201)" but I am using postgres 9.2 installed fro the centos SCL repositories (https://wiki.centos.org/AdditionalResources/Repositories/SCL). I was thinking about upgrading postgres to 9.4 but in the labkey compatibility table I see that postgres 9.2 is supported so I am not sure if this could be the problem.

does anyone knows which could be the issue?

Thanks in advance for any help or suggestion.

regards,
Pablo.
 
 
Jon (LabKey DevOps) responded:  2016-01-27 19:55
Hi Pablo,

Looking at your full log here (https://gist.github.com/pescobar/11468150968f960f16ed), the problem appears to be with the study module and the SQL scripts tied to it.

Let me confirm whether this is something that is recoverable or if a clean installation of LabKey is going to have to be performed to correct this problem.

Thank you for your patience.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2016-01-27 20:36
Hi Pablo,

Also, were you doing anything prior to that error message? What actions were you performing within LabKey at the time?

Regards,

Jon
 
pablo escobarlopez responded:  2016-01-27 23:49
Hi Jony,

thanks for your help.

This is our devel instance and we were not using it before or during the ugprade.

I think the first version we installed in this machine (clean install) was 15.1 (or maybe a previous version) and we have followed the upgrade procedure for each new labkey release since then.

regards,
Pablo.
 
Jon (LabKey DevOps) responded:  2016-01-28 15:30
Thanks Pablo,

We think this might be recoverable, but we would like you to run this query directly on the dev database of yours and send us the results:


SELECT domainid FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset)


This should show us the domainid values (if there are any) from the exp.domaindescriptor table based on the criteria in the query. Once we know this, we can proceed from there.

Regards,

Jon
 
pablo escobarlopez responded:  2016-01-29 00:43
Hi Jon,

This is the output of the query:

labkey=> SELECT domainid FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset);
 domainid
----------
      171
      173
      174
      175
(4 rows)

While reviewing the configuration I noticed that the developers in my lab added another database in the labkey config file for some tests (this is our devel instance). I don't know if this can be affecting somehow. Should I remove this database from the config file? This is what I have in my config file $TOMCAT_HOME/conf/Catalina/localhost/labkey.xml

    <Resource name="jdbc/labkeyDataSource" auth="Container"
        type="javax.sql.DataSource"
        username="labkey"
        password="XXXXXXXXXXXXX"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://127.0.0.1/labkey"
        maxActive="20"
        maxIdle="10"
        accessToUnderlyingConnectionAllowed="true"
        validationQuery="SELECT 1"
        />

    <Resource name="jdbc/pgDataSource" auth="Container"
        type="javax.sql.DataSource"
        username="labkey"
        password="XXXXXXXXXXXXXX"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://127.0.0.1/labkey_external"
        maxActive="20"
        maxIdle="10"
        accessToUnderlyingConnectionAllowed="true"
        validationQuery="SELECT 1"
        />
 
Jon (LabKey DevOps) responded:  2016-01-31 20:49
Hi Pablo,

Thanks for the query results. Our developers suspect that you may have orphaned objects in your database that are in relation to the domaindescriptor table.

Can you run the following query on your database? This should give us the rest of the picture we need:



SELECT * FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset);


Also, the extra XML line is totally fine and unrelated. It's just adding another datasource for LabKey to access. (More info available here: https://www.labkey.org/home/Documentation/wiki-page.view?name=externalSchemas)

Regards,

Jon
 
pablo escobarlopez responded:  2016-02-01 00:58
Hi Jon

The query gives no results. This is the output:

labkey=# SELECT * FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset);
 domainid | name | domainuri | description | container | project | storagetablename | storageschemaname | _ts | modifiedby | modified
----------+------+-----------+-------------+-----------+---------+------------------+-------------------+-----+------------+----------
(0 rows)

thanks again for your help

regards,
Pablo.
 
Jon (LabKey DevOps) responded:  2016-02-01 10:59
Hi Pablo,

No results? That's odd. The previous query was not that different from this one, except we're trying to pull all fields rather than just one.

Can you re-run both queries this time?

This one gave us the four results from before:


SELECT domainid FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset);


This gave you no results:


SELECT * FROM exp.domaindescriptor WHERE domainuri like '%:StudyDataset%Folder-%' and domainuri not in (SELECT typeuri from study.dataset);


Either both will give you four results or no results.

Regards,

Jon
 
pablo escobarlopez responded:  2016-02-02 00:25
ups, excuse me. I was doing few things in parallel and I am not sure what I did wrong. Here you have the right output of both queries. I have pasted it in github gist to keep the format so it's easier to read.

https://gist.github.com/pescobar/bfcb8f8fa745c02a574d

thanks again for your help.

best regards,
Pablo.
 
Jon (LabKey DevOps) responded:  2016-02-03 11:18
Hi Pablo,

Thanks for re-running those queries. I'm glad the results populated!

What we believe is happening is that a folder was moved across projects from before and that created this problem we're seeing now.

With the current state of the server, we're not sure if it is possible to hit these respective URLs on a server that is running a copy of that database:

http://localhost:8080/labkey/admin/00f5cfd6-5de7-1032-985c-5d5c86b279ce/folderManagement.view?tabId=info

http://localhost:8080/labkey/admin/d9063732-c32d-1031-9d41-5041966045e0/folderManagement.view?tabId=info

However, these pages would still manage to tell us if the container is no longer under that the exp.domaindescriptor table thinks it belongs to. From here, doing a modification of the table to correct the issue should allow the upgrade to occur.

Can you hit those URLs and let us know the results? You may need to change the hostname respectively based on your server's own settings.

Regards,

Jon
 
pablo escobarlopez responded:  2016-02-04 00:31
Hi Jon,

I have asked the developers in my lab and they told me that maybe they moved some folders but they are not 100% sure. They also told me that they thought that moving folders was a supported feature in Labkey. Should I tell them not to do this?

I have tried the two urls you sent me and both work. I have uploaded two screenshot so you can see what I get when accessing those urls. http://imgur.com/a/6i5qr

thanks again for you help.

best regards,
Pablo.

EDIT: Developers in my lab just told me that they moved some folders following this documentation
https://www.labkey.org/home/Documentation/wiki-page.view?name=manageProjects&_docid=wiki%3Aaa644ff8-12e8-102a-a590-d104f9cdb538
 
Jon (LabKey DevOps) responded:  2016-02-04 18:58
Hi Pablo,

Thanks for the screenshots and the additional information. This helps greatly and confirmed our suspicions about the project/container being mismatched. There were a few bugs previously that were the cause of this problem, but we've found several ways of working around these folder/project moves.

This is what we suggest you do to fix the issue.

1. Access the following URL from the server: http://localhost:8080/labkey/admin/Shared/folderManagement.view?tabId=info

2. Obtain the EntityId value. You'll need this when you access your database.

3. From the database, run the following UPDATE statements and make sure you the entity ID from step 2:

First UPDATE:


UPDATE exp.domaindescriptor SET Project = 'ENTITY_ID_FROM_STEP_2' WHERE domainuri LIKE '%:StudyDataset%Folder-%' AND domainuri NOT IN (SELECT typeuri FROM study.dataset);


Second UPDATE:


UPDATE exp.propertydescriptor SET Project = 'ENTITY_ID_FROM_STEP_2' WHERE PropertyId IN (SELECT d.propertyid FROM exp.propertydomain d, exp.domaindescriptor dd WHERE d.domainId = dd.domainId AND domainuri LIKE '%:StudyDataset%Folder-%' AND domainuri NOT IN (SELECT typeuri FROM study.dataset));


4. After you ran your two UPDATE statements, restart the Tomcat service and re-try your upgrade again.

The steps above should resolve the issue and allow the upgrade to finish.

Regards,

Jon
 
pablo escobarlopez responded:  2016-02-05 01:21
Hi Jon,

thank you very much for your help again! :)

After running the sql queries the upgrade procedure worked fine. I have asked the developers in my lab to check if everything is working as it should and they told me that in the first look everything seems to be working fine.

thank you.

Pablo.
 
Jon (LabKey DevOps) responded:  2016-02-05 18:59
Fantastic Pablo!

Let us know if you have any further questions.

Regards,

Jon