com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Symptom

I was making my decision to use 5.0.51 version of MySQL instead of using Oracle 10g in my project. I applied it to my application and then, to verify connection, I ran my web Tomcat server. The server worked well until about 5 minutes later. After that, I received the exception like this:

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 353,678,680 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago. ### The error may exist in sql/cuMonitorSQL.xml ### The error may involve cuMonitor.selectCurrentCuInfo ### The error occurred while executing a query ### SQL:  ### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 353,678,680 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago. ; SQL []; Communications link failure

<Content1> Exception message from console>

<Picture1> Exception message from web page

Root Cause

MySQL has Default Timeout value. That is, once the connection created and if the connection hasn't ever been used during that time out period, MySQL considers this connection as disconnected one. So it closes the connection by itself. Even though it was closed, DataSource in the DBCP never knows whether it is disconnected or not. So DataSource returns disconnected one when responding to Connection request. That's why problem occurred.

Solution

The core breakthrough is making DBCP knows the connection condition. Thus, I included 3 things in the DBCP configuration(DataSource, jdbc.properties):

1. add 'validationQuery = "SELECT 1"'

2. add 'testOnBorrow = "true"'

3. add 'autoReconnect = "true"' to at the end of JDBC URL

autoReconnect: If there is any connection problem, Reconnect to MySQL.

validationQuery: Before DBCP returns the connection to Application, it checks whether connection is still available by executing "SELECT 1" query.

testOnBorrow: Before DBCP returns the connection to Application, DBCP checks the connection.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"   >         <property name="driverClassName" value="${jdbc.driverClassName}" />         <property name="url" value="${jdbc.url}" />         <property name="username" value="${jdbc.username}"/>         <property name="password" value="${jdbc.password}"/>         <property name="initialSize" value="${jdbc.initialSize}"/>         <property name="maxActive" value="${jdbc.maxActive}"/>         <property name="validationQuery"                     value="${jdbc.validationQuery}"/>         <property name="testOnBorrow"                    value="${jdbc.testOnBorrow}"/>         </bean>

<Content2> Modified "Commonservice.xml" file

jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://xxx.xxx.xx.xxx/xxxx?autoReconnect=true jdbc.username=xxxx jdbc.password=xxxxxx jdbc.maxActive=10 jdbc.initialSize=5 jdbc.validationQuery=SELECT 1 jdbc.testOnBorrow=true

<Content3> Modified "jdbc.properties" file

[Oracle] Install Oracle 11g

[INS-13001] Oracle Database is not supported on this operating system solution

ORA-28000: the account is locked solution

[Java] Install JDK and configure Environment variable

[Java] Install Eclipse IDE and run HelloWorld java app