Spring Boot doesn`t start with Oracle. Schema-update doesn`t work


#1

Hello!
I try integrate application with Oracle database. Oracle scheme is empty.
The following query returns nothing
select * from user_objects

application.yaml:

camunda.bpm:
  admin-user:
    id: test
    password: test
    firstName: Kermit
  filter:
    create: All tasks
  database:
    schema-update: drop-create
    type: oracle

spring.datasource.url: "jdbc:oracle:thin:@srv7-wastani:1521:oracle"
spring.datasource.username: "CAMUNDA"
spring.datasource.password: "CAMUNDA"
spring.datasource.driverClassName: "oracle.jdbc.driver.OracleDriver"

and I get following logs:

2018-02-12 11:23:28.179 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'alter table ACT_RE_DECISION_DEF 
drop constraint ACT_FK_DEC_REQ'. Cause: 'ORA-00942: table or view does not exist
'
2018-02-12 11:23:28.183 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'drop index ACT_IDX_DEC_DEF_TENANT_ID'. Cause: 'ORA-01418: specified index does not exist
'
2018-02-12 11:23:28.186 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'drop index ACT_IDX_DEC_DEF_REQ_ID'. Cause: 'ORA-01418: specified index does not exist
'
2018-02-12 11:23:28.189 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'drop index ACT_IDX_DEC_REQ_DEF_TENANT_ID'. Cause: 'ORA-01418: specified index does not exist
'
2018-02-12 11:23:28.194 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'drop table ACT_RE_DECISION_DEF'. Cause: 'ORA-00942: table or view does not exist
'
2018-02-12 11:23:28.198 ERROR 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03015 Problem during schema operation 'drop' with statement 'drop table ACT_RE_DECISION_REQ_DEF'. Cause: 'ORA-00942: table or view does not exist
'
2018-02-12 11:23:28.198  INFO 13876 --- [           main] org.camunda.bpm.engine.persistence       : ENGINE-03016 Performing database operation 'drop' on component 'decision.engine' with resource 'org/camunda/bpm/engine/db/drop/activiti.oracle.drop.decision.engine.sql'
2018-02-12 11:23:28.290 ERROR 13876 --- [           main] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist


org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

2018-02-12 11:23:28.304 ERROR 13876 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Failed to destroy the filter named [Engines Filter] of type [org.camunda.bpm.spring.boot.starter.webapp.filter.LazyProcessEnginesFilter]
java.lang.NullPointerException: null
	at org.camunda.bpm.spring.boot.starter.webapp.filter.LazyDelegateFilter.destroy(LazyDelegateFilter.java:49) ~[camunda-bpm-spring-boot-starter-webapp-core-2.3.0.jar:2.3.0]
	at org.apache.catalina.core.ApplicationFilterConfig.release(ApplicationFilterConfig.java:318) ~[tomcat-embed-core-8.5.23.jar:8.5.23]
    ...
2018-02-12 11:23:28.304 ERROR 13876 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Failed to destroy the filter named [Security Filter] of type [org.camunda.bpm.spring.boot.starter.webapp.filter.LazySecurityFilter]
java.lang.NullPointerException: null
	at org.camunda.bpm.spring.boot.starter.webapp.filter.LazyDelegateFilter.destroy(LazyDelegateFilter.java:49) ~[camunda-bpm-spring-boot-starter-webapp-core-2.3.0.jar:2.3.0]
    ...
2018-02-12 11:23:28.318 ERROR 13876 --- [           main] o.s.boot.SpringApplication               : Application startup failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'webappExampleProcessApplication': Unsatisfied dependency expressed through field 'runtimeService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.camunda.bpm.engine.spring.SpringProcessEngineServicesConfiguration': Unsatisfied dependency expressed through field 'processEngine'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngineFactoryBean': FactoryBean threw exception on object creation; nested exception is org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
	....
	... 49 common frames omitted
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

