OHDSI Home | Forums | Wiki | Github

Postgres connection for Olympus

Developers
#1

Hi all,

I’m currently trying to get Olympus to work. But I got stuck at the database connection and keep getting the follwing error when launching: “WebAPI was not able to start up successfully, please check your connection”. We are using postgres. Can someone give me an example of the connection details for postgres?

BTW: we are not allowed to have patient data on a PC with an internet connection. So this machine has NO internet connection. I noticed with every interaction I make that the statusbar is showing “Resolving host…” for about 5 seconds. Is an internet connection required to get this to work?

BTW2: Java v1.8 is installed.

Thanks,
Marcel de Wilde

1 Like
#2

Hi Marcel,

This is a tricky one. Olympus does rely on the OHDSI online repository to download drivers and other items at runtime. I will check with @alfranke to see if a purely standalone file can be built for Postgres. Once OLYMPUS is running, it does not depend on an internet connection, so it may be possible.

Will keep you posted.

Jon

#3

Hi Jon,

Thanks for this quick response. Do we need an internet connection each time we restart the server/services? Or is is it required only the first time after the configuration?

Maybe stupid question: after installing Olympus on a Windows server, where can I find the software? I was not able to find it. Maybe I can then try to patch it based on an installation on a PC with internet connection but without patient data…

Thanks,

Marcel

#4

Olympus should only require an internet connection when building from source (maven repositories - artifact retrieval). I’m assuming @mdewilde is trying to run the executable WAR? Assuming @mdewilde is using the executable WAR AND the postgres db in question is available via localhost (i.e. running locally) AND the db account user has the necessary privileges, then Olympus should be able to start without an internet connection and then apps should be usable. @mdewilde, can you send @jon_duke and/or myself the console output from the Olympus executable? It should provide the info we need to diagnose this further.

#5

