Recently, I encountered an issue with Druid during activity check, unable to normally output select x. After resolving the issue, I came to share the implementation of Druid’s activity check mechanism.
1. What is Druid? And how does Druid implement connection activity checks?
Druid is an open-source JDBC component from Alibaba. It is a database connection pool that is very powerful for MySQL, supporting features like monitoring database access performance, database password encryption, SQL execution logs, and monitoring extensions. It’s quite favorable when applied to MySQL.
Through a simple analysis of the official source code (see reference), we learned that using Druid for connection checks involves adjusting the following two parameters:
|
Parameter |
Description |
|---|---|
|
druid.validationQuery = select 1 |
SQL used to detect if a connection is valid. Must be a query statement, usually select ‘x’. If validationQuery is null, testOnBorrow, testOnReturn, and testWhileIdle will not work. |
|
druid.mysql.usePingMethod = false |
Disables the MySQL com_ping activity check mechanism. Requires enabling validationQuery = select x to activate the validationQuery activity check mechanism. |
For explanations of other parameters, refer to the configuration property list: https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8
According to the source code’s information, Druid sequentially initializes and loads initValidConnectionChecker(); and validationQueryCheck(); In ValidConnectionChecker, the default com_ping is enabled, using com_ping as the default activity check. Let’s observe the outputs of both com_ping and validationquery respectively.
Let’s read further to see how these two parameters’ functions are implemented specifically.
2. Verification
In testing, we finalize the roles of these two activity check mechanism parameters. The version used here is Druid 1.2.5.
2.1. com_ping method requires verification through packet capture
By capturing packets through tcpdump, we obtained the network packet transmission within the Druid connection, then analyzed and viewed the Request packets sent by Druid to MySQL using Wireshark.
In the Ping (14) of the MySQL Protocol’s Request Command Ping.
2.2. validationquery method is verified through using MySQL general log
public MySqlValidConnectionChecker(){
try {
clazz = Utils.loadClass("com.mysql.jdbc.MySQLConnection");
if (clazz == null) {
clazz = Utils.loadClass("com.mysql.cj.jdbc.ConnectionImpl");
}
if (clazz != null) {
ping = clazz.getMethod("pingInternal", boolean.class, int.class);
}
if (ping != null) {
usePingMethod = true;
}
} catch (Exception e) {
LOG.warn("Cannot resolve com.mysql.jdbc.Connection.ping method. Will use 'SELECT 1' instead.", e);
}
// Note here it is acquired from the system variables System.getProperties().
configFromProperties(System.getProperties());
}
@Override
public void configFromProperties(Properties properties) {
// Acquired from system variables, so it should be added to the project startup script usePingMethod=false
String property = properties.getProperty("druid.mysql.usePingMethod");
if ("true".equals(property)) {
setUsePingMethod(true);
} else if ("false".equals(property)) {
setUsePingMethod(false);
}
}
public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
if (conn.isClosed()) {
return false;
}
if (usePingMethod) {
if (conn instanceof DruidPooledConnection) {
conn = ((DruidPooledConnection) conn).getConnection();
}
if (conn instanceof ConnectionProxy) {
conn = ((ConnectionProxy) conn).getRawObject();
}
// Whether the current conn is com.mysql.jdbc.MySQLConnection(or com.mysql.cj.jdbc.ConnectionImpl)
if (clazz.isAssignableFrom(conn.getClass())) {
if (validationQueryTimeout <= 0) {
validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
}
try {
// Use reflection to call MySQLConnection.pingInternal method to check connection validity, and will refresh the idle time of the connection. If it fails, an exception will be thrown, caught by the upper layer
ping.invoke(conn, true, validationQueryTimeout * 1000);
} catch (InvocationTargetException e) {
Throwable cause = e.getCause();
if (cause instanceof SQLException) {
throw (SQLException) cause;
}
throw e;
}
return true;
}
}
String query = validateQuery;
// When usePingMethod=false or conn is not com.mysql.jdbc.MySQLConnection (or com.mysql.cj.jdbc.ConnectionImpl), the following method will execute
if (validateQuery == null || validateQuery.isEmpty()) {
query = DEFAULT_VALIDATION_QUERY;
}
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
if (validationQueryTimeout > 0) {
stmt.setQueryTimeout(validationQueryTimeout);
}
// Execute select x query and refresh the idle time of the connection. If it fails, the exception will be caught by the upper layer
rs = stmt.executeQuery(query);
return true;
} finally {
JdbcUtils.close(rs);
JdbcUtils.close(stmt);
}
}
druid.validationQuery = SELECT 1 cannot directly use validationquery, it needs to disable com_ping (druid.mysql.usePingMethod = false) through configuration to implement it. This parameter can be directly added to the configuration file, but be cautious when using it. If the configuration disables com_ping and validationquery still cannot be used for activity check queries, it may be a problem with the program itself.
The program code may exist with the issue where parameter values only fetch parameter information from configFromPropety, causing the (druid.mysql.usePingMethod = false) parameter to be ineffective. Below is the connection diagram of my modified program:
// Original Program
public DruidDriverTest() {
logger = Logger.getLogger("druid_driver_test");
this.dataSource = new DruidDataSource();
// Druid configuration file path.
this.configPath = "./config.properties";
...
#############################################
// After modification
public DruidDriverTest() {
logger = Logger.getLogger("druid_driver_test");
// Druid configuration file path.
this.configPath = "config.properties";
try (BufferedReader bufferedReader = new BufferedReader(new FileReader(configPath))) {
// Load configuration file into system.config
System.getProperties().load(bufferedReader);
} catch (IOException e) {
e.printStackTrace();
return;
}
...
In the original program: Druid defaults to pulling configuration parameter information from the config file into DruidDataSource, but the usePingMethod parameter needs to be loaded and read into DruidDataSource using the MySqlValidConnectionChecker plugin, but config is not loaded into System.getProperties(), thus Druid cannot recognize the usePingMethod parameter in the config configuration file. Druid loads DruidDataSource configuration information for a series of behaviors.
After modification: Establish a connection to load the config configuration file into system variables, then use the MySqlValidConnectionChecker plugin to load into DruidDataSource.
[root@yang-02 druid_demo-master]# mvn exec:java -Dexec.mainClass="test.App"
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Building druid-demo 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- exec-maven-plugin:3.0.0:java (default-cli) @ druid-demo ---
[2021-04-28 17:23:06] [SEVERE] minEvictableIdleTimeMillis should be greater than 30000
[2021-04-28 17:23:06] [SEVERE] keepAliveBetweenTimeMillis should be greater than 30000
[2021-04-28 17:23:06] [INFO] start test
[2021-04-28 17:23:06] [INFO] ------------------ status --------------------
[2021-04-28 17:23:06] [INFO] initial size: 3
[2021-04-28 17:23:06] [INFO] min idle: 2
[2021-04-28 17:23:06] [INFO] max active: 20
[2021-04-28 17:23:06] [INFO] current active: 0
[2021-04-28 17:23:06] [INFO] max wait: 6000
[2021-04-28 17:23:06] [INFO] time between eviction runs millis: 2000
[2021-04-28 17:23:06] [INFO] validation query: SELECT 1
[2021-04-28 17:23:06] [INFO] keepAlive: true
[2021-04-28 17:23:06] [INFO] testWhileIdle: false
[2021-04-28 17:23:06] [INFO] testOnBorrow: false
[2021-04-28 17:23:06] [INFO] testOnReturn: false
[2021-04-28 17:23:06] [INFO] keepAliveBetweenTimeMillis: 4000
[2021-04-28 17:23:06] [INFO] MinEvictableIdleTimeMillis: 2000
[2021-04-28 17:23:06] [INFO] MaxEvictableIdleTimeMillis: 25200000
[2021-04-28 17:23:06] [INFO] RemoveAbandoned: false
[2021-04-28 17:23:06] [INFO] RemoveAbandonedTimeoutMillis: 300000
[2021-04-28 17:23:06] [INFO] RemoveAbandonedTimeout: 300
[2021-04-28 17:23:06] [INFO] LogAbandoned: false
// Observe Druid's query command output through enabling MySQL general log
// mysql general log output
2021-04-28T17:23:01.435944+08:00 7048 Connect [email protected] on druid_demo using TCP/IP
2021-04-28T17:23:01.441663+08:00 7048 Query /* mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 )
*/SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client,
@@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results,
@@character_set_server AS character_set_server,@@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout,
@@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet,
@@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size,
@@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone,
@@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2021-04-28T17:23:01.467362+08:00 7048 Query SHOW WARNINGS
2021-04-28T17:23:01.469893+08:00 7048 Query SET NAMES utf8mb4
2021-04-28T17:23:01.470325+08:00 7048 Query SET character_set_results = NULL
2021-04-28T17:23:01.470681+08:00 7048 Query SET autocommit=1
2021-04-28T17:23:01.580189+08:00 7048 Query SELECT 1
2021-04-28T17:23:01.584444+08:00 7048 Query select @@session.tx_read_only
2021-04-28T17:23:01.584964+08:00 7048 Query SELECT @@session.tx_isolation
...
2021-04-28T17:23:10.621839+08:00 7052 Quit
2021-04-28T17:23:12.623470+08:00 7051 Query SELECT 1
2021-04-28T17:23:12.624380+08:00 7053 Query SELECT 1
2021-04-28T17:23:14.625555+08:00 7053 Query SELECT 1
2021-04-28T17:23:14.626719+08:00 7051 Query SELECT 1
2021-04-28T17:23:16.627945+08:00 7051 Query SELECT 1
2021-04-28T17:23:16.628719+08:00 7053 Query SELECT 1
2021-04-28T17:23:18.629940+08:00 7053 Query SELECT 1
2021-04-28T17:23:18.630674+08:00 7051 Query SELECT 1