How to handle Database connection closed issue?

Hao_Kuang · May 24, 2018, 5:08pm

We just migrate our database to use mariadb and the time of executing bpmn flow is longer than the db timeout which prints

SQL: select * from ACT_RE_DEPLOYMENT where ID_ = ?
Cause: java.sql.SQLNonTransientConnectionException: Could not read resultset: unexpected end of stream, read 0 bytes from 4
Query is: select * from ACT_RE_DEPLOYMENT where ID_ = ?, parameters [‘d5b7dadc-5f6d-11e8-b61e-2ada3ae472d5’]
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:66) ~[mybatis-3.2.8.jar:3.2.8]
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectById(DbSqlSession.java:101) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectById(DbEntityManager.java:197) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.persistence.entity.DeploymentManager.findDeploymentById(DeploymentManager.java:209) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.persistence.deploy.cache.ResourceDefinitionCache.resolveDefinition(ResourceDefinitionCache.java:104) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.persistence.deploy.cache.ResourceDefinitionCache.findDeployedDefinitionById(ResourceDefinitionCache.java:52) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.persistence.deploy.cache.DeploymentCache.findDeployedProcessDefinitionById(DeploymentCache.java:73) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.cmd.GetDeployedProcessDefinitionCmd.findById(GetDeployedProcessDefinitionCmd.java:76) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.cmd.GetDeployedProcessDefinitionCmd.find(GetDeployedProcessDefinitionCmd.java:68) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.cmd.GetDeployedProcessDefinitionCmd.execute(GetDeployedProcessDefinitionCmd.java:53) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.cmd.GetDeployedProcessDefinitionCmd.execute(GetDeployedProcessDefinitionCmd.java:25) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) ~[camunda-engine-7.7.0.jar:7.7.0]
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104) ~[camunda-engine-7.7.0.jar:7.7.0]
… 294 common frames omitted
Caused by: java.sql.SQLNonTransientConnectionException: Could not read resultset: unexpected end of stream, read 0 bytes from 4
Query is: select * from ACT_RE_DEPLOYMENT where ID_ = ?, parameters [‘d5b7dadc-5f6d-11e8-b61e-2ada3ae472d5’]
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125) ~[na:na]
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:71) ~[na:na]
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:226) ~[na:na]
at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:411) ~[na:na]
at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:359) ~[na:na]
at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:303) ~[na:na]
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) ~[mybatis-3.2.8.jar:3.2.8]
… 308 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Could not read resultset: unexpected end of stream, read 0 bytes from 4
Query is: select * from ACT_RE_DEPLOYMENT where ID_ = ?, parameters [‘d5b7dadc-5f6d-11e8-b61e-2ada3ae472d5’]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1063) ~[na:na]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:601) ~[na:na]
at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:398) ~[na:na]
… 319 common frames omitted
Caused by: java.io.EOFException: unexpected end of stream, read 0 bytes from 4
at org.mariadb.jdbc.internal.packet.read.ReadPacketFetcher.getReusableBuffer(ReadPacketFetcher.java:178) ~[na:na]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1054) ~[na:na]
… 321 common frames omitted

The interactive_timeout and wait_timeout are both 8 hours. Mu question is that is that part of camunda configuration? Do I have a way to catch this issue in BPMN?

Below is my jboss JNDI datasource configuration:

    <datasource jta="true" jndi-name="java:jboss/datasources/ProcessEngine" pool-name="ProcessEngine" enabled="true" use-java-context="true" use-ccm="true">
            <connection-url>jdbc:mariadb://onap-hkuang-mariadb:3306/camundabpmn?autoReconnect=true&amp;connectTimeout=60000&amp;socketTimeout=60000</connection-url>
            <driver>mariadb</driver>
            <new-connection-sql>set autocommit=1</new-connection-sql>
            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
            <pool>
                <min-pool-size>10</min-pool-size>
                <max-pool-size>100</max-pool-size>
                <prefill>true</prefill>
                <use-strict-min>false</use-strict-min>
                <flush-strategy>FailingConnectionOnly</flush-strategy>
            </pool>
            <security>
                <user-name>camunda</user-name>
                <password>camunda</password>
            </security>
            <validation>
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
            </validation>
            <statement>
                <prepared-statement-cache-size>32</prepared-statement-cache-size>
                <share-prepared-statements>true</share-prepared-statements>
            </statement>
        </datasource>

Thanks

Yana · May 25, 2018, 3:41pm

Hi @Hao_Kuang,

I see that you are using 7.7.0 Camunda version.
Which db version and driver version do you use?

Best regards,
Yana

Hao_Kuang · May 25, 2018, 4:31pm

Hi Yana,

mysql Ver 15.1 Distrib 10.1.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
JBoss Modules version 1.5.2.Final WildFly Full 10.1.0.Final (WildFly Core 2.2.0.Final)
./modules/mariadb/main/mariadb-java-client-1.5.4.jar

Best,
Hao

Yana · May 30, 2018, 6:03am

Hi @Hao_Kuang,

Sorry for the delay.
How did you migrate to mariadb? Did you start with new schema?
How often do you see this error? Does it occur only for particular deployment?

Best regards,
Yana

ironijunior · May 31, 2021, 2:24pm

Hello,

We are facing the same issue after migrating to MariaDB.
The only difference is the error that happens on

Caused by: java.sql.SQLException: unexpected end of stream, read 0 bytes from 4 (socket was closed by server)
Query is: select * from ACT_RU_EXECUTION where PROC_INST_ID_ = ?, parameters ['<id>']

Camunda version: 7.11.0
Camunda Sprint Boot Starter Version: 3.3.5
MariaDB version: 10.5.9-MariaDB-log
mariadb-java-client version: 2.2.4

How can we solve this problem?