Are local variables stored in SQL?

Camunda 7 Topics Discussion & Questions
1

I have made an app with a simple service task. Long story short, I know my use case is not ideal for BPM, but it is just for testing boundaries.

I tried storing a large amount of data, like a large rest response, into both a process variable and local process variable. I was getting this sql exception. It seems like Camunda is trying to store all of my process variables in SQL in the column for historic variables.

So the question is, is there a size limit for local scoped and other scoped variables? Do they all get saved to SQL? What do I do if I want to move good-sized variables around?

2017-09-29 09:31:39.408 ERROR 5456 --- [io-8080-exec-10] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: ENGINE-03004 Exception while executing Database Operation 'INSERT HistoricVariableInstanceEntity[49702]' with message '
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)
### 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_DEF_KEY_,       PROC_DEF_ID_,       PROC_INST_ID_,       EXECUTION_ID_,       ACT_INST_ID_,       TENANT_ID_,       CASE_DEF_KEY_,       CASE_DEF_ID_,       CASE_INST_ID_,       CASE_EXECUTION_ID_,       TASK_ID_,       NAME_,       REV_,       VAR_TYPE_,       BYTEARRAY_ID_,       DOUBLE_,       LONG_,       TEXT_,       TEXT2_     ) values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )
### Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)'. Flush summary: 
 [
  INSERT HistoricVariableInstanceEntity[49702]
  INSERT HistoricVariableInstanceEntity[49704]
  INSERT HistoricVariableInstanceEntity[49713]
  INSERT HistoricVariableInstanceEntity[49718]
  INSERT HistoricVariableInstanceEntity[49731]
  INSERT HistoricVariableUpdateEventEntity[49706]
  INSERT HistoricVariableUpdateEventEntity[49708]
  INSERT HistoricVariableUpdateEventEntity[49715]
  INSERT HistoricVariableUpdateEventEntity[49720]
  INSERT HistoricVariableUpdateEventEntity[49724]
  INSERT HistoricVariableUpdateEventEntity[49728]
  INSERT HistoricVariableUpdateEventEntity[49733]
  INSERT HistoricProcessInstanceEventEntity[49701]
  INSERT HistoricActivityInstanceEventEntity[StartEvent_1:49705]
  INSERT HistoricActivityInstanceEventEntity[Task_0w6wycg:49711]
  INSERT HistoricActivityInstanceEventEntity[receiveTask:49735]
  INSERT ExecutionEntity[49701]
  INSERT ByteArrayEntity[49703]
  INSERT ByteArrayEntity[49707]
  INSERT ByteArrayEntity[49709]
  INSERT ByteArrayEntity[49714]
  INSERT ByteArrayEntity[49719]
  INSERT ByteArrayEntity[49722]
  INSERT ByteArrayEntity[49723]
  INSERT ByteArrayEntity[49725]
  INSERT ByteArrayEntity[49726]
  INSERT ByteArrayEntity[49727]
  INSERT ByteArrayEntity[49729]
  INSERT ByteArrayEntity[49730]
  INSERT ByteArrayEntity[49732]
  INSERT ByteArrayEntity[49734]
  INSERT VariableInstanceEntity[49702]
  INSERT VariableInstanceEntity[49704]
  INSERT VariableInstanceEntity[49713]
  INSERT VariableInstanceEntity[49718]
  INSERT VariableInstanceEntity[49731]
]

