DateDiff Overflow while finishing a task older than a month

[martin.ka...@googlemail.com]

Hello,

we are using the CAMUNDA BPM Engine against an SQL-Server and have observed the following exception while calling TaskService.complete with a task id whose task has been started more than a month ago.

### Error updating database. Cause: java.sql.SQLException: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.HistoricTaskInstanceEntity.updateHistoricTaskInstanceEvent_mssql-Inline
### The error occurred while setting parameters
### SQL: update ACT_HI_TASKINST set EXECUTION_ID_ = ?, NAME_ = ?, PARENT_TASK_ID_ = ?, DESCRIPTION_ = ?, OWNER_ = ?, ASSIGNEE_ = ?, DELETE_REASON_ = ?, TASK_DEF_KEY_ = ?, PRIORITY_ = ?, DUE_DATE_ = ? , END_TIME_ = ? , DURATION_ = DATEDIFF(ms, HTI.START_TIME_, ?) FROM ACT_HI_TASKINST HTI where ID_ = ?
### Cause: java.sql.SQLException: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:699)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:500)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:154)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
at org.camunda.bpm.engine.impl.TaskServiceImpl.complete(TaskServiceImpl.java:155)

We were able to prevent the error by overriding the following files in the package org.camunda.bpm.engine.impl.persistence.entity:

- HistoricActivityInstanceEntity.xml
- HistoricProcessInstanceEntity.xml
- HistoricTaskInstanceEntity.xml

; and replacing the Line

<if test="startTime == null"> , DURATION_ = DATEDIFF(ms, HAI.START_TIME_, #{endTime, jdbcType=TIMESTAMP})

with

<if test="startTime == null"> , DURATION_ = DATEDIFF(s, HAI.START_TIME_, #{endTime, jdbcType=TIMESTAMP})

in them.

Our question is: Is it possible to prevent this error without having to override parts of the library?

[thorben....@camunda.com]

Hi,

which Camunda version do you use?

Cheers,
Thorben

[Martin Karsten]

We use 7.0.0-Final. 

[thorben....@camunda.com]

The problem was fixed with this ticket: https://app.camunda.com/jira/browse/CAM-1346
Thus, the fix is available with every version > 7.0.

Regarding a workaround I am not aware of a better solution than you propose.

Cheers,
Thorben

On Tuesday, August 4, 2015 at 1:28:00 PM UTC+2, Martin Karsten wrote:

We use 7.0.0-Final.