OHDSI Home | Forums | Wiki | Github

Error deploying WebAPI 2.8.0 -

Hi all,

We have built the WebAPI.war file for the latest version 2.8.0 but have issues when we try deploying to Tomcat 9.0. Below are the logs from Tomcat, hope to seek some advice if there’s a workaround solution

22-Jan-2021 14:42:40.560 SEVERE [main] org.apache.catalina.startup.HostConfig.deployWAR Error deploying web application archive [C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI.war]
 java.lang.IllegalStateException: Error starting child
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:716)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:695)
	at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:978)
	at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1850)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
	at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:773)
	at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:427)
	at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1577)
	at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:309)
	at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
	at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:424)
	at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:367)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:929)
	at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:831)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1377)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1367)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:902)
	at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardService.startInternal(StandardService.java:423)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:928)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.startup.Catalina.start(Catalina.java:638)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350)
	at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492)
Caused by: org.apache.catalina.LifecycleException: Failed to start component [StandardEngine[Catalina].StandardHost[localhost].StandardContext[/WebAPI]]
	at org.apache.catalina.util.LifecycleBase.handleSubClassException(LifecycleBase.java:441)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:198)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:713)
	... 37 more
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'shiroFilter' defined in class path resource [org/ohdsi/webapi/ShiroConfiguration.class]: Unsatisfied dependency expressed through method 'shiroFilter' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/ohdsi/webapi/FlywayConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 156
Message    : Incorrect syntax near the keyword 'IF'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT IF EXISTS ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN IF EXISTS is_deleted;

	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:749)
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:467)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1177)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1072)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:511)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.context.support.PostProcessorRegistrationDelegate.registerBeanPostProcessors(PostProcessorRegistrationDelegate.java:237)
	at org.springframework.context.support.AbstractApplicationContext.registerBeanPostProcessors(AbstractApplicationContext.java:703)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:527)
	at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:124)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)
	at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:156)
	at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:136)
	at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:91)
	at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5125)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	... 38 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/ohdsi/webapi/FlywayConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 156
Message    : Incorrect syntax near the keyword 'IF'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT IF EXISTS ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN IF EXISTS is_deleted;

	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1630)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1139)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1067)
	at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:835)
	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741)
	... 60 more
Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 156
Message    : Incorrect syntax near the keyword 'IF'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT IF EXISTS ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN IF EXISTS is_deleted;

	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:345)
	at org.flywaydb.core.internal.command.DbMigrate.access$900(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:276)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273)
	at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146)
	at org.flywaydb.core.internal.dbsupport.DbSupport$1.call(DbSupport.java:223)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.dbsupport.DbSupport.lock(DbSupport.java:219)
	at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174)
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:971)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:971)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1759)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1696)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1626)
	... 73 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:859)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:759)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:739)
	at sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy93.execute(Unknown Source)
	at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:267)
	at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:142)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:76)
	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:343)
	... 96 more

Thanks!
-Hui Xing

Ok, the problem is with the MS Sql server migration (so not related to tomcat at all). The problematic statement is: the IF EXISTS. I think this is something related to a newer SQL server syntax.

What version of SQL server are you running?

I will also say, that statement looks like a problem, in that the way that the SQL statement is being dynamically constructed.

If you want to try something locally for yourself, you can alter the file: src/main/resources/db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql to the following:

-- at first removing default value
declare @command nvarchar(1000)
select @command = 'ALTER TABLE ${ohdsiSchema}.pathway_event_cohort DROP CONSTRAINT  ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('${ohdsiSchema}')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE ${ohdsiSchema}.pathway_event_cohort DROP COLUMN is_deleted;

Thanks, @Chris_Knoll !

Realised the src/main/resources/db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql is not in the .war that we deploy to tomcat but rather in the WebAPI 2.8.0 source code.

Is it possible to amend the file after running the maven build, or must we amend and then build it again?

We are using Microsoft SQL Server 2014

Thanks

Best,
Hui Xing

