[6.0] Replace H2 with a standalone mysql server in activiti-app and activiti-rest

[maximechicois]

Hello, I am currently trying the activiti-app and activiti-rest applications and tried to replace the in-memory H2 database with a mysql server because the 2 applications did not point on the same database. However, when I try to change this configuration, Tomcat returns me a 404 for the associated pages. My goal is to reflect changes in 

My environment is Debian 8 and I'm using Activiti 6.0. I created a user activiti in mysql that has access to the activiti database.

I modified the following file (just commenting the h2 configuration and adding the mysql one) : 

/var/lib/tomcat7/webapps/activiti-app/WEB-INF/classes/META-INF/activiti-app/activiti-app.properties

db=activiti
datasource.driver=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/activiti?characterEncoding=UTF-8
datasource.username=activiti
datasource.password=activiti

#db=h2
#datasource.driver=org.h2.Driver
#datasource.url=jdbc:h2:mem:activiti;DB_CLOSE_DELAY=-1
#datasource.username=sa
#datasource.password=

hibernate.dialect=org.hibernate.dialect.H2Dialect
#hibernate.dialect=org.hibernate.dialect.MySQLDialect
#hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
#hibernate.dialect=org.hibernate.dialect.SQLServerDialect
#hibernate.dialect=org.hibernate.dialect.DB2Dialect
#hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

And the following file : 

/var/lib/tomcat7/webapp/activiti-rest/WEB-INF/classes/db.properties

db=activiti
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/activiti?characterEncoding=UTF-8
jdbc.username=activiti
jdbc.password=activiti

With this configuration, http://localhost:8080/activiti-app returns a 404 while http://localhost:8080/activiti-rest/service/repository/deployments continue to send data from the example (and not from mysql).

I catched the log in /var/log/tomcat7/localhost.2018-06-07.log : "Error initialising form data schema" 

GRAVE: Exception lors de l'envoi de l'évènement contexte initialisé (context initialized) à l'instance de classe d'écoute (listener) org.activiti.app.servlet.WebConfigurer
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'bootstrapper': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.activiti.engine.IdentityService org.activiti.app.conf.Bootstrapper.identityService; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'identityService' defined in class path resource [org/activiti/app/conf/ActivitiEngineConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.activiti.engine.IdentityService]: Factory method 'identityService' threw exception; nested exception is java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:334)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1210)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:755)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:757)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)
at org.activiti.app.servlet.WebConfigurer.contextInitialized(WebConfigurer.java:62)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5027)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5525)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:649)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1083)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1879)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.activiti.engine.IdentityService org.activiti.app.conf.Bootstrapper.identityService; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'identityService' defined in class path resource [org/activiti/app/conf/ActivitiEngineConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.activiti.engine.IdentityService]: Factory method 'identityService' threw exception; nested exception is java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:561)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:331)
... 24 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'identityService' defined in class path resource [org/activiti/app/conf/ActivitiEngineConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.activiti.engine.IdentityService]: Factory method 'identityService' threw exception; nested exception is java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:599)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1119)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1014)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1120)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1044)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:942)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:533)
... 26 more
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.activiti.engine.IdentityService]: Factory method 'identityService' threw exception; nested exception is java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588)
... 38 more
Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.activiti.app.conf.ActivitiEngineConfiguration.processEngine(ActivitiEngineConfiguration.java:84)
at org.activiti.app.conf.ActivitiEngineConfiguration.identityService(ActivitiEngineConfiguration.java:186)
at org.activiti.app.conf.ActivitiEngineConfiguration$$EnhancerBySpringCGLIB$$7874fcd9.CGLIB$identityService$8(<generated>)
at org.activiti.app.conf.ActivitiEngineConfiguration$$EnhancerBySpringCGLIB$$7874fcd9$$FastClassBySpringCGLIB$$8c10758c.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228)
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:309)
at org.activiti.app.conf.ActivitiEngineConfiguration$$EnhancerBySpringCGLIB$$7874fcd9.identityService(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162)
... 39 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:175)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:103)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1517)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:251)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor$1.intercept(ConfigurationClassEnhancer.java:379)
at org.activiti.spring.ProcessEngineFactoryBean$$EnhancerBySpringCGLIB$$c2e3bcf4.getObject(<generated>)
at org.activiti.app.conf.ActivitiEngineConfiguration.processEngine(ActivitiEngineConfiguration.java:82)
... 50 more
Caused by: org.activiti.form.engine.ActivitiFormException: Error initialising form data schema
at org.activiti.form.engine.FormEngineConfiguration.initDbSchema(FormEngineConfiguration.java:581)
at org.activiti.form.engine.FormEngineConfiguration.init(FormEngineConfiguration.java:391)
at org.activiti.form.engine.FormEngineConfiguration.buildFormEngine(FormEngineConfiguration.java:375)
at org.activiti.form.engine.configurator.FormEngineConfigurator.initFormEngine(FormEngineConfigurator.java:59)
at org.activiti.form.engine.configurator.FormEngineConfigurator.beforeInit(FormEngineConfigurator.java:36)
at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.configuratorsBeforeInit(ProcessEngineConfigurationImpl.java:1419)
at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:683)
at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:664)
at org.activiti.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:65)
at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:57)
at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:32)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:168)
... 57 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:689)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at org.activiti.form.engine.FormEngineConfiguration.initDbSchema(FormEngineConfiguration.java:552)
... 68 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1418)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
... 70 more

juin 07, 2018 9:57:34 AM org.apache.catalina.core.StandardContext listenerStop
GRAVE: Exception lors de l'envoi de l'évènement contexte détruit (context destroyed) à l'instance de classe d'écoute org.activiti.app.servlet.WebConfigurer
java.lang.IllegalStateException: No WebApplicationContext found: no ContextLoaderListener registered?
at org.springframework.web.context.support.WebApplicationContextUtils.getRequiredWebApplicationContext(WebApplicationContextUtils.java:83)
at org.activiti.app.servlet.WebConfigurer.contextDestroyed(WebConfigurer.java:125)
at org.apache.catalina.core.StandardContext.listenerStop(StandardContext.java:5068)
at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5720)
at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:232)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:160)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:649)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1083)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1879)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Do you have any idea where the problem is ? Do I have to preconfigure anything in mysql before ? Here is my mysql version : 

mysql --version

mysql  Ver 14.14 Distrib 5.5.60, for debian-linux-gnu (x86_64) using readline 6.3

mysql -uactiviti -pactiviti

use activiti;

SHOW TABLES;
+-----------------------+
| Tables_in_activiti |
+-----------------------+
| ACT_EVT_LOG |
| ACT_GE_BYTEARRAY |
| ACT_GE_PROPERTY |
| ACT_HI_ACTINST |
| ACT_HI_ATTACHMENT |
| ACT_HI_COMMENT |
| ACT_HI_DETAIL |
| ACT_HI_IDENTITYLINK |
| ACT_HI_PROCINST |
| ACT_HI_TASKINST |
| ACT_HI_VARINST |
| ACT_ID_GROUP |
| ACT_ID_INFO |
| ACT_ID_MEMBERSHIP |
| ACT_ID_USER |
| ACT_PROCDEF_INFO |
| ACT_RE_DEPLOYMENT |
| ACT_RE_MODEL |
| ACT_RE_PROCDEF |
| ACT_RU_DEADLETTER_JOB |
| ACT_RU_EVENT_SUBSCR |
| ACT_RU_EXECUTION |
| ACT_RU_IDENTITYLINK |
| ACT_RU_JOB |
| ACT_RU_SUSPENDED_JOB |
| ACT_RU_TASK |
| ACT_RU_TIMER_JOB |
| ACT_RU_VARIABLE |
+-----------------------+
28 rows in set (0.00 sec)

The data inside the database looks like the one shown in activiti-rest so I will suppose this part works. How can I fix the activiti-app please ?

- - - -

PS : By replacing datasource with jdbc in activiti-app/.../activiti-app.properties, the configuration seems to reuse the H2 one as they are not stored after a restart of tomcat7 (which is weird because I commented the H2 configuration).

- - - -

PPS : after editing the activiti-app configuration back to datasource (instead of jdbc), I could access to the application with a persistent database. It modified the application configuration as the data seems to be reflected in database : 

| Tables_in_activiti |
| ACT_DE_DATABASECHANGELOG | 

[...]

| ACT_WO_COMMENTS                    |

| ACT_WO_RELATED_CONTENT     |

| hibernate_sequences                        |

+-------------------------------+
48 rows in set (0.00 sec)

But creating a new process definition / app / task / ... in activiti-app is never reflected in activiti-rest.

I could not create a new user and check if he was created in database because Kermit doesn't have the right to manage users even though he is in the admin group. Thus, I tested a modification of the profile (the lastname field in fact). However, this field is not in any ACT_ID_xxx table. 

Part of the URLs tested in activi-rest are :

activiti-rest/service/runtime/tasks
activiti-rest/service/repository/process-definitions
activiti-rest/runtime/process-instances
activiti-rest/service/identity/users

Thanks by advance.

[bassam_al-saror]

I noticed that you are not using the correct Dialect for MySQL.

#hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.dialect=org.hibernate.dialect.MySQLDialect

Seems that form engine tables were not created

Caused by: org.activiti.form.engine.ActivitiFormException: Error initialising form data schema

Are you using an existing schema or letting Activiti create the tables?

[maximechicois]

Hello, thanks for your response.

Yes, I let Activiti create the tables. I only created a user that has access to the activiti database and linked its credentials in the activiti-app.properties file.

I replaced the H2 configuration with MySQL for Hibernate as you pointed me and re-executed tomcat and now, the databases are correctly linked !

In the db.properties from activiti-rest, there wasn't any definition of Hibernate for H2 in the demo. Should I add the same MySQL configuration as for activiti-app ?

[bassam_al-saror]

No that's not needed for activiti-rest since it doesn't use Hibernate. Is all working fine now?