Geek Answers Handbook

Outdated connections, validationQuery does not fix

I get terrible MySQL JDBC legacy connection errors:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 243,263,541 milliseconds ago.  The last packet sent successfully to the server was 243,263,541 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.

Everyone seems to agree that this is fixed with validationQuery + testOnBorrow, but this does not solve the problem.

I am using the following MySQL software 5.1.41-3ubuntu12.10 Connector / J 5.1.18 Tomcat 6.0.24

Here is how the connection is defined in server.xml, we use tomcat-dbcp to join the connections.

 <Resource
       auth="Container"
       driverClassName="com.mysql.jdbc.Driver"
       factory="org.apache.commons.dbcp.BasicDataSourceFactory"
       logAbandoned="true"
       maxActive="75"
       maxIdle="20"
       maxWait="10000"
       name="jdbc/jndiname"
       password="password"
       removeAbandoned="true"
       removeAbandonedTimeout="60"
       validationQuery="/* ping */SELECT 1"
       testOnBorrow="true"
       testOnReturn="true"
       timeBetweenEvictionRunsMillis="10000"
       testWhileIdle="true"
       scope="Shareable"
       type="javax.sql.DataSource"
       url="jdbc:mysql://host:3306/schema"
       username="username" />
+5
source share
3 answers

I can check wait_timeout in my.cnf file. The default value is 28800 seconds or 8 hours. It has a maximum of 31536000 seconds or 365 days.

: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException, try/catch. catch, , , . , , wait_timeout - .

. : http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout

0

. "select 1" .

0

my.cnf mysql , 365 -

wait_timeout = 31536000
interactive_timeout = 31536000

The session value wait_timeoutwill be initialized with a global value wait_timeoutfor non-interactive connections and with a global value interactive_timeoutfor interactive connections.

PS - Both values ​​are in seconds.

0
source

More articles:


All Articles