Olympus configuration questions/problems

glarimer · July 10, 2015, 7:01pm

I’m having issues launching Olympus. The application will run and allows me to login, but I keep getting errors when trying to configure the WebAPI options. I’m not sure if I’m setting the options correctly.

My setup:
Database: SQL Server
Database Name: OMOP_V5
CDM tables schema: dbo

Olympus configuration:
SQL Dialect: SQLSERVER
Host Server:
Username/Password: Local DB account with full rights
CDM Database Name / Schema: OMOP_V5
Results Database Name / Schema: OMOP_V5
Cohort Database Name / Schema: OMOP_V5

The error I’m getting is:

WebAPI was not able to start up successfully, please check your configuration. (org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘flyway’ defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Migration V1.0.0.3__cohort_definition_persistence.sql failed ------------------------------------------------------------ SQL State : S0000 Error Code : 1770 Message : Foreign key ‘FK_cohort_definition_details_cohort_definition’ references invalid column ‘id’ in referenced table ‘dbo.cohort_definition’. Location : db/migration/sqlserver/V1.0.0.3__cohort_definition_persistence.sql (C:\Users\vhaisllarimg\AppData\Local\Temp\2\jetty-0.0.0.0-20000-WebAPI.war-_WebAPI-any-\webapp\WEB-INF\classes\db\migration\sqlserver\V1.0.0.3__cohort_definition_persistence.sql) Line : 1 Statement : IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘cohort_definition’)) BEGIN CREATE TABLE [dbo].[cohort_definition]( [id] [int] NOT NULL identity(1,1), [name] varchar NOT NULL, [description] varchar NULL, [expression_type] varchar NULL, [created_by] varchar NULL, [created_date] 2018-10-28 16:34:04 NULL, [modified_by] varchar NULL, [modified_date] 2018-10-28 16:34:04 NULL, CONSTRAINT [PK_cohort_definition] PRIMARY KEY (id) ) ON [PRIMARY] END ; IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘cohort_definition_details’)) BEGIN CREATE TABLE [dbo].[cohort_definition_details]( [id] [int], [expression] varchar NOT NULL, CONSTRAINT PK_cohort_definition_details PRIMARY KEY (id), CONSTRAINT FK_cohort_definition_details_cohort_definition FOREIGN KEY (id) REFERENCES dbo.cohort_definition(id) ) ON [PRIMARY]; END ; )

The inner exception appears to expect the primary key of the cohort_definition table to be [id], but according to the CDM, the primary key is titled [cohort_definition_id].

I’m not sure if I’m configuring Olympus wrong or if there’s a mistake in my CDM architecture. Any thoughts?

Thanks
Grant

jon_duke · July 10, 2015, 8:52pm

Grant,

I will review the logs with @alfranke to see what may be going on. I know we have seen a similar flyway issue before related to the cohort_definition table. It looks like you are connecting correctly.

Will get back to you shortly.

Thanks,

Jon

jon_duke · July 10, 2015, 9:01pm

Grant,

My cohort_definition table in the results_schema actually does have the ‘id’ field.

The WebAPI relies on a updated version of this table. Since you are using the same results schema and cdm schema, probably best to delete your cohort_definition table form OMOP_V5 and let flyway rebuild it with the updated fields.

Let me know how it goes.

Jon

Chris_Knoll · July 11, 2015, 12:01am

This is a schema name issue. The problem is the flyway DDL scripts assume the tables shoudl be created in DBO, but that’s not always the case.

The flyway scripts need to be updated to use placeholders when creating the tables.

I was thinking you could ‘possibly’ get around this by creating a separate database instance (call it OHDSI) and configure your application to poin tto this new OHDSI database instance. This way, you have 2 different databases (the OHDSI is blank, and the flyway will create the tables in this database), and the OMOP_V5 will contain your current CDM Schema (and no tables will be created there).

You will configure olympus to have the following settings:
CDM Schema: OMOP_V5.dbo
Results Schema: OHDSI.dbo

You need to configure the database connection string to point to the OHDSI database (if you could provide the configuration settings that you are using to set up your environment, I can give you more specific instructions). The the bug is that there are 2 tables in the CDM schema that are also in the OHDSI schema: cohort_definition and cohort. And if you try to point the CDM and OHDSI Results to the same database, it will try to create tables that already exist and fail.

-Chris

jon_duke · July 13, 2015, 3:16pm

@Chris_Knoll,

Would it possible for @glarimer to just drop his current COHORT and COHORT_DEFINITION tables from his OMOP_V5 schema and manually run the new create scripts for those two tables? Seems like it would be a bit easier and should work, no?

Chris_Knoll · July 13, 2015, 7:50pm

Sure, if those tables are not being used otherwise.

Edit:
He should also drop the table: dbo.schema_version. This is the flyway table that keeps track of which tables have been deployed. The reason why he should drop this is that the flyway DDL for MSSQL tests to see if the table already exists, and if so, considers it done. Example is here:

github.com

OHDSI/WebAPI/blob/master/src/main/resources/db/migration/sqlserver/V1.0.0.3__cohort_definition_persistence.sql

CREATE TABLE [${ohdsiSchema}].[cohort_definition](
	[id] [int] NOT NULL identity(1,1),
	[name] [varchar](255) NOT NULL,
	[description] [varchar](1000) NULL,
	[expression_type] [varchar](50) NULL,
	[created_by] [varchar](255) NULL,
	[created_date] [datetime] NULL,
	[modified_by] [varchar](255) NULL,
	[modified_date] [datetime] NULL,
	CONSTRAINT [PK_cohort_definition] PRIMARY KEY (id) 
) ON [PRIMARY]

CREATE TABLE [${ohdsiSchema}].[cohort_definition_details](
	[id] [int],
	[expression] [varchar](max) NOT NULL,
	CONSTRAINT PK_cohort_definition_details PRIMARY KEY (id),
	CONSTRAINT FK_cohort_definition_details_cohort_definition 
		FOREIGN KEY (id)
		REFERENCES ${ohdsiSchema}.cohort_definition(id)
) ON [PRIMARY];

So, if you don’t delete the dbo.schema_version, then it will try to continue where it failed last time, which is after the cohort_definition creation (which was skipped because it detected the table already existed), but the table that was there is the wrong table definition (it came from the CDM Schema). So, here’s what he needs to do:

DROP TABLE dbo.schema_version;
DROP TABLE dbo.cohort;
DROP TABLE dbo.chort_definition;

Then, next time he runs Olympus, it will run all the flyway migration scripts from step 0, and that will create the correct tables, and set up the proper database relationships.

-Chris

glarimer · August 4, 2015, 7:35pm

Thank you all for the information. Sorry for the delay in this response, the time I have allocated to OMOP is unfortunately limited. I will try dropping those tables and see where that leads. Thanks

Grant