Activiti tables creation issue

[frequentcrasher]

Hi,

I am trying to use Activiti 5.7 inside of a Spring webapp on Tomcat 6 and Oracle 10g database. When I deploy the webapp, I thought the way it should work is the process engine configuration spring bean will check and see that the tables do not exist in the new user schema and proceed to create them. My spring bean config looks like this:

<!– Configure process engine –>
   <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration" lazy-init="true">
      <property name="databaseType" value="oracle" />
      <property name="dataSource" ref="dataSource" />
      <property name="transactionManager" ref="transactionManager" />
      <property name="databaseSchemaUpdate" value="true" />
      <property name="jobExecutorActivate" value="false" />
      <property name="history" value="full" />
   </bean>

However, when Tomcat is started and it attempts to load the webapp, I get an exception in the console and the tables are never created. The error looks something like this:

Jan 25, 2012 2:19:07 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: ORA-00942: table or view does not exist

### The error may involve org.activiti.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-00942: table or view does not exist

   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:69)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:40)
   at org.activiti.engine.impl.db.DbSqlSession.selectById(DbSqlSession.java:217)
   at org.activiti.engine.impl.db.DbSqlSession.dbSchemaUpdate(DbSqlSession.java:629)
   at org.activiti.engine.impl.db.DbSqlSession.performSchemaOperationsProcessEngineBuild(DbSqlSession.java:885)
   at org.activiti.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:25)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
   at org.activiti.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:77)
   at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:270)
   at org.activiti.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:60)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:56)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:32)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1414)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:349)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:973)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:879)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1317)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1076)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:838)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:780)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:697)
   at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:478)
   at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:84)
   at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:283)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1064)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:574)
   at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:276)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:785)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:445)
   at org.apache.catalina.core.StandardService.start(StandardService.java:519)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:581)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist

Since then I have also tried modifying the demo start up ant task and use the ant task internal.db.create to see if that will work and get the same error. I could have swore I tried a quick prototype of this exact same scenario some months ago and it all worked without a hitch. Thanks for any help.

[trademak]

Hi,

Is there a specific reason why you are using lazy-init="true" ?
When you look in the database, can you see that no tables are created?
Are there any error messages before the log messages you already included?

Best regards,

[frequentcrasher]

Thanks for taking the time to look at my question. I got a chance to download and debug through the source code and here's what I realized I was doing that was slightly different. We were setting up a test environment so we created a second oracle user/schema on the same database to have a second environment.

Here's my interpretation of what's going on so please take it with a grain of salt since I'm no expert at this. When the process engine configuration bean is created by Spring, the app eventually find its way to DbSqlSession.dbSchemaCreate() where there's a call to isEngineTablePresent() followed by a call to getDbVersion(). I think the DbSqlSession object created for the isEngineTablePresent call might be instantiated using the constructor that doesn't pass schema so the app thinks the engine tables exists at this point because the call to fetch database meta data indeed find a table named ACT_RU_EXECUTION from the first user/schema and proceed to query the ACT_GE_PROPERTY table for the schema.version property which bombs. I see a DbSqlSession constructor that takes schema but it was used by something else and I didn't find a property I could set that will pass the schema along to the schema check/create piece.

I created a test user schema and started up the app in debug mode pointing to this test database and updated the schema property of the DbSqlSession object on the fly and everything ran great as a test.

On the lazy-init, I don't think I consciously picked it as a choice. Either I copied it from an example or one of the other developers chose it. Since you pointed it out, I assume it is not the recommeded practice and will look in the documentation to see what it does exactly.

I verified that no database tables are created when it errors out each time (we tried multiple times on a couple different developer's local databases). The errors I pasted in the original post were the first error messages to show up each time.

[pgadecki]

I have noticed the same problem in 5.10, with two user schemas in oracle - first with activiti tables and second with no tables. When trying to start process engine with db config set to use second user schema
method isTablePresent returns true as it was checking for any table in the db.

Is this issue fixed in the current release?

[frederikherema1]

Did you specify the "databaseSchema" property on the process-engine-configuration?

[pgadecki]

No, but now I used your advice. I don't have direct access to the db user name, but I was able to plug into process engine configuration initialization and managed to get use of patch https://jira.codehaus.org/secure/attachment/58549/ACT-1062.patch from jira issue https://jira.codehaus.org/browse/ACT-1062 so the code looks like this:

this.databaseSchema = this.dataSource.getConnection().getMetaData().getUserName();
this.dbSqlSessionFactory.setDatabaseSchema(this.databaseSchema);
thanks