Insert into ACT_HI_VARINST causes ORA-01401: inserted value too large for column

[soaguy]

Hello all,
             I am running into an issue when I set a xml string into a string variable and starting a process instance as below,

——> Error code

                        Map<String, Object> variableMap = new HashMap<String, Object>();         variableMap.put("_JMSMessage", messageRequest);         pid = runtimeService.startProcessInstanceByKey("responseHandler", variableMap).getId();‍‍‍‍‍

I get the error below,

### Error updating database.  Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column### The error may involve org.activiti.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_, TASK_ID_, NAME_, REV_, VAR_TYPE_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_, CREATE_TIME_, LAST_UPDATED_TIME_)     values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )### Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column   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.flushRegularInsert(DbSqlSession.java:825)   at org.activiti.engine.impl.db.DbSqlSession.flushPersistentObjects(DbSqlSession.java:805)   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:789)   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:610)   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:137)   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)   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.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:77)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

But when I use the same message (in a correlation type interaction) as below it works.

—-> Working code

            Map<String, Object> variableMap = new HashMap<String, Object>();            variableMap.put("_MessageName", messageName);            variableMap.put("_MessageContent", messageRequest);            runtimeService.messageEventReceived(messageName, execution.getId(), variableMap);‍‍‍‍‍‍

Can you please help on how to overcome this issue and the reason why there is a conflicting behavior ? I am using Activiti v5.18.

Many thanks
Clement

[jbarrez]

What's messageRequest? is it a string? The type of the variable defines in which column it will be stored. But normally, when it's a string it would switch to storing it as a blob if it's too large.

[soaguy]

Yes, messageRequest is a String. It looks like it is not switching to store it as a BLOB but instead in the TEXT column as a string.

[vasile_dirla]

Hi Clement ,
I'll try to reproduce the issue but it will be wonderful if you could upload a unit test to show this issue.

[vasile_dirla]

Just tested a process instance with a string variable containing a very long string (302 MB).
It is saved correctly and it is also retrieved correctly in my test.
- Activiti 5.18
- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

If you could provide a unit test which fails then I could test it in my environment and then will be easier to debug and eventually fix.

[soaguy]

Hi Vasile,
              I tried with a large string and it works for me as well. It gets stored as 'longString' and as BLOB in the bytearray table.  I have narrowed it to the xml string that is being passed in. The java REST code is below.

<java>
package org.activiti.rest.service.api.cmf;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.activiti.engine.RuntimeService;
import org.activiti.engine.runtime.Execution;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import org.tiaa.bpm.pojo.JMSMessageWrapper;
import org.tiaa.bpm.pojo.MessageCorrelationRequest;
import org.tiaa.bpm.pojo.MessageCorrelationResponse;

@RestController
public class CustomRestController {

@Autowired
private RuntimeService runtimeService;


@RequestMapping(value = "/cmf/postResponseToBPM2", method = RequestMethod.POST, produces = {"application/json","application/xml"})
public String postResponseToBPM2(@RequestBody String messageRequest, HttpServletRequest request) {
  System.out.println(" postResponseBPM method..message size–>" + messageRequest.length());
  String pid = null;
  Map<String, Object> variableMap = new HashMap<String, Object>();
  variableMap.put("_JMSMessage", messageRequest);
  pid = runtimeService.startProcessInstanceByKey("responseHandler", variableMap).getId();
  System.out.println(" postResponseBPM method..Started pid–>" + pid);
  return pid;

}

private boolean checkValidString(String str) {
  if (str != null && !str.isEmpty())
   return true;
  return false;

}

}
</java>

I have attached the xml string (mocked_response.txt) that is passed into the method which causes the problem. The error log is also attached.

Thanks much
Clement

[vasile_dirla]

Hi,
Yes found the reason and created a pull request for it.
see: https://github.com/vasiledirla/Activiti/commit/8557fb8d17ebbe21e79e5c5005902d4979648bc1

the problem could be reproduced only with the strings containing between 2000 and 4000 characters.

[ganeshr]

I'm also seeing the same issue but we are using Activiti 5.17 version.
Any help on how to resolve that ?

<code>
08-Jan-2016[15:01:07.550]::ERROR:ool-1-thread-3:rg.activiti.engine.impl.jobexecutor.ExecuteJobsRunnable:96 - exception during job execution:
### Error updating database. Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column

### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertVariableInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) values ( ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
### Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column

org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column

### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertVariableInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) values ( ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
### Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column
</code>

[vasile_dirla]

Hi Ganesh,
just upgrade to the latest version of Activiti if possible, otherwise you could take the code from my PR and adjust it for your version.

[mahessub7]

Hi ,

I am facing the same problem with 5.18 version
inserted value too large for column in ACT_RU_VARIABLE

INSERT ALL INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INTO ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) SELECT * FROM dual
### The error occurred while setting parameters
### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.bulkInsertVariableInstance_oracle-Inline
### Error updating database. Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column