MySQL connection validity test in datasource : SELECT 1 or something better?

Question

I'm setting up a failover cluster on MySQL, in a master/slave architecture. I'm also configuring my JBoss Datasource, and I'm looking for the better way to test my connection, knowing that it is for Alfresco (which is using Ibatis).

Even I praticed MySQL many times, I don't know very well internals mechanisms of execution in MySQL Server.

So far, I'm using this query to test my SQL connection (like in this thread : Database Fail Over in Jboss Data sources)

SELECT 1;

Here the full datasource.

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
    <jndi-name>alfresco-datasource</jndi-name>
    <connection-url>
        jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco
    </connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>alfresco</user-name>
    <password>alfresco</password>
    <exception-sorter-class-name>
        org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
    </exception-sorter-class-name>


    <connection-property name="readOnly">false</connection-property>
    <failOverReadOnly>false</failOverReadOnly>

    <!-- Automatic reconnecion - desactivated to preserve transactions  -->
    <!-- http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html :
    Failover happens when the driver determines that the connection has failed (checked before every query), and falls back to the first host when it determines that the host has become available again (after queriesBeforeRetryMaster queries have been issued). -->
    <!--<autoReconnect>true</autoReconnect>-->

    <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    <valid-connection-checker-class-name>
        org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
    </valid-connection-checker-class-name>


    <!-- If you're using Connector/J 3.1.8 or newer, you can use our implementation 
        of these to increase the robustness "mysql-ds.xml" 64L, 3683C of the connection 
        pool. -->
    <exception-sorter-class-name>
        com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
    </exception-sorter-class-name>
    <valid-connection-checker-class-name>
        com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
    </valid-connection-checker-class-name>

    <metadata>
        <type-mapping>mySQL</type-mapping>
    </metadata>
</local-tx-datasource>

</datasources>

I have multiple question that I'm not able to answer by myself:

• Won't this simple query be cached by Ibatis (or any kind or ORM) ? This would mean that it could return me false results.
• Won't that query be too simple ? Is it really trying to execute internal mechanisms that would be representative of server health ? Or will it test only connection ?
• Is it really a reliable test ?
• Is there any other kind of test (already integrated with Connector/J for instance) ?
• Performance is also important for me, so is SELECT 1 is a good compromise between health check and performance

Don't hesitate to point me out some links (inside Stackoverflow or not). If this question have bean already answered (it seems not, as far as I searched), I will obviously delete this thread.

I would much appreciate returns of exeperience of mysql developpers or administrators. I'm looking for the best way to do it.

Thanks for your help.

Answer 1

Quoting this link: Ping MySQL Server Using JDBC

you have to:

The MySQL JDBC driver (Connector/J) provides a ping mechanism.

If you do a SQL query prepended with /* ping */ such as:

"/* ping */ SELECT 1" This will actually cause the driver send a ping to the server and return a fake, light-weight, result set.

(You can find this buried fairly deep in the Connector/J documentation; search for "ping" on that page. Read it carefully: this mechanism is very sensitive to the syntax used. Unlike most SQL, the "parsing" of the "ping" marker happens in the client-side JDBC driver itself.).

Answer 2

The query we use at work to check the connection to a MySQL server is SHOW databases; which is nice and simple as it doesn't need you to have selected a particular database. I'd say it's pretty reliable and a query like that is pretty efficient.