It must be in the war file, otherwise I’m not sure how it foud the file to error on in the first place?

Either way, if you modify the .sql file, yes, you’ll need to rebuild the source so that it gets packaged into the WAR file that you deploy.

Got it, so we can’t modify the .war file directly but need to edit and rebuild from source.

Thanks!

Ah, I apologize, I didn’t understand, but I think I understand now: do you need to build a war to get the .sql updated in the application? No, you can unzip the war, edit the file in the war, and re-zip the war and that should work. But I am more of a process-driven guy, so anything that changes should go through the build process but I think in this case, you are correct that you could modify the contents of the WAR and re-deploy.

Btw, the path to the migration scripts in the WAR is found under: \WEB-INF\classes\db\migration\sqlserver\

Ah ok thank you, have managed to find the script under \WEB-INF\classes\db\migration\sqlserver\ and edited as per above. But now I get a different error when I start the application on Tomcat:

28-Jan-2021 09:55:27.995 SEVERE [Catalina-utility-2] org.apache.catalina.startup.HostConfig.deployDirectory Error deploying web application directory [C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI]
 java.lang.IllegalStateException: Error starting child
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:716)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:695)
	at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1133)
	at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1868)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
	at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:1045)
	at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:429)
	at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1621)
	at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:305)
	at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
	at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1144)
	at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1346)
	at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1350)
	at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1328)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.apache.catalina.LifecycleException: Failed to start component [StandardEngine[Catalina].StandardHost[localhost].StandardContext[/WebAPI]]
	at org.apache.catalina.util.LifecycleBase.handleSubClassException(LifecycleBase.java:441)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:198)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:713)
	... 25 more
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'shiroFilter' defined in class path resource [org/ohdsi/webapi/ShiroConfiguration.class]: Unsatisfied dependency expressed through method 'shiroFilter' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/ohdsi/webapi/FlywayConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 4924
Message    : ALTER TABLE DROP COLUMN failed because column 'is_deleted' does not exist in table 'pathway_event_cohort'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT  ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN is_deleted;

	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:749)
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:467)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1177)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1072)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:511)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.context.support.PostProcessorRegistrationDelegate.registerBeanPostProcessors(PostProcessorRegistrationDelegate.java:237)
	at org.springframework.context.support.AbstractApplicationContext.registerBeanPostProcessors(AbstractApplicationContext.java:703)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:527)
	at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:124)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)
	at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:156)
	at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:136)
	at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:91)
	at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5125)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	... 26 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/ohdsi/webapi/FlywayConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 4924
Message    : ALTER TABLE DROP COLUMN failed because column 'is_deleted' does not exist in table 'pathway_event_cohort'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT  ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN is_deleted;

	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1630)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1139)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1067)
	at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:835)
	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741)
	... 48 more
Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql failed
-------------------------------------------------------------------------------------------------
SQL State  : S0001
Error Code : 4924
Message    : ALTER TABLE DROP COLUMN failed because column 'is_deleted' does not exist in table 'pathway_event_cohort'.
Location   : db/migration/sqlserver/V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\WebAPI\WEB-INF\classes\db\migration\sqlserver\V2.8.0.20200116134252__remove_obsolete_field_pathway_event_cohort_deleted_id.sql)
Line       : 2
Statement  : declare @command nvarchar(1000)
select @command = 'ALTER TABLE dbo.pathway_event_cohort DROP CONSTRAINT  ' + d.name
from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where
  t.name = 'pathway_event_cohort'
  and t.schema_id = schema_id('dbo')
  and c.name = 'is_deleted';
execute (@command)

ALTER TABLE dbo.pathway_event_cohort DROP COLUMN is_deleted;

	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:345)
	at org.flywaydb.core.internal.command.DbMigrate.access$900(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:276)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273)
	at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146)
	at org.flywaydb.core.internal.dbsupport.DbSupport$1.call(DbSupport.java:223)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.dbsupport.DbSupport.lock(DbSupport.java:219)
	at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174)
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:971)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:971)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1759)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1696)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1626)
	... 61 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: ALTER TABLE DROP COLUMN failed because column 'is_deleted' does not exist in table 'pathway_event_cohort'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:859)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:759)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:739)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy1057.execute(Unknown Source)
	at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:267)
	at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:142)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:76)
	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:343)
	... 84 more

Managed to find some syntax error in the SQL editor, could this be the issue?

The main issue is that it’s trying to drop a column that doesn’t exist. I’m looking through the migration scripts, and I can’t find any reference where is_deleted was ever added to the pathway_event_cohort table. Therefore, I think we should delete this migration from the sourcecode.

To do this on your side, just delete that file from the directory, and re-deploy the war. It shouldn’t see that file to deploy as a migration anymore, and you should be OK. If you do get an error, let us know, and we’ll look at additional steps.

The other option is to upgrade to a later version of SQL Server where the IF EXISTS syntax is valid.

The other error you got about a syntax error: I think it is because the ${} tokens were left in, but the ALTER TABLE {table} DROP COLUMN {column} is valid syntax per this reference.

Thanks @Chris_Knoll, that helped alot.

After removing the code, we are able to deploy WebAPI 2.8.0

Regarding the next step which is the CDM Configuration for WebAPI, we are currently migrating from WebAPI 2.7.0 to 2.8.0, and noticed that there is an additional temp schema that’s needed for temporary storage analysis (not used in 2.7.0).

Just to check,

  1. can the temp schema share the same database as the vocab, results and cdm schemas?
  2. should we just run the ddl for the temp schema, or also for the other schemas as well. Currently the other schemas do contain some tables (e.g. achilles analysis in the results schema)

Thanks!

Yes, the temp schema needs to be in the same database as the results schema (similar to how the results schema must be in the same DB as the cdm schema). The reason for this is that ‘temporary’ analysis tables are created in the temp schema instead of the results schema. In some cases, we weren’t able to use ‘true temp tables’ so the temp schema was added.

There’s no DDL for the temp schema: it’s an empty schema that WebAPI uses to create/drop tables. Sometimes you may have an analysis failure or service restart that may leave some temp tables behind, so having a dedicated temp schema isolates those tables so you can do manual cleanup if necessary.

Thanks, @Chris_Knoll!

Noted there’s no ddl for the temp schema. The ddl script makes changes to the results schema only…

I compared the ddl for the results schema with our existing results schema (used with WebAPI 2.7.0). There are new tables to be added and also changes to the existing tables, listed below:

New tables to be added

  • cohort_censor_stats
  • cohort_cache
  • cohort_censor_stats_cache
  • cohort_inclusion_result_cache
  • cohort_inclusion_stats_cache
  • cohort_summary_stats_cache
  • cohort_sample_element
  • pathway_analysis_codes
  • pathway_analysis_paths

Changes to existing tables

  1. cohort_inclusion – additional column ‘design_hash’
  2. cc_results – additional columns ‘strata_id’, ‘strata_name’
  3. pathway_analysis_stats – ‘target_cohort_count’ and ‘pathways_count’ changed to type BIGINT
  4. Heracles_analysis – more rows of analysis inserted

For the new tables, understand I can insert them using the ddl scripts.

For the existing tables which have changes. I was intending to drop the existing ones and create new tables in the results schema again. However, the tables are already populated with some results (probably from using Atlas earlier) – is it possible to make the above changes to the tables while retaining the existing data…?

Thanks!

Best,
Hui Xing

If you want to retain existing data, you’ll have to do ALTER TABLE instead of DROP/CREATE. Our release notes should have the ALTER DDL for the tables that have changed (we try to help people with that information, for example the 2.8 release notes has a Migration Notes section).

Otherwise, you can rename the old table to something temporary, create the new tables, then INSERT INTO…SELECT FROM the renamed tables…then drop the renamed tables after you confirm you brought over the data.

Thanks @Chris_Knoll. This is very useful!

t