数据源中的MySQL连接有效性测试：SELECT 1或更好的东西？ [英] MySQL connection validity test in datasource : SELECT 1 or something better?
问题描述
我在MySQL中设置一个主/从架构的故障转移群集。
我也在配置我的JBoss数据源，我正在寻找更好的方法来测试我的连接，知道它是Alfresco（使用Ibatis）。
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).
即使我多次尝试MySQL，我也不知道MySQL服务器中执行的内部机制。
Even I praticed MySQL many times, I don't know very well internals mechanisms of execution in MySQL Server.
到目前为止，我使用这个查询以测试我的SQL连接（类似于此主题： Jboss数据库中的数据库故障转移来源）
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;
这里是完整的数据源。
<?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:
- 这个简单的查询不会被Ibatis（或任何类型或ORM）缓存？这将意味着它可能返回我错误的结果。
- 这个查询不会太简单吗？它真的试图执行代表服务器健康的内部机制吗？
- 是否有任何其他类型的测试（已经集成了连接器/ J例如）
- 性能对我也很重要，所以SELECT 1是健康检查和性能之间的一个很好的折中。
- 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
不要犹豫，指出一些链接（在Stackoverflow或不是）。如果这个问题有bean已经回答（似乎不，就我的搜索），我会显然删除此线程。
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.
我会非常感谢的回报mysql的exeperience开发人员或管理员。我正在寻找最好的方法。
I would much appreciate returns of exeperience of mysql developpers or administrators. I'm looking for the best way to do it.
感谢您的帮助。
推荐答案
引用此链接：使用JDBC Ping MySQL服务器
您必须：
MySQL JDBC驱动程式（Connector / J）提供ping机制。
The MySQL JDBC driver (Connector/J) provides a ping mechanism.
如果你用/ * ping * /前面加一个SQL查询，例如：
If you do a SQL query prepended with /* ping */ such as:
/ * ping * / SELECT 1这将导致驱动程序发送一个ping
到服务器，并返回一个假的，轻量级的结果集。
"/* ping */ SELECT 1" This will actually cause the driver send a ping to the server and return a fake, light-weight, result set.
（你可以找到这个埋没深入在连接器/ J
文档中;在该页面上搜索ping。仔细阅读：
机制对所使用的语法非常敏感。与大多数SQL不同，
的ping标记发生在客户端JDBC驱动程序
本身。）。
(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.).
这篇关于数据源中的MySQL连接有效性测试：SELECT 1或更好的东西？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
