MysqlDataTruncation while deploying new process.

[progix_21]

I am getting a MysqlDataTruncation Exception while deploying any process in Activiti Engine. I am using MySQL as Database instead of In-Memory Database. With In-Memory Database, deployment is working fine.

Exception details:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'DEPLOY_TIME_' at row 1
### The error may involve org.activiti.engine.impl.persistence.entity.DeploymentEntity.insertDeployment-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RE_DEPLOYMENT(ID_, NAME_, CATEGORY_, TENANT_ID_, DEPLOY_TIME_)     values(?, ?, ?, ?, ?)
### Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'DEPLOY_TIME_' at row 1
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:572)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:475)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:171)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:118)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
   at org.activiti.engine.impl.RepositoryServiceImpl.deploy(RepositoryServiceImpl.java:78)
   at org.activiti.engine.impl.repository.DeploymentBuilderImpl.deploy(DeploymentBuilderImpl.java:156)
   at com.test.poc.App.main(App.java:38)
   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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'DEPLOY_TIME_' at row 1
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2868)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
   at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
   at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
   … 17 more

[frederikherema1]

We have different SQL-scripts for MySQL 5.5 and 5.6, make sure you're using the correct version. From the user-guide:

Note for MySQL users: MySQL version lower than 5.6.4 has no support for timestamps or dates with millisecond precision. To make things even worse, some version will throw an exception when trying to create such a column but other versions don't. When doing auto-creation/upgrade, the engine will change the DDL when executing it. When using the DDL file approach, both a regular version and a special file with mysql55 in it are available (this applies on anything lower than 5.6.4). This latter file will have column types with no millisecond precision it it.

Concretely, the following applies for MySQL version

<5.6: No millisecond precision available. DDL files available (look for files containing mysql55). Auto creation/update will work out of the box.
5.6.0 - 5.6.3: No millisecond precision available. Auto creation/update will NOT work. It is advised to upgrade to a newer database version anyway. DDL files for mysql 5.5 could be used if really needed.
5.6.4+:Millisecond precision available. DDL files available (default file containing mysql). Auto creation/update works out of the box.
Do note that in the case of upgrading the MySQL database later on and the Activiti tables are already created/upgraded, the column type change will have to be done manually!

I suspect it's related to this, not 100% sure… Can you check your MySQL version?

[payamrastogi]

Hi,
We are getting the same error. If you were able to resolve that problem, kindly let me know the fix.

[trademak]

Which version of Activiti are you using and which version of MySQL?

Best regards,

[prasadprabha07]

I am having the same problem….I am using MySQL version 5.6.19 and Activiti 5.19.0.2.
I executed SQL-scripts for MySQL 5.5, but I dont find SQL-scripts for 5.6

[prasadprabha07]

Yes…now its working …I have changed the mysql-connector-java-3.1.14 to mysql-connector-java-5.1.24