I don`t understand why select VALUE_ from ACT_GE_PROPERTY is executed instead of create required tables.

If I change schema-update on false then

2018-02-12 11:41:42.268 ERROR 14256 --- [           main] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: ENGINE-03057 There are no Camunda tables in the database. Hint: Set <property name="databaseSchemaUpdate" to value="true" or value="create-drop" (use create-drop for testing only!) in bean processEngineConfiguration in camunda.cfg.xml for automatic schema creation

org.camunda.bpm.engine.ProcessEngineException: ENGINE-03057 There are no Camunda tables in the database. Hint: Set <property name="databaseSchemaUpdate" to value="true" or value="create-drop" (use create-drop for testing only!) in bean processEngineConfiguration in camunda.cfg.xml for automatic schema creation

#2

I know this is probably a silly question, but have you tried running the sql statements outside of SpringBoot?

Also I wouldn’t worry about the initial set of errors, that is Oracle complaining about the scripts trying to drop tables etc. that don’t exist in the first place - that is expected when you are bootstrapping the schema.

I would turn on some debug logging to get a better idea of the whats happening here.

Cheers, Conor.


#3

Yes, I tried, it`s working. I executed sql/create/oracle_engine_7.8.0.sql and sql/create/oracle_identity_7.8.0.sql scripts through JDBC and it did. After I changed schema-update on false and application was started good.

After I changed schema-update on drop-create and it wasn`t start. The errors is same above.

2018-02-13 13:16:55.456 ERROR 17180 --- [           main] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist


org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.4.4.jar:3.4.4]
...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
...
	... 70 common frames omitted

2018-02-13 13:16:55.466  WARN 17180 --- [           main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'webappExampleProcessApplication': Unsatisfied dependency expressed through field 'runtimeService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.camunda.bpm.engine.spring.SpringProcessEngineServicesConfiguration': Unsatisfied dependency expressed through field 'processEngine'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngineFactoryBean': FactoryBean threw exception on object creation; nested exception is org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
2018-02-13 13:16:55.466  INFO 17180 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'camundaTaskExecutor'
2018-02-13 13:16:55.476  INFO 17180 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2018-02-13 13:16:55.476 ERROR 17180 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Failed to destroy the filter named [Engines Filter] of type [org.camunda.bpm.spring.boot.starter.webapp.filter.LazyProcessEnginesFilter]

java.lang.NullPointerException: null
	at org.camunda.bpm.spring.boot.starter.webapp.filter.LazyDelegateFilter.destroy(LazyDelegateFilter.java:49) ~[camunda-bpm-spring-boot-starter-webapp-core-2.3.0.jar:2.3.0]
	at org.apache.catalina.core.ApplicationFilterConfig.release(ApplicationFilterConfig.java:318) ~[tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.StandardContext.filterStop(StandardContext.java:4623) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5421) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:226) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1435) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1424) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_144]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]

2018-02-13 13:16:55.476 ERROR 17180 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Failed to destroy the filter named [Security Filter] of type [org.camunda.bpm.spring.boot.starter.webapp.filter.LazySecurityFilter]

java.lang.NullPointerException: null
	at org.camunda.bpm.spring.boot.starter.webapp.filter.LazyDelegateFilter.destroy(LazyDelegateFilter.java:49) ~[camunda-bpm-spring-boot-starter-webapp-core-2.3.0.jar:2.3.0]
	at org.apache.catalina.core.ApplicationFilterConfig.release(ApplicationFilterConfig.java:318) ~[tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.StandardContext.filterStop(StandardContext.java:4623) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5421) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:226) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1435) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1424) [tomcat-embed-core-8.5.23.jar:8.5.23]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_144]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]

2018-02-13 13:16:55.486  INFO 17180 --- [           main] utoConfigurationReportLoggingInitializer : 

Error starting ApplicationContext. To display the auto-configuration report re-run your application with 'debug' enabled.
2018-02-13 13:16:55.496 ERROR 17180 --- [           main] o.s.boot.SpringApplication               : Application startup failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'webappExampleProcessApplication': Unsatisfied dependency expressed through field 'runtimeService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.camunda.bpm.engine.spring.SpringProcessEngineServicesConfiguration': Unsatisfied dependency expressed through field 'processEngine'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngineFactoryBean': FactoryBean threw exception on object creation; nested exception is org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
...
	... 19 common frames omitted
...
	... 41 common frames omitted
...
	... 49 common frames omitted
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.4.4.jar:3.4.4]
...
	... 61 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
...
	... 70 common frames omitted

I tried to analyse debug log but I didn`t see the cause of this behavior.

Thanks for answer. I will try to debug start.


#4

I have oracle sheme (sheme2) where apllication start with schema-update on drop-create. But I dont understand why It doesnt work on the sheme1 and work on the scheme2.