How exactly did you “install” Olympus? After downloading the Olympus-x.x.x-exec.war, one starts it with “java -jar”. Say you do this a Windows command prompt, then it will then start logging information as it starts. A simple option for creating a log file to consult, would be to “pipe” this logging into a text file (something like java -XX:MaxPermSize=256m -jar Olympus-x.x.x-exec.jar > Olympus.log
We’ll need to consult this log to troubleshoot.

Olympus creates a temporary/working directory somewhere like C:\Users\afranken\AppData\Local\Temp\olympus . Again, the Olympus log output will indicate where this is on your computer. You really shouldn’t need to do much with this directory.

See my response to Jon about Olympus and internet connection expectations.

#6

Correct I’m running the *.war file.

I stopped Olympus and restarted it with the redirection of the output to a logfile. I don’t know what happened but now the WebAPI is running with the same settings as before!!! Maybe restarting Olympus was the fix. But ok, so far so good now….

However some points:

· It keeps saying “Resolving host…” for every interaction I have and have to wait several seconds before a new window will show. Running the “old” non-integrated Achilles is not having this issue and is still running fast. Olympus is now running very slow because of this resolving issue. Looks like it is looking for something on the internet with every interaction and continues after a timeout.

· The Achilles in the integrated Olympus environment seems to be an old one. We had a problem with the generated json Achilles files that were not compatible with the viewer part of Achilles. An update of Achilles was made but it looks like it was included in Olympus. Is there a update of Olympus planned where it also contains the latest Achilles version? If so when is this expected?

· Starting Heracles and looking for cohorts. But it is not showing the cohort we have in the cohort_definition table. All of our tables (patientdata, Achilles, cohorts) are in the public schema. In the configuration they are all three defined as : “IPCI_dopey_CDM/public” where “IPCI_Dopey_CDM” is the database name and “public” is the name of the schema. The user in the configuration is the administrator, so rights should not be the problem.

See attached logfile for details (since txt attachement were not possible (??) before opening please rename to *.txt). I’m not able to interpret the whole logfile. But I noticed a strange columnname that could not be found.

“Caused by: org.postgresql.util.PSQLException: ERROR: column cohortdefi0_.id does not exist”

And it’s right I don’t have a column named “cohortdefi0_.id” I have a column named “cohort_definition_id”. :wink:

Thanks,
Marcel2015-05-28 - Olympus.xls (158.5 KB)

#7

There are known issues with launching WebAPI after a failure. After a failure (e.g. incorrect properties), update/save WebAPI configuration changes, then restart Olympus and launch WebAPI. If another failure, repeat.

Regarding the Olympus interaction and the internet, I first thought that it could be that we were using references to resources (javascript/css) from a CDN. However, I have not found evidence of this, besides a few font urls, which are found in both Achilles as well as Olympus. What url are you using to access Olympus (http://localhost:20000)? It really must be that (currently) or things will not work. There is an effort to address “Olympus in a hosted environment” and will require packaging/building Olympus from source. If you are using localhost:20000, then what is the url you are using for “old non-integrated Achilles” (e.g. http://localhost/Achilles)?

Regarding cohort_definition.id. WebAPI manages the creation of the cohort_definition table it is attempting to connect to and therefore requires a column with name “id”. The log you attached indicates that it was able to “migrate”. 1.0.0.3 is the cohort_definition_persistence.sql.

2015-05-28 08:07:52.059 INFO qtp1309736113-17 org.flywaydb.core.internal.command.DbMigrate - - Migrating schema "IPCI_Dopey_CDM/public" to version 1.0.0.3

CREATE TABLE cohort_definition(
	id int NOT NULL,
	name varchar(255) NOT NULL,
	description varchar(1000) NULL,
	expression_type varchar(50) NULL,
	created_by varchar(255) NULL,
	created_date Timestamp(3) NULL,
	modified_by varchar(255) NULL,
	modified_date Timestamp(3) NULL,
	CONSTRAINT PK_cohort_definition PRIMARY KEY (id) 
);

Looks like possibly the properties are still not defined correctly, as expected by WebAPI.

2015-05-28 07:56:43.737 DEBUG main org.ohdsi.olympus.model.WebApiService -  - Property [datasource.cdm.schema,IPCI_Dopey_CDM/public]
2015-05-28 07:56:43.737 DEBUG main org.ohdsi.olympus.model.WebApiService -  - Property [datasource.ohdsi.schema,IPCI_Dopey_CDM/public]

Looks like WebAPI created the following schema… I bet you may find a new IPCI_Dopey_CDM/public schema within your IPCI_Dopey_CDM database.
2015-05-28 08:07:51.828 INFO qtp1309736113-17 org.flywaydb.core.internal.command.DbSchemas - - Creating schema "IPCI_Dopey_CDM/public" ...

I imagine that the schema should not include the database name and / (in your case, just ‘public’). I would caution again that the WebAPI manages (creates/drops db objects) the “Results Database Name / Schema” schema. So, if you have your cdm and results/ohdsi schema as one-in-the-same (i.e. ‘public’) and there are conflicting tables (e.g. you have some cohort_definition table already existing), WebAPI will fail to start.

Regarding updating AchillesWeb within Olympus, I can look at pulling in the latest and if we can get another snapshot out.

#8

Hi Marcel,

Glad it started working in terms of getting connected. In terms of your other questions:

Does this come up between reports on Achilles? Or between applications in Olympus?

Thanks to @alfranke, I have just posted an updated Olympus WAR on ohdsi.org that contains updates to WebAPI, Circe, Heracles, and Hermes. I thought AchillesWeb was the latest version, but @alfranke can confirm if there is a newer version on GitHub than appears in Olympus.

This reference to cohortdefi0_.id is an alias i believe, but let me see in @cahilton can weigh in on this error:

select cohortdefi0_.id as id1_2_, cohortdefi0_.created_by as created_2_2_, cohortdefi0_.created_date as created_3_2_, cohortdefi0_.description as descript4_2_, cohortdefi0_.expression_type as expressi5_2_, cohortdefi0_.modified_by as modified6_2_, cohortdefi0_.modified_date as modified7_2_, cohortdefi0_.name as name8_2_ from cohort_definition cohortdefi0_

ERROR: column cohortdefi0_.id does not exist

#9

I did confirm that Olympus contains the latest (April 1st commit) from AchilleWeb/master. https://github.com/OHDSI/AchillesWeb/commits/master
Added @Chris_Knoll , in case he knows about the problem you are experiencing and why you might not be seeing it fixed.
I have experienced some issues with iframe caching in browsers that impacts Olympus, preventing a reload of resources (js/css,etc.), but I don’t believe this should be an issue here though…

#10

Disregard my post, cross posted with @alfranke who addressed the issues.

#11

Regarding the migration script:
The original script doesn’t have the schema specified and therefore uses the default schema (I think public is what postgres uses). We should include placeholders to read from the ohdsi_schema so that it will create the tables in the correct schema.

I think to prevent future issues like this, we should update the prior migration scripts, but this will force everyone who has already deployed to either wipe their db (so that the tables get recreated and the version checksum gets set properly) or run the flyway migration repair script. Or we disable schema validation entirely and let people manually repair prior deploys.

-Chris

#12

Yes, the URL I’m using is “http://localhost:20000”.

You’re correct, there is an extra schema named “IPCI_Dopey_CDM/public”.

This is what I did now:

  • Downloaded update
  • Created a results schema in the database
  • Updated the schema settings to “public” and only the results scheme to “results” and saved.
  • Stopped Olympus (attempt to prevent the known launch issues).
  • Started Olympus again
  • Chrome to “http://localhost:20000”
  • After login, there was this messages that the WebAPI was not runnning.
  • Went to configuration again, to check the settings and pressed the “Launch”.
  • Lauchin was ok!!!
  • Noticed the results schema was filled with tables including the cohort tables.
  • Started Heracles and tried to find our cohort we created which was not there.

Its not clear to me why and how I should interpret two different schemes and sets of two cohort tables. But decided to remove them from the “results” scheme and copy them back from our original tables in the “public” scheme. So at that point I have the cohort table set (the 3 tables) in both schemes.

Started everything from scratch noticing the WebAPI was not launched again and restarted it from the configuration page. Tried Hercules but unfortunately don’t see our cohort showing up here.

Have any idea why the cohort we defined does not show up??

#13

About the Achilles version in Olympus. We had some issues with the “standalone” Achilles last month and Chris did some great hotfiles for us. If I’m correct Chris did this after April 1. Is this correct @Chris_Knoll?

@jon_duke “Resolving host…” is an issue in Olympus in general when browsing through the pages.For example clicking the “Home” button takes about 10 seconds to show.Same with going to configuration and saving configuration. Once in Achilles everything is Achilles is fast.

#14

Heracles pulls from the cohort_definition table, not (initially) the cohort
table. Is that table populated? eg Does Circe definition list work?

#15

Hi Jon,

thanks for response. Yes the cohort definitions table has one record for our default research population. This is the one we want to have profiles. If I open Circle I got an windows with the main header and the rest is completely empty. ;-( Don’t know if the logfile will be of any help, but here it is. 2015-05-29 - Olympus (rename to txt).xls (55.8 KB)

There is a project meeting in Gent (Belgium) in a couple of week that Patrick also will join where we want to show the Heracles results of our IPCI database via CDM (if feasible). This is the background reason why we are busy with this great stuff.

Hope to get it working before that :wink:

Marcel

#16

Marcel,
I think we should try to fix the DDL that installs the WebAPI tables for you so that you don’t have to do manual table movement around the different schemas. There’s special behavior to support MSSQL server’s {database}.{schema}.{table} notation that I don’t think you need to deal with in Postgres ({schema}.{table} should suffice)… Your schemas should be something like ‘public’ and ‘cdm’ (if you have your cdm tables in a different schema then where you want the ohdsi results).

Let me go thorough an example (this assumes that you have 1 postgres database, and you’d like to put your cdm schema separate from your ohdsi results schema):
The way our database migration technology works is that you declare the schemas that it manages ('let’s say, for example your CDM tables are in schema ‘cdm’ and your ohdsi results schema is in schema ‘ohdsi’). I’m not totally familiar with Olympus setup, but it should have asked you for a cdm schema and an ohdsi schema. Those would be set to ‘cdm’ and ‘ohdsi’ based on the example i described above. When the database tool starts up, the first thing it does is makes sure that the results schema exists because our database CREATE TABLES will indicate which schema to create the tables in (or they SHOULD, I have found that they will use the ‘default’ schema, which I think we need to correct). After the schema is created, the tables are created. Note, we don’t manage anything with the CDM schema, that’s assumed to exist. The tool only manages the schema indicated by the ‘ohdsi schema’ parameter.

In your case, it seems like you put the ohdsi result schema as ‘IPCI_Dopey_CDM/public’. If you want the results schema to be put in the public schema, just set the ohdsi result schema to ‘public’.

I hope this clear, admittedly I do not have all the details about your postgres environment (such as how many database you are using, what schema your CDM tables are in, etc). let me know if you have any questions.

-Chris

#17

Hi, Marcel, Thanks for the logs. Very enlightening. It looks like you have 1 database, and 2 different schemas: public and results:

2015-05-29 17:01:11.244 DEBUG main org.ohdsi.olympus.model.WebApiService -  - Property [datasource.cdm.schema,public]
2015-05-29 17:01:11.245 DEBUG main org.ohdsi.olympus.model.WebApiService -  - Property [datasource.ohdsi.schema,results]

The problem is, the migration scripts create the tables in the default schema which is probably ‘public’ for your admin account. Are you seeing the tables created in the public schema?

-Chris

#18

Hi Chris,

Thanks for helping us out here. Feeling stupid not being able to get this to work. Just hoping its not me…

Yes, I currently have 1 database with 2 schemas. When I started everything was in the public scherma. Including our 3 filled cohort tables. Starting Heracles in this situation gave conflicts because of some migration handling. The I created a extra schema named “results” and restarted everything.After this some empty tables were created in the "results"schema. Because we already have a filled cohort table in the public schema I decided delete the three cohort tables and copy them from the “public” schema including the records we already had inserted. I expected our cohort definition to show up in Heracles, but unfortunately it didn’t. ;-(

Need any more details?

Remember: no internet connection her which is probably different compared to most other installations.

Thanks,
Marcel

#19

I don’t think the lack of internet connection is an issue, it’s just creating the tables locally.

I think you’ll definitely run into problems with the CDM tables in the same place as the OHDSI tables because we have a CDM ‘cohort’ table as well as an OHDSI ‘cohort’ table. That should have caused a conflict. So splitting them up makes sense.

You have to be very careful about just changing the database around and restarting the app. The database management tool we use to create tables maintains it’s own version table (you should see a table called schema_version) that determines if a migration script has been applied.

Instead of deleting the tables and re-creating them, i’d have copied the values from he public schema into the results schema…HOWEVER: the cohort_definition_id you had from your public.cohort table would conflict with the resutls.cohort table because the results.cohort table’s cohort_definition_id comes from the results.cohort_definition’s cohort_definition_id table. Just somethign to be aware of. All the OHDSI webapi calculations (circe/heracles) will work off of the data found in the results.cohort_definition table and the results.cohort table. So be aware of this!

I think we need to start fresh with your install, but I also think the migration scripts that come from the olypmus (and the general webAPI github repo) do not specify the schema when the tables are created, so you’ll get these conflicts until we fix the migration script.

In the short term, even tho i said you shouldn’t mess around with moving tables around manually, there may be something you can do: ignoring my advice to not move tables around, I suggest you continue with what you tried to do above:

The problem is the migration scripts are creating the tables in the default schema which have conflicts (specifically the ‘cohort’ table). is there a way to specify the administrator account that the ‘flyway user’ uses to create the tables, to have the default schema set to ‘results’? Then, you can re-install olympus using this ‘results schema flyaway admin user’ and all the tables should be created int he results schema. Then, if that works, you should have all the tables created in the correct place, you just need to specify your cdm schema is ‘public’ and your ohdsi schema is ‘results’.

Can you give that a try?

-Chris

#20

Hi Chris,

“Flyway user”??? I had a look at postgres settings and on the internet, but I have no idea what you are talking about. I’m a simple Delphi developer and have no experience in maintaining databases and servers at all (yet…). It’s all pretty new for me. Sorry. :wink: Also this feels like patching over patching and at the end we might have something that works but have no clue what we did. I prefer to have something we can easily reproduce as well. Other persons at our department should be able to install all these things as well.

So I completely agree that the best thing is to start fresh with our install. But then the way it supposed to be done. I just started the whole ETL (we had some other fixes as well discovered by @ericaVoss Erica’s trip to Rotterdam). I hope I will have a fresh database again in a couple of days. After this I will backup the database before installing and playing with Olympus/Heracles. That makes it is much easier to rollback everything from there. Do you think the migration script could be fixed by then or do I have to “patch” the database to get it working?

Other question: In our previous OHDSI demo for the EMIF project we showed Achilles (together with @Patrick_Ryan). We just copied the Achilles results to a laptop with Apache and this just worked perfect. After running Heracles, is it then possible to copy the Heracles results (without any patient data) to another machine for a Heracles demo?

BTW we are on CDM v5, I don’t know if this matters.

Thanks,
Marcel

t