org.camunda.bpm.engine.ProcessEngineException: ENGINE-03004 Exception while executing Database Operation 'INSERT HistoricVariableInstanceEntity[49702]' with message '
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)
### 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_DEF_KEY_,       PROC_DEF_ID_,       PROC_INST_ID_,       EXECUTION_ID_,       ACT_INST_ID_,       TENANT_ID_,       CASE_DEF_KEY_,       CASE_DEF_ID_,       CASE_INST_ID_,       CASE_EXECUTION_ID_,       TASK_ID_,       NAME_,       REV_,       VAR_TYPE_,       BYTEARRAY_ID_,       DOUBLE_,       LONG_,       TEXT_,       TEXT2_     ) values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )
### Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)'. Flush summary: 
 [
  INSERT HistoricVariableInstanceEntity[49702]
  INSERT HistoricVariableInstanceEntity[49704]
  INSERT HistoricVariableInstanceEntity[49713]
  INSERT HistoricVariableInstanceEntity[49718]
  INSERT HistoricVariableInstanceEntity[49731]
  INSERT HistoricVariableUpdateEventEntity[49706]
  INSERT HistoricVariableUpdateEventEntity[49708]
  INSERT HistoricVariableUpdateEventEntity[49715]
  INSERT HistoricVariableUpdateEventEntity[49720]
  INSERT HistoricVariableUpdateEventEntity[49724]
  INSERT HistoricVariableUpdateEventEntity[49728]
  INSERT HistoricVariableUpdateEventEntity[49733]
  INSERT HistoricProcessInstanceEventEntity[49701]
  INSERT HistoricActivityInstanceEventEntity[StartEvent_1:49705]
  INSERT HistoricActivityInstanceEventEntity[Task_0w6wycg:49711]
  INSERT HistoricActivityInstanceEventEntity[receiveTask:49735]
  INSERT ExecutionEntity[49701]
  INSERT ByteArrayEntity[49703]
  INSERT ByteArrayEntity[49707]
  INSERT ByteArrayEntity[49709]
  INSERT ByteArrayEntity[49714]
  INSERT ByteArrayEntity[49719]
  INSERT ByteArrayEntity[49722]
  INSERT ByteArrayEntity[49723]
  INSERT ByteArrayEntity[49725]
  INSERT ByteArrayEntity[49726]
  INSERT ByteArrayEntity[49727]
  INSERT ByteArrayEntity[49729]
  INSERT ByteArrayEntity[49730]
  INSERT ByteArrayEntity[49732]
  INSERT ByteArrayEntity[49734]
  INSERT VariableInstanceEntity[49702]
  INSERT VariableInstanceEntity[49704]
  INSERT VariableInstanceEntity[49713]
  INSERT VariableInstanceEntity[49718]
  INSERT VariableInstanceEntity[49731]
]
	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.flushDbOperationException(EnginePersistenceLogger.java:115)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:305)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:281)
	at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:203)
	at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:132)
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:113)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66)
	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
	at org.camunda.bpm.engine.impl.ProcessInstantiationBuilderImpl.executeWithVariablesInReturn(ProcessInstantiationBuilderImpl.java:162)
	at org.camunda.bpm.engine.impl.ProcessInstantiationBuilderImpl.execute(ProcessInstantiationBuilderImpl.java:128)
	at org.camunda.bpm.engine.impl.ProcessInstantiationBuilderImpl.execute(ProcessInstantiationBuilderImpl.java:124)
	at org.camunda.bpm.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:94)
	at com.transamerica.platform.insurance.life.service.RequirementsDecisionService.processRequirements2(RequirementsDecisionService.java:115)
	at com.transamerica.platform.insurance.life.service.AlertService.populateAlertsProcess2(AlertService.java:185)
	at com.transamerica.platform.insurance.life.controller.LifeAppTrackerRestController.getTransformedData2(LifeAppTrackerRestController.java:103)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.transamerica.platform.headers.spring.LoggerHeaderFilter.doFilter(LoggerHeaderFilter.java:196)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)
### 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_DEF_KEY_,       PROC_DEF_ID_,       PROC_INST_ID_,       EXECUTION_ID_,       ACT_INST_ID_,       TENANT_ID_,       CASE_DEF_KEY_,       CASE_DEF_ID_,       CASE_INST_ID_,       CASE_EXECUTION_ID_,       TASK_ID_,       NAME_,       REV_,       VAR_TYPE_,       BYTEARRAY_ID_,       DOUBLE_,       LONG_,       TEXT_,       TEXT2_     ) values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )
### Cause: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeInsertEntity(DbSqlSession.java:146)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.insertEntity(DbSqlSession.java:138)
	at org.camunda.bpm.engine.impl.db.AbstractPersistenceSession.executeDbOperation(AbstractPersistenceSession.java:41)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:303)
	... 85 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(4000)
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
	... 90 common frames omitted
2

Strings have a 4000 character limit.

Store your data using SPIN/JSON.

https://docs.camunda.org/manual/7.7/reference/spin/json/01-reading-json/

It will store as a bytearray/blob, so you wont have a size issue.

3

Thanks, I found that having the history level set to audit, it stores all of my variables. Your saying I can sort of compress the data a bit using spin. That may only take me so far though, if data size keeps increasing etc.

Do you think it is a good practice, or feasible to increase the table size past 4000, to say maybe 10000? Does the camunda project expose any configuration for this? Or is it more hardcoded into the library?

4

Before I implement spin, are you saying that if I serialize my variables using Spin, that the size of the variables wont matter, because there is not a size limit on that particular column for storing blobs? Or will spin just help decrease the size a bit?

Your saying if I use spin on my variables I wont have to worry about their size at all?

5

You store your data as a spin variables rather than a string variable. The data is stored differently in the db. You have blob/bytearray size limits rather than the string limit.

6

Thanks, I’ll give spin a shot

7