Druid version: 1.1.21
1. Problem description
Druid database connection pool Druid database connection pool
ERROR c.alibaba.druid.pool.DruidDataSource - discard connection
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 63,213,665 milliseconds ago. The last packet sent successfully to the server was 63,213,666 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
Copy the codeThe current connection made the last request 63,213 seconds ago, which is longer than the wait_timeout(default: 28,800 seconds) configured for the service.
2. Cause analysis,
Cause analysis:
When connecting to MySQL, the server default wait_timeout is 8 hours, which means that if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection. Connections If the connection has been idle for more than 8 hours, Mysql will disconnect it. The Druid pool does not know that the connection is invalid. If a Client requests a connection, Druid will provide the invalid connection to the Client. Will cause an exception.
There were two official issues on this issue:
(1) github.com/alibaba/dru…
This issue shows that the problem has already occurred in version 1.0.26. The official reply fixes the problem in version 1.0.27, but the comment reply shows the problem again in version 1.2.23.
(2) github.com/alibaba/dru…
This issue shows another error after version 1.1.1. The issue is in open state, and the official has not replied, so it is not clear whether the issue has been solved.
3. The solution
According to the analysis, there are several solutions.
(1) Version upgrade
(2) add autoReconnect = true
According to online data: stackoverflow.com/questions/9…
Using the Connector/J connection property ‘autoReconnect=true’ to avoid this problem.
The autoReconnect website explains as follows:
autoReconnect Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, Rather than the default of 8 hours. Default: false Since version: 1.1Copy the codeOther solutions: www.cnblogs.com/mracale/p/9…
MySQL > alter table wait_timeout = wait_timeout
Modify the configuration file my.ini in the mysql installation directory. If the file does not exist, copy the my-default.ini file to generate the copy my-default.ini file. Rename the “copy my-default.ini” file to “my.ini”) and set the following parameters in the file:
wait_timeout=31536000
interactive_timeout=31536000
Copy the codeThe default value for both parameters is 8 hours (60608=28800). Note: 1. The maximum wait_timeout is only allowed at 2147483 (about 24 days).
(4) Reduce the lifetime of connections in the connection pool
Reduce the lifetime of connections in the connection pool to less than the value of wait_timeout set in the previous item.
(5) Regularly use connections in the connection pool
Use connections in the connection pool regularly so that they are not disconnected by MySQL due to idle timeouts.
Appendix:
DruidDataSource configuration semantics.
| configuration | The default value | instructions |
| name | The significance of this attribute is that if multiple data sources exist, they can be distinguished by name when monitoring. If not, a name is generated in the form “DataSource-” + system.identityHashCode (this). In addition, setting this property does not work at least in version 1.0.5, forcing name will cause an error.For details – click here. | |
| url | The URL used to connect to a database varies from database to database. For example: mysql: JDBC: mysql: / / 10.20.153.104:3306 / druid2 oracle: JDBC: oracle: thin: @ 10.20.149.85:1521: ocnauto | |
| username | User name for connecting to the database | |
| password | Password for connecting to the database. If you don’t want your password written directly to the configuration file, use ConfigFilter.Look at this in detail | |
| driverClassName | Automatic identification by URL | Druid automatically identifies dbType by URL and selects the appropriate driverClassName |
| initialSize | 0 | Number of physical connections established during initialization. Initialization occurs when the display calls the init method, or the first getConnection |
| maxActive | 8 | Maximum number of connection pools |
| maxIdle | 8 | It’s no longer in use. It doesn’t work when configured |
| minIdle | Minimum number of connection pools | |
| maxWait | Gets the maximum wait time for a connection, in milliseconds. After maxWait is configured, fair lock is enabled by default, reducing the concurrency efficiency. If necessary, you can set useUnfairLock to true to use an unfair lock. | |
| poolPreparedStatements | false | Whether to cache preparedStatement, which is PSCache. PSCache provides a huge performance boost for databases that support cursors, such as Oracle. You are advised to disable this function in mysql. |
| maxPoolPreparedStatementPerConnectionSize | – 1 | To enable PSCache, the configuration must be greater than 0, when greater than 0, poolPreparedStatements automatically triggers a change to true. In Druid, there is no problem with Oracle PSCache taking up too much memory. You can set this value to a higher value, such as 100 |
| validationQuery | SQL to check whether a connection is valid, requires a query statement, usually select ‘x’. If validationQuery is null, testOnBorrow, testOnReturn, and testWhileIdle will not take effect. | |
| validationQueryTimeout | Unit: second: timeout period for checking whether the connection is valid. The underlying void setQueryTimeout(int seconds) method of the JDBC Statement object is called | |
| testOnBorrow | true | When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration degrades performance. |
| testOnReturn | false | ValidationQuery is executed when the connection is returned to verify that the connection is valid. This configuration degrades performance. |
| testWhileIdle | false | You are advised to set this parameter to true to ensure performance and security. Apply for connection, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is valid. |
| keepAlive | False (1.0.28) | Links within minIdle number of connection pool, free time more than minEvictableIdleTimeMillis, will perform the keepAlive operations. |
| timeBetweenEvictionRunsMillis | 1 minute (1.0.14) | Has two meanings: 1) the Destroy thread will detect the connection time interval, if the connection is idle time is greater than or equal to minEvictableIdleTimeMillis close physical connection. 2) testWhileIdle determines the value of the whileIDLE attribute |
| numTestsPerEvictionRun | 30 mins (1.0.14) | No longer used, a DruidDataSource supports only one EvictionRun |
| minEvictableIdleTimeMillis | Minimum time for a connection to remain idle without being expelled | |
| connectionInitSqls | SQL executed when the physical connection is initialized | |
| exceptionSorter | Automatic identification by dbType | When the database throws some unrecoverable exception, the connection is discarded |
| filters | The attribute type is a string. Extensions are configured using aliases. Common plug-ins include: Filter :stat for monitoring statistics Filter :log4j for defending against SQL injection Filter: Wall | |
| proxyFilters | Type is the List < com. Alibaba. Druid. Filter. The filter >, if the configuration of the filters and proxyFilters at the same time, is the combination relations, is not to replace |
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <! <property name="url" value="${jdbc_url}" /> <property name="username" value="${jdbc_user}" /> <property name="password" value="${jdbc_password}" /> <property name="filters" value="stat" /> <property name="maxActive" value="20" /> <property name="initialSize" value="1" /> <property name="minIdle" value="1" /> <! <property name="maxWait" value="60000" /> <! -- How often does the configuration check for idle connections that need to be closed, Unit is milliseconds - > < property name = "timeBetweenEvictionRunsMillis" value = "60000" / > < property name = "minEvictableIdleTimeMillis" value="300000" /> <property name="testWhileIdle" value="true" /> <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <property name="poolPreparedStatements" value="true" /> <property name="maxOpenPreparedStatements" value="20" /> <property name="asyncInit" value="true" /> </bean>Copy the code