title: MySQL connection validity test in datasource : SELECT 1 or something better?
body: <p>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).</p>
  
  <p>Even I praticed MySQL many times, I don't know very well internals mechanisms of execution in MySQL Server.</p>
  
  <p>So far, I'm using this query to test my SQL connection (like in this thread : <a href="https://stackoverflow.com/questions/9409111/database-fail-over-in-jboss-data-sources">Database Fail Over in Jboss Data sources</a>)</p>
  
  
  
  <pre><code>SELECT 1;
  </code></pre>
  
  <p>Here the full datasource.</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;datasources&gt;
  &lt;local-tx-datasource&gt;
      &lt;jndi-name&gt;alfresco-datasource&lt;/jndi-name&gt;
      &lt;connection-url&gt;
          jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco
      &lt;/connection-url&gt;
      &lt;driver-class&gt;com.mysql.jdbc.Driver&lt;/driver-class&gt;
      &lt;user-name&gt;alfresco&lt;/user-name&gt;
      &lt;password&gt;alfresco&lt;/password&gt;
      &lt;exception-sorter-class-name&gt;
          org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
      &lt;/exception-sorter-class-name&gt;
  
  
      &lt;connection-property name="readOnly"&gt;false&lt;/connection-property&gt;
      &lt;failOverReadOnly&gt;false&lt;/failOverReadOnly&gt;
  
      &lt;!-- Automatic reconnecion - desactivated to preserve transactions  --&gt;
      &lt;!-- 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). --&gt;
      &lt;!--&lt;autoReconnect&gt;true&lt;/autoReconnect&gt;--&gt;
  
      &lt;check-valid-connection-sql&gt;SELECT 1&lt;/check-valid-connection-sql&gt;
      &lt;valid-connection-checker-class-name&gt;
          org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
      &lt;/valid-connection-checker-class-name&gt;
  
  
      &lt;!-- 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. --&gt;
      &lt;exception-sorter-class-name&gt;
          com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
      &lt;/exception-sorter-class-name&gt;
      &lt;valid-connection-checker-class-name&gt;
          com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
      &lt;/valid-connection-checker-class-name&gt;
  
      &lt;metadata&gt;
          &lt;type-mapping&gt;mySQL&lt;/type-mapping&gt;
      &lt;/metadata&gt;
  &lt;/local-tx-datasource&gt;
  
  &lt;/datasources&gt;
  </code></pre>
  
  <p>I have multiple question that I'm not able to answer by myself: </p>
  
  <ul>
  <li>Won't this simple query be cached by Ibatis (or any kind or ORM) ? This would mean that it could return me false results.</li>
  <li>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 ?</li>
  <li>Is it really a reliable test ?</li>
  <li>Is there any other kind of test (already integrated with Connector/J for instance) ?</li>
  <li>Performance is also important for me, so is SELECT 1 is a good compromise between health check and performance</li>
  </ul>
  
  <p>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.</p>
  
  <p>I would much appreciate returns of exeperience of mysql developpers or administrators. I'm looking for the best way to do it.</p>
  
  <p>Thanks for your help.</p>