WEBAPI startup failed from docker deployment

[Sergio_Maffioletti]

Hi,

I’m deployng Broadsea following the instructions at https://github.com/OHDSI/Broadsea
I’m deploying the docker containers using the docker-compose file.
postgresdb is an external service and has been already populated with the schema and table and access rights.

service are running:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
235b2c4cd30a ohdsi/broadsea-webtools “/usr/bin/supervisord” 2 weeks ago Up 2 weeks 0.0.0.0:8080->8080/tcp ohdsidocker_broadsea-webtools_1
c156a5e30c4a ohdsi/broadsea-methodslibrary “/usr/bin/supervisor…” 2 weeks ago Up 2 weeks 0.0.0.0:6311->6311/tcp, 0.0.0.0:8787->8787/tcp ohdsidocker_broadsea-methods-library_1

but apparently WEBAPI fails to start somehow:
INFO: Starting service Catalina
Jun 24, 2019 11:38:27 AM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.91
Jun 24, 2019 11:38:27 AM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive /usr/local/tomcat/webapps/WebAPI.war
Jun 24, 2019 11:38:29 AM org.apache.catalina.loader.WebappClassLoaderBase validateJarFile
INFO: validateJarFile(/usr/local/tomcat/webapps/WebAPI/WEB-INF/lib/tomcat-embed-el-8.5.11.jar) - jar not loaded. See Servlet Spec 3.0, section 10.7.2. Offending class: javax/el/Expression.class
Jun 24, 2019 11:38:36 AM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jun 24, 2019 11:49:47 AM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deployment of web application archive /usr/local/tomcat/webapps/WebAPI.war has finished in 679,851 ms

the webinterface works but it reports an error:
OHDSI (http://127.0.0.1:8080/WebAPI/)

$ cat docker-compose.yml
version: ‘2’

services:

broadsea-methods-library:
image: ohdsi/broadsea-methodslibrary
ports:
- “8787:8787”
- “6311:6311”

broadsea-webtools:
image: ohdsi/broadsea-webtools
extra_hosts:
- “database:10.65.4.5”
ports:
- “8080:8080”
volumes:
- .:/tmp/drivers/:ro
- ./config-local.js:/usr/local/tomcat/webapps/atlas/js/config-local.js:ro
environment:
- WEBAPI_URL=http://127.0.0.1:8080
- env=webapi-postgresql
- datasource_driverClassName=org.postgresql.Driver
- datasource_url=jdbc:postgresql://10.65.4.5:5432/ohdsi
- datasource.cdm.schema=cdm
- datasource.ohdsi.schema=ohdsi
- datasource_username=ohdsi_admin_user
- datasource_password=admin1
- spring.jpa.properties.hibernate.default_schema=ohdsi
- spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
- spring.batch.repository.tableprefix=ohdsi.BATCH_
- flyway_datasource_driverClassName=org.postgresql.Driver
- flyway_datasource_url=jdbc:postgresql://10.65.4.5:5432/ohdsi
- flyway_schemas=ohdsi
- flyway.placeholders.ohdsiSchema=ohdsi
- flyway_datasource_username=ohdsi_admin_user
- flyway_datasource_password=admin1
- flyway.locations=classpath:db/migration/postgresql

$ cat config-local.js
define([], function () {
var configLocal = {};

// clearing local storage otherwise source cache will obscure the override settings
localStorage.clear();

// WebAPI
configLocal.api = {
	name: 'OHDSI',
	url: 'http://127.0.0.1:8080/WebAPI/'
};

configLocal.cohortComparisonResultsEnabled = false;
configLocal.userAuthenticationEnabled = false;
configLocal.plpResultsEnabled = false;

return configLocal;

});

connection to the postgres DB works fine.
I can see logs from both ends: deploy-script-stdout—supervisor-UciPql.log inside the container and the postgres log as well

any idea what are we missing ?

thanks for your help

Regards,
Sergio

[lee_evans]

@Sergio_Maffioletti what error messages are you seeing in the tomcat log file?

You can view the tomcat log (stdout) file inside the Broadsea docker container using the info here:
https://github.com/OHDSI/Broadsea#viewing-the-broadsea-web-tools-log-files

Also, did you setup the CDM database connection source and source_daimon info in your postgres database ‘ohdsi’ schema source and source_daimon tables?

There’s an example postgresql SQL file you can use (update it with your postgresql database connection/schemas info) here:

OHDSI/Broadsea/blob/master/postgresql/source_source_daimon.sql

Use a postgres SQL client to run the SQL file in your postgres database and then restart the Broadsea Docker container.

[kyriakosschwarz]

@lee_evans

Thank you for your response.

Here is the tomcat log.

Also, did you setup the CDM database connection source and source_daimon info in your postgres database ‘ohdsi’ schema source and source_daimon tables?

As mentioned, this step was done:

postgresdb is an external service and has been already populated with the schema and table and access rights

How can we proceed?

[lee_evans]

@kyriakosschwarz In the log extract you uploaded, I don’t see the log error messages that indicate the cause of the problem.

Can you provide the log messages immediately after the ‘S P R I N G’ banner?

You could try using this command inside the container to get that info:
head -2000 *stdout*

[kyriakosschwarz]

@lee_evans

Thank you for answering. Here is the output of:
head -2000 *stdout*

I appreciate your help!

[admin (Lee Evans)]

@kyriakosschwarz It looks like the tomcat log you posted is just flooded with messages like the following (which doesn’t provide any additional info):

2019-07-16 17:21:55.956 DEBUG task-scheduler-5 org.springframework.jdbc.datasource.DriverManagerDataSource - - Creating new JDBC DriverManager Connection to [jdbc:postgresql://10.65.4.5:5432/ohdsi]
2019-07-16 17:21:55.976 DEBUG task-scheduler-5 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager
2019-07-16 17:22:05.977 DEBUG task-scheduler-9 org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - - Creating new EntityManager for shared EntityManager invocation
Hibernate: select analysisex0_.id as id1_0_, analysisex0_.analysis_id as analysis2_0_, analysisex0_.analysis_type as analysis3_0_, analysisex0_.duration as duration4_0_, analysisex0_.executed as executed5_0_, analysisex0_.executionStatus as executio6_0_, analysisex0_.source_id as source_i7_0_, analysisex0_.update_password as update_p8_0_, analysisex0_.sec_user_id as sec_user9_0_ from ohdsi.analysis_execution analysisex0_ where analysisex0_.executed<? and (analysisex0_.executionStatus in (? , ? , ?))

Can you stop the Broadsea Docker container (docker-compose down), restart it (docker-compose up -d) and then post the log from inside the container again?

I’m interested to see the ATLAS/WebAPI application messages from a clean tomcat log, from the point when it starts up (beginning with the ‘S P R I N G’ banner message) which will hopefully provide some useful info about the issue.

[kyriakosschwarz]

Hi, I hope this is what you are looking for.

Thanks again.

[lee_evans]

Hi @kyriakosschwarz that is the log output I was looking for!

I see this database connection message:

Creating new JDBC DriverManager Connection to [jdbc:postgresql://localhost:5432/ohdsi?

and this error message:

2019-08-07 11:26:48.653 ERROR taskExecutor-1 org.ohdsi.webapi.cdmresults.CDMResultsCacheTasklet - - Failed to warm cache for OHDSI-CDMV5. Exception: Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Take a look at the jdbc string value in the source database table source_connection column. It should reference the hostname or ip address (10.65.4.5?) of your postgres database server not ‘localhost’.

[kyriakosschwarz]

@lee_evans

Thanks a lot! After fixing this problem I now get the error that:
relation "source" already exists

But this was created when I ran the source_source_daimon.sql

Here is the full log.

[admin (Lee Evans)]

@kyriakosschwarz OK - try the following steps:

1. docker-compose down
2. manually drop the source and source_daimon tables using a postgresql sql client
3. docker-compose up -d
Wait a few minutes for the container to start up - it will migrate the ohdsi schema tables (including automatically creating empty source and source_daimon tables).
4. Manually run your edited source_source_daimon.sql in a postgresql sql client to populate those 2 tables
5. docker-compose down
6. docker-compose up -d

[kyriakosschwarz]

@lee_evans thanks again.

Now the Atlas animation is loading endlessly and I don’t see any obvious errors.

Log

[kyriakosschwarz]

@admin Now I see this page:

[lee_evans]

@kyriakosschwarz OK here are a few additional troubleshooting ideas to review:

Open the following URLs in the browser:

This url should show the WebAPI version as a JSON string (and demonstrates that WebAPI is up and running OK)
http://127.0.0.1:8080/WebAPI/info

This should show the data in the source and source daimon tables (without any passwords):
http://127.0.0.1:8080/WebAPI/source/sources

Ensure you are using a recent version of Chrome browser (only Chrome is officially supported) and open the “Developer Tools” - refresh the web page and look at the network tab and the developer console to see what error messages are produced by ATLAS.

[kyriakosschwarz]

Note: I am not using 127.0.0.1, but the IP of my instance where OHDSI is running:

http://X.X.X.X:8080/WebAPI/info
{"version":"2.6.0"}

http://X.X.X.X:8080/WebAPI/source/sources
[{"sourceId":1,"sourceName":"OHDSI CDM V5 Database","sourceDialect":"postgresql","sourceKey":"OHDSI-CDMV5","daimons":[{"sourceDaimonId":1,"daimonType":"CDM","tableQualifier":"public","priority":2},{"sourceDaimonId":2,"daimonType":"Vocabulary","tableQualifier":"public","priority":2},{"sourceDaimonId":3,"daimonType":"Results","tableQualifier":"ohdsi","priority":2},{"sourceDaimonId":4,"daimonType":"CEM","tableQualifier":"ohdsi","priority":2}]}]

refresh the web page and look at the network tab and the developer console to see what error messages are produced by ATLAS

[kyriakosschwarz]

I was trying to switch the WebAPI URLs from localhost to X.X.X.X

In config-local.js:

configLocal.api = {
name: ‘OHDSI’,
url: ‘http://X.X.X.X:8080/WebAPI/’
};

Also in docker-compose.yml:

broadsea-webtools:
environment:
- WEBAPI_URL=http://X.X.X.X:8080

Then I restarted the containers:

docker-compose down
docker-compose up -d

I checked within the broadsea-webtools container that these changes were made. However, in Atlas it still sends requests to 127.0.0.1:8080/WebAPI , which of course does not work since the service is not running on the client machine, but on a remote server. Though, how can I change the request URLs in Atlas??

[lee_evans]

@kyriakosschwarz you have followed the correct approach to switch the WebAPI URLs.

Perhaps your web browser has cached the older version of the config-local.js file?

Clear your web browser cache and try accessing Atlas again.

One way to do that is in Chrome web developer tools, on the Network tab you can click on ‘disable cache’ and then reload the page.

[kyriakosschwarz]

Thanks a lot! It seems to load now but I still get some errors on the client:

And on the server side:

2019-08-22 11:47:51.344 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodesFactory - - SQL error codes for ‘PostgreSQL’ found
2019-08-22 11:47:51.345 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodesFactory - - Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@2d47694f]: database product name is ‘PostgreSQL’
2019-08-22 11:47:51.345 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - - Translating SQLException with SQL state ‘42P01’, error code ‘0’, message [ERROR: relation “public.domain” does not exist
Position: 56]; SQL was [select DOMAIN_ID, DOMAIN_NAME, DOMAIN_CONCEPT_ID
from public.DOMAIN
order by DOMAIN_NAME asc
] for task [PreparedStatementCallback]

2019-08-22 11:47:52.762 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodesFactory - - SQL error codes for ‘PostgreSQL’ found
2019-08-22 11:47:52.762 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodesFactory - - Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@5f8d4823]: database product name is ‘PostgreSQL’
2019-08-22 11:47:52.762 DEBUG http-apr-8080-exec-2 org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - - Translating SQLException with SQL state ‘42P01’, error code ‘0’, message [ERROR: relation “public.vocabulary” does not exist
Position: 33]; SQL was [select VOCABULARY_VERSION
from public.VOCABULARY
where VOCABULARY_ID = ‘None’
] for task [PreparedStatementCallback]

[lee_evans]

@kyriakosschwarz good, WebAPI is up and running and ATLAS can connect to it.

It appears that WebAPI is looking in the default postgres schema ‘public’ for the OHDSI OMOP vocabulary tables (vocabulary, domain etc) and not finding them.

Review/update the vocabulary row entry in your source_daimon table and follow the usual process to refresh the source_daimon table (restart WebAPI docker instance). Also verify that the database user you are using for the database connection has the required permissions for the database schemas/tables.

This is an ATLAS/WebAPI CDM configuration question vs a Broadsea question so take a look at the config info here for CDM setup - folks in the ATLAS/WebAPI development team can also help if you have questions about that:

CDM Configuration

[kyriakosschwarz]

@lee_evans

Thanks. I have trouble understanding the next steps because the OHDSI documentation is very inconsistent and confusing.

I have populated the source_daimon table according to: https://github.com/OHDSI/Broadsea/blob/master/postgresql/source_source_daimon.sql

Now, do I have to additionally add those entries? Do I have to change the source_id ?

INSERT INTO webapi.source (source_id, source_name, source_key, source_connection, source_dialect) VALUES (1, ‘My Cdm’, ‘MY_CDM’, ’ jdbc:postgresql://server:5432/cdm?user={user}&password={password}', ‘postgresql’);

INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (1,1,0, ‘cdm’, 0);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (2,1,1, ‘vocab’, 1);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (3,1,2, ‘results’, 1);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (4,1,5, ‘temp’, 0);

Additionally, I have set up the Postgresql according to: PostgreSQL Installation Guide · OHDSI/WebAPI Wiki · GitHub

Thank you for any suggestion.

[jcabrerazuniga (Jose Cabrera Zuniga)]

Hi… The deployment manual talks about the creation of an OHDSI database but it does not talk about the creation of an ohdsi schema. For the previous sql code, should I change ohdsi to webapi as the manual talks about the creation of a webapi schema?