ORA-01461 when saving historic process variable

[Gregor Tudan]

Hi,

are there any known issues with camunda 7.1.0 and Oracle 10g? I'm getting the following exception when trying to safe a large process variable (probably saved as clob):

Caused by: org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
        at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:170)
        at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:99)
        at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
        at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
        at org.camunda.bpm.engine.impl.FormServiceImpl.submitStartForm(FormServiceImpl.java:70)
        at org.camunda.bpm.engine.rest.sub.repository.ProcessDefinitionResourceImpl.submitForm(ProcessDefinitionResourceImpl.java:137)
        ... 47 more
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.HistoricVariableInstanceEntity.insertHistoricVariableInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_VARINST (ID_, PROC_INST_ID_, EXECUTION_ID_, ACT_INST_ID_, TASK_ID_, NAME_, REV_, VAR_TYPE_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)     values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )
### Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
        at org.camunda.bpm.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:667)
        at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:487)
        at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:196)
        at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:126)
        ... 54 more
Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
        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:108)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)

[Frank Langelage]

This might be an oracle bug according to https://community.oracle.com/message/1608792.

[Christian Lipphardt]

Hi,

The thread Frank mentioned also says the exception can also occur when you try to insert a string and it is too long (>4000 bytes).

Did you use a string variable with big content?

Cheers,

Christian

[Gregor Tudan]

Hi Christian, hi Frank,

yes, the variable is a pretty large json-string and my database charset is UTF-8, so the 4000 bytes isn't that much. I've found this discussion on the activiti forum about those kind of variables: http://forums.activiti.org/content/increase-lenght-string-process-variables

I'm going to try saving the string in a serialized form, in order to get it persisted to  to get around this issue, or clear the variable with an end event listener, as I don't need to historize it.

Still, it would be great if the engine would notice this kind of error and issue a warning, since the database error isn't that clear. Even better would be an automatic fallback to storing the variable as byte array.

Thanks,

Gregor

[Christian Lipphardt]

Hi Gregor,

Regarding the size: Oracle states it is limited to 4000 bytes but it depends also on used charset. Using UTF-8 requires 2-3 bytes per character. So in reality you could only store about 1333-2000 characters.

Something like fallback to ByteArray is coming with 7.2.0-Final and the serializable variables feature. (Storing variables as JSON or XML).

Cheers,

Christian

[thorben....@camunda.com]

Hi Gregor and Christian,

The last point is not entirely correct. For a new variable type that allows storing variables as JSON, we were considering to store variables that "fit" into the TEXT_ field in there and otherwise as a byte array in the ACT_GET_BYTEARRAY table. It turns out that determining a robust cutoff limit is highly database-specific and introduces a lot of complexity so that we are dropping this idea for now and simply store these variables regardless of their size as byte arrays in the first step (see [1] for details). So for plain String variables, this current behavior won't change. However, from 7.2.0-Final onwards, you should be able to declare a JSON variable as such in the engine which will then be persisted as byte array.

Cheers,
Thorben

[1] https://app.camunda.com/jira/browse/CAM-2693