About MySQL wait_timeout connection Timeout problem error resolution

Bug review:

Presumably everyone will encounter a connection timeout problem when using MySQL, as shown in:

# # # Cause:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:The last packet successfully received from the  Server was 47,795,922 milliseconds ago. The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j Connection property ' Autoreconnect=true ' to avoid this Problem.; SQL [];  The last packet successfully received from the server was 47,795,922 milliseconds ago. The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j connection property ' Autoreconnect=true ' to avoid this problem.; Nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:The last packet successfully received from  The server was 47,795,922 milliseconds ago. The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j Connection property ' Autoreconnect=true ' to avoid this Problem.

It probably means that the most recent request made by the current connection is 52,587 seconds ago, and this time is greater than the Wait_timeout time configured by the service.

Cause Analysis:

When MySQL connects, the server default "Wait_timeout" is 8 hours, which means that a connection idle for more than 8 hours, MySQL will automatically disconnect the connection. Connections if idle for more than 8 hours, MySQL disconnects it, and the DBCP connection pool does not know that the connection has failed, if there is a client request connection, DBCP The failed connection is provided to the client, which will cause an exception.

MySQL Analysis:

Open MySQL console, run: Show variables like '%timeout% ', view and connect time related MySQL system variables, get the following results:

Where Wait_timeout is responsible for time-out control of the variable, its length is 28800s, that is, 8 hours, then the MySQL service will be 8 hours after the operation interval disconnects, need to re-connect again. There are also users in the URL using Jdbc.url=jdbc:mysql://localhost:3306/nd?autoreconnect=true to make the connection automatically recover, of course, this is possible, but MySQL4 and the following version of the applicable. The MySQL5 has been invalidated and the system variable must be adjusted to control it. Two variables in the MYSQL5 manual are described below:

Interactive_timeout: The number of seconds that the server waits for activity before closing an interactive connection. The interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect (). See Wait_timeout again.
Wait_timeout: The number of seconds that the server waits for activity before closing a non-interactive connection. When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or global interactive_timeout value, depending on the client type (the connection option for the Mysql_real_connect () Client_ Interactive definition), see also Interactive_timeout
So it seems that two variables are in common control, so they must be modified. Continue to drill down into these two variables Wait_timeout range is 1-2147483 (Windows), 1-31536000 (Linux), interactive_time values with wait_timeout changes, Their default values are 28800.
The MySQL system variable is controlled by the configuration file, and when not configured in the configuration file, the system uses the default value, which is the default value of 28800. To be modified, it can only be modified in the configuration file. Under Windows under%mysql home%/bin there is a Mysql.ini configuration file, open after adding two variables in the following location, assign values. (modified here to 388000)

How to resolve:

1. Increase the value of the MySQL Wait_timeout property (not recommended)

Modify the configuration file My.ini file under the MySQL installation directory (if you do not have this file, copy the "My-default.ini" file and generate a "duplicate My-default.ini" file. Rename the "Copy My-default.ini" file to "My.ini" and set it in the file:

wait_timeout=31536000  interactive_timeout=31536000  

The default value for these two parameters is 8 hours (60*60*8=28800). Note: The maximum value of 1.wait_timeout is only allowed for 2147483 (24 days or so)

You can also use the MySQL command to modify these two properties

2. Reduce the lifetime of connections in the connection pool

Reduce the lifetime of connections within a connection pool to less than the wait_timeout value set in the previous item.

Modify the C3P0 configuration file and set it in the Spring configuration file:

<id= "DataSource"  class= " Com.mchange.v2.c3p0.ComboPooledDataSource ">          <name= "MaxIdleTime"value= "1800"/>      <!---    </bean>

3. Regular use of connections within the connection pool uses connections within the connection pool, so that they do not get disconnected by MySQL due to idle timeouts. Modify the C3P0 configuration file and set it in the Spring configuration file:

<BeanID= "DataSource"class= "Com.mchange.v2.c3p0.ComboPooledDataSource">      < Propertyname= "Preferredtestquery"value= "Select 1"/>      < Propertyname= "Idleconnectiontestperiod"value= "18000"/>      < Propertyname= "Testconnectiononcheckout"value= "true"/>  </Bean>

Attach standard configurations for DBCP and C3P0

<BeanID= "DataSource"class= "Org.apache.commons.dbcp.BasicDataSource"> < Propertyname= "Driverclassname"value= "Com.mysql.jdbc.Driver" /> < Propertyname= "url"value= "Jdbc:mysql://192.168.40.10:3336/xxx" /> < Propertyname= "username"value="" /> < Propertyname= "Password"value="" /> < Propertyname= "Maxwait"value= "20000"></ Property> < Propertyname= "Validationquery"value= "Select 1"></ Property> < Propertyname= "Testwhileidle"value= "true"></ Property> < Propertyname= "Testonborrow"value= "true"></ Property> < Propertyname= "Timebetweenevictionrunsmillis"value= "3600000"></ Property> < Propertyname= "Numtestsperevictionrun"value= " the"></ Property> < Propertyname= "Minevictableidletimemillis"value= "120000"></ Property> < Propertyname= "removeabandoned"value= "true"/> < Propertyname= "Removeabandonedtimeout"value= "6000000"/></Bean>

<BeanID= "DataSource"class= "Com.mchange.v2.c3p0.ComboPooledDataSource"Destroy-method= "Close">    　　 < Propertyname= "Driverclass"><value>Oracle.jdbc.driver.OracleDriver</value></ Property>    　　 < Propertyname= "Jdbcurl"><value>Jdbc:oracle:thin: @localhost: 1521:test</value></ Property>    　　 < Propertyname= "User"><value>Kay</value></ Property>    　　 < Propertyname= "Password"><value>Root</value></ Property>    　　 <!--the minimum number of connections that are kept in the connection pool.  -    　　 < Propertyname= "Minpoolsize"value= "Ten" />    　　 <!--the maximum number of connections that are kept in the connection pool. Default:15 -    　　 < Propertyname= "Maxpoolsize"value= "+" />    　　 <!--maximum idle time, unused in 1800 seconds, the connection is discarded. If 0, it will never be discarded. default:0 -    　　 < Propertyname= "MaxIdleTime"value= "1800" />    　　 <!--when the connection in the connection pool runs out, c3p0 the number of connections that are fetched at one time. Default:3 -    　　 < Propertyname= "Acquireincrement"value= "3" />    　　 < Propertyname= "Maxstatements"value= "+" />    　　 < Propertyname= "Initialpoolsize"value= "Ten" />    　　 <!--Check for idle connections in all connection pools every 60 seconds. default:0 -    　　 < Propertyname= "Idleconnectiontestperiod"value= "$" />    　　 <!--defines the number of repeated attempts to obtain a new connection from the database after a failure. Default:30 -    　　 < Propertyname= "Acquireretryattempts"value= "+" />   　　 < Propertyname= "Breakafteracquirefailure"value= "true" />    　　 < Propertyname= "Testconnectiononcheckout"value= "false" />   </Bean>   

Reference:

https://my.oschina.net/guanzhenxing/blog/213364

http://sarin.iteye.com/blog/580311/

http://blog.csdn.net/wangfayinn/article/details/24623575

About MySQL wait_timeout connection Timeout problem error resolution