VSM - Use Connection Pool in SQL Database connection step but losing DB connection

Products

• CA Application Test
• CA Continuous Application Insight (PathFinder)
• Service Virtualization

Issue/Introduction

In VSM for SQL Database Connect, using "keep open connection", but the model is throwing an error and then going offline until enabled the "Use connection pool" feature.

Still its unstable. Connection keep going offline. 

Environment

All supported DevTest releases.

Cause

Customer is using the same DB for Registry and for VSM 
Vse logs shows : 
 ERROR com.itko.lisa.ws.nx.NxWSStep   - Unable to invoke request: ; nested exception is:
      java.net.SocketTimeoutException: Read timed out
AxisFault
 faultCode: {http://schemas.xmlsoap.org/soap/envelope/}Server.userException
 faultSubcode:
 faultString: java.net.SocketTimeoutException: Read timed out
 faultActor:
 faultNode:
 faultDetail:
      {http://xml.apache.org/axis/}stackTrace:java.net.SocketTimeoutException: Read timed out
      at java.net.SocketInputStream.socketRead0(Native Method)
      at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
      at java.net.SocketInputStream.read(SocketInputStream.java:170)
      
       Communication failure detected when attempting to perform read query outside of a transaction. Attempting to retry query. Error was: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException  Internal Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 33,270,970 milliseconds ago.  The last packet sent successfully to the server was 33,270,970 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.  Error Code: 0  Call: SELECT ACL_USER_ID, MISC_INFO, USER_NAME, USER_PASSWORD, USER_UNIQUE_ID FROM ACL_USERS WHERE (LOWER(USER_UNIQUE_ID) = LOWER(?))              bind => [1 parameter bound]  Query: ReadAllQuery(referenceClass=DbUser sql="SELECT ACL_USER_ID, MISC_INFO, USER_NAME, USER_PASSWORD, USER_UNIQUE_ID FROM ACL_USERS WHERE (LOWER(USER_UNIQUE_ID) = LOWER(?))"). 
       

Resolution

Try the below options:

If using common pool for ACL

lisadb.acl.poolName=common

First option  is 

lisadb.pool.common.preferredTestQuery=SELECT 1
lisadb.pool.common.testConnectionOnCheckout=true

more c3p0 configuration can be found at https://www.mchange.com/projects/c3p0/#configuration

If the above doesn’t work then try

lisadb.pool.common.url=jdbc:mysql://DBHOST:DBPORT/DBNAME?autoReconnect=true