search

OHDSI / WebAPI

OHDSI WebAPI contains all OHDSI services that can be called from OHDSI applications
Apache License 2.0
126 stars 156 forks source link

Sharing PostgresSQL db with more than one WebAPI instance #2285

Open davidhcar opened 1 year ago

davidhcar commented 1 year ago

Expected behavior

2 Web API instances of same Docker image share same PostgresSQL database instance.

Actual behavior

java.lang.ClassNotFoundException: Unable to load class: jdbc:postgresql://{host}/{databasename} from ClassLoader:java.net.URLClassLoader@3d299393;ClassLoader:ParallelWebappClassLoader context: WebAPI

Steps to reproduce behavior

Share same postgresSql instance from two different WebAPI, meaning host 2 different webapi instance on tomcat and connect to one PostgresSQL database. Share one database for more than one Appservice.

Note: Upon setting up 2nd WebAPI instance with existing database causing this error. the First WebApi instance working well against this DB.

konstjar commented 1 year ago

Curious to see why do you need 2 instances to use the same DB. Every WebAPI instance requires its own PostgreSQL database. It's per design.

davidhcar commented 1 year ago

Basically to test features of some of the webapi while the other instances are used by end users. I do not want to cause disruptions when I enable new features. So Is Atlas API designed to restrict the DB if another instance is connected to it ? Thats where I am trying to get. This could mean also when newer version of API released I want to review the new features, before releasing for general availability.

alondhe commented 1 year ago

I think generally we've recommended having a test webapi schema that is a copy of your production webapi schema. With new features in WebAPI (meaning possibly changing the table structures in the webapi schema via flyway scripts), you wouldn't want to disturb your production users.

chrisknoll commented 6 months ago

I've connected to the same postgresDB instance while different WebAPI processes are running. So, the only issue with that is that when a WebAPI process starts, it assumes any job in 'running' state should be marked canceled (because when the process gets restart the running jobs have been killed. This prevents a load-balanced context (at least, when you add the new process to the load balancer it will kill running jobs). We can think more deeply about 'node-specific job contexts' at some point so we can have it work in a muliti-host case, but I don't think that is what is happening here.

java.lang.ClassNotFoundException: Unable to load class: jdbc:postgresql://{host}/{databasename} from ClassLoader:java.net.URLClassLoader@3d299393;ClassLoader:ParallelWebappClassLoader
context: WebAPI

I'm not familiar with the ParallelWebappClassLoader, and what might be happening in docker to cause this issue. A simple proof of concept where multiple connections to same database (or using the same JDBC driver, since it seems to be trying to resolve a Class from the ClassLoader that's being shared across VMs (assuming this is separate VMs?). Do you think you can make a small java program that just connects to a database can be deployed as a WAR twice?