Activiti 5.0.rc1 - MySQL Database creation

[akakunin]

Hi!
I'm trying to setup activiti process engine with using Spring on top of my mysql database. This database has no activiti tables yet, so, I'm using "create-if-necessary" strategy. Process Engine configure in Spring like:

   <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
      <property name="dataSource" ref="dataSource"/>
      <property name="transactionManager" ref="transactionManager" />
      <property name="dbSchemaStrategy" value="create-if-necessary" />
   </bean>   
‍‍‍‍‍‍‍

But during application start-up I've got error like:

Caused by: org.activiti.engine.ActivitiException: couldn't create or drop db schema
   at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:311)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.dbSchemaCreate(DbSqlSessionFactory.java:264)
   at org.activiti.engine.impl.ProcessEngineImpl.performSchemaOperationsCreate(ProcessEngineImpl.java:109)
   at org.activiti.engine.impl.ProcessEngineImpl$1.execute(ProcessEngineImpl.java:70)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:22)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:36)
   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:68)
   at org.activiti.engine.impl.cfg.ProcessEngineConfiguration.buildProcessEngine(ProcessEngineConfiguration.java:302)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:83)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:54)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
   … 30 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'longvarbinary,
    primary key (ID_)
)' at line 6
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
   at com.mysql.jdbc.Statement.execute(Statement.java:714)
   at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:293)
   … 44 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Is it bug or I'm doing something wrong?

[akakunin]

BTW  I'm not 100% sure, but it looks like it is failed on ACT_GE_BITEARRAY table creation

[jbarrez]

Alexey, you need to set the databaseType to 'mysql', otherwise by default the h2 creation scripts will be executed (which explains your error).

http://activiti.org/userguide/index.html#N103BA

[akakunin]

Thanks.
2 questions:
1. Are any plans for autodetection of DB type (at least for supported databases)?
2. Setting databaseType to 'mysql' helps a little bit. Not all tables are created, but at the end I've got error like:

18:02:17,589 ERROR [ContextLoader:220] Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.engine.ActivitiException: couldn't create or drop db schema
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:149)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1435)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:245)
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.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:575)
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:3972)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4467)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1041)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:964)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
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:443)
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: org.activiti.engine.ActivitiException: couldn't create or drop db schema
at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:311)
at org.activiti.engine.impl.db.DbSqlSessionFactory.dbSchemaCreate(DbSqlSessionFactory.java:264)
at org.activiti.engine.impl.ProcessEngineImpl.performSchemaOperationsCreate(ProcessEngineImpl.java:109)
at org.activiti.engine.impl.ProcessEngineImpl$1.execute(ProcessEngineImpl.java:70)
at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:22)
at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:36)
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:68)
at org.activiti.engine.impl.cfg.ProcessEngineConfiguration.buildProcessEngine(ProcessEngineConfiguration.java:302)
at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:83)
at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:54)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
… 43 more
Caused by: java.sql.SQLException: Can't create table 'liferay_activiti.#sql-49a_129' (errno: 121)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
at com.mysql.jdbc.Statement.execute(Statement.java:714)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:293)
… 57 more

Looks like all tables are created, but processEngine is not initialized correctly.
BTW: My configuraiton is: ubuntu 10.10 + sub jdk 1.6.0_22 + mysql 5.1.49

[jbarrez]

Hi Alexey,

We've been thinking about auto-db-detection, but we didnt find a solution that would work in every scenario. Do you have any insights there ?

Regarding your second issue:
I see the following line:

Can't create table 'liferay_activiti.#sql-49a_129'

Any idea what the '#sql-49a_129' refers to?

[akakunin]

Hi Joram!
I'm not sure about this table… Probably this problem reproduced only on my environment and specific only for me - hope so. Anyway - it is not so critical.

About autodetection - since activiti supported only very limited set of database - it is not so complex I think.
For example I've added kind of autodetect in my own ProcessEngineFactory implementation.
You can check sources here: http://www.emforge.net/web/activiti-liferay/sources?p_p_id=scmportlet_WAR_emforgeportlet&p_p_lifecyc...

[jbarrez]

Thanks for the pointer.
I added a Jira issue to include this feature (but wont make it in 5.0):
http://jira.codehaus.org/browse/ACT-331