BoneCP throws "SQLException: Connection is closed!" when batch inserting into MySQL

Question

I've been tasked with setting up a project using BoneCP with jOOQ and Spring, but I've run into some difficulties doing so. Doing individual inserts into my MySQL-database works perfectly fine, but doing so with 190 000 objects takes almost 20 minutes, so to speed it up I want to use batch inserts of 100 at a time instead. However, this throws the following exception:

org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLException: Connection is closed!
   at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:288)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:849)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:826)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:496)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:266)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
   at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
   at com.theshahin.service.YmsLinkDataService$$EnhancerBySpringCGLIB$$b9b6e447.create(<generated>)
   at com.theshahin.integration.YmsLinkDataServiceTest.foo(YmsLinkDataServiceTest.java:76)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
   at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
   at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
   at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
   at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
   at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
   at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
   at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
   at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
   at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
   at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
   at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
   at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
   at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
   at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
   at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)
   at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:53)
   at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:123)
   at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:104)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
   at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
   at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
   at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:107)
   at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)
Caused by: java.sql.SQLException: Connection is closed!
   at com.jolbox.bonecp.ConnectionHandle.checkClosed(ConnectionHandle.java:459)
   at com.jolbox.bonecp.ConnectionHandle.rollback(ConnectionHandle.java:1270)
   at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:285)
   ... 44 more

(It may be worth mentioning that this exception is thrown at the very first batch query, so no queries have been executed prior to it). This is my applicationContext.xml, which is based on the one from jOOQ's tutorial (you can find it here: http://www.jooq.org/doc/3.3/manual/getting-started/tutorials/jooq-with-spring/ ):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
            http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

<context:component-scan base-package="com.theshahin" />
<context:property-placeholder location="classpath:application.properties" ignore-resource-not-found="false"/>

<tx:annotation-driven transaction-manager="transactionManager"/>

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
    <property name="driverClass" value="${db.driver}"/>
    <property name="jdbcUrl" value="${db.url}"/>
    <property name="username" value="${db.username}"/>
    <property name="password" value="${db.password}"/>
    <property name="idleConnectionTestPeriod" value="60"/>
    <property name="idleMaxAge" value="240"/>
    <property name="maxConnectionsPerPartition" value="30"/>
    <property name="minConnectionsPerPartition" value="10"/>
    <property name="partitionCount" value="3"/>
    <property name="acquireIncrement" value="5"/>
    <property name="statementsCacheSize" value="100"/>
    <property name="releaseHelperThreads" value="3"/>
</bean>

<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="transactionAwareDataSource"
    class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <constructor-arg ref="dataSource" />
</bean>

<bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">
    <constructor-arg ref="transactionAwareDataSource" />
</bean>

<bean id="dsl" class="org.jooq.impl.DefaultDSLContext">
    <constructor-arg ref="config" />
</bean>

<bean id="jooqToSpringExceptionTransformer" class="com.theshahin.exception.JOOQToSpringExceptionTransformer"/>

<bean class="org.jooq.impl.DefaultConfiguration" name="config">
    <constructor-arg index="0" ref="connectionProvider" />
    <constructor-arg index="1"><null /></constructor-arg>
    <constructor-arg index="2"><null /></constructor-arg>
    <constructor-arg index="3">
        <list>
            <bean class="org.jooq.impl.DefaultExecuteListenerProvider">
                <constructor-arg index="0" ref="jooqToSpringExceptionTransformer"/>
            </bean>
        </list>
    </constructor-arg>
    <constructor-arg index="4"><null /></constructor-arg>
    <constructor-arg index="5"><value type="org.jooq.SQLDialect">${jooq.sql.dialect}</value></constructor-arg>
    <constructor-arg index="6"><null /></constructor-arg>
    <constructor-arg index="7"><null /></constructor-arg>
</bean>

This is the code used for saving the records to the MySQL-database. (Note: the out-commented code is the one I use for individual inserts)

@Service
public class YmsLinkDataService extends BaseService {

    @Transactional
    public void create(List<YmsLinkDataRecord> records) {
        dsl.batchInsert(records).execute();

//        dsl.insertInto(YMS_LINK_DATA, YMS_LINK_DATA.SITE_ID,
//                YMS_LINK_DATA.SITE_TYPE, YMS_LINK_DATA.TIME, YMS_LINK_DATA.URL,
//                YMS_LINK_DATA.KEYWORD).values(linkData.getSiteId(),
//                        YmsLinkDataSiteType.SEARCH, System.currentTimeMillis(),
//                        linkData.getUrl(), linkData.getKeyword()).execute();

    }
}

Here's the test case from which the error is thrown (I am aware that it doesn't actually test anything at the moment. I will do that once it successfully saves to DB):

@Test
public void batchInsert() throws InterruptedException, SQLException {
    int batchCount = 0;
    List<YmsLinkDataRecord> batchRecords = Lists.newArrayList();
    for (YmsLinkDataRecord ld : ConfigurationToYmsLinkDataRecord.convert(
            config)) {
        batchCount++;
        batchRecords.add(ld);
        if (batchCount == 100) {
            ldService.create(batchRecords);
            batchRecords.clear();
            batchCount = 0;
        }
    }
    ldService.create(batchRecords);
}

Any help would be greatly appreciated!

Answer 1

BoneCP has a rather interesting "feature" : if query fails with "fatal" error code pool will close ALL connections and become unusable. As far as I remember I had a similar issue when MySQL was failing with "HY00" error due to something like missing column

Relevant piece of code : https://github.com/wwadge/bonecp/blob/master/bonecp/src/main/java/com/jolbox/bonecp/ConnectionHandle.java#L182

It seems that "HY00" is no longer deemed fatatl in the latest version