configuring MyBatis to use lightweight MySQL ping /* ping */

Question

Using the JDBC MySQL driver (v5.1.3 and up), it is possible to issue a "lightweight" ping instead of the provided SQL statement by prefixing with /* ping */.

For example: /* ping */ SELECT 1

I am trying to configure myBatis to use this, but it isn't working. I can confirm the original SQL is running instead of the lightweight ping by using xRebel (profiling tool) or by swapping SELECT 1 with a very slow statement.

Does anyone know why, or how to fix this?

From my copy of myBatisMapperConfig.xml

<environments default="dev">
    <environment id="dev">
        <transactionManager type="JDBC" />
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver" />
            <property name="url" value="${db.dev.url}" />
            <property name="username" value="${db.dev.username}" />
            <property name="password" value="${db.dev.password}" />
            <property name="poolPingQuery" value="/* ping */ SELECT 1" />
            <property name="poolPingEnabled" value="true" />
            <property name="poolMaximumActiveConnections" value="50" />
            <property name="poolMaximumIdleConnections" value="5" />
            <property name="poolMaximumCheckoutTime" value="10000" />
        </dataSource>
    </environment>

---

References explaining the lightweight ping:

• http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html
• MySQL connection validity test in datasource : SELECT 1 or something better?

References explaining pinging the connection in myBatis (search "poolPingQuery"):

• https://mybatis.github.io/mybatis-3/configuration.html

Answer 1

You probably already figured this out but you need to also set poolPingConnectionsNotUsedFor. The default value is 0 and even with poolPingEnabled it will not ping without a time (in millis).

The reference that you linked mentions it but it's not obvious that the default value of 0 means that it won't do the pings :/