DevTest 10.3 VSM -Use Connection Pool in SQL Database connection step looses DB connection

Document ID : KB000119995
Last Modified Date : 06/11/2018
Show Technical Document Details
Issue:
When I was using Devtest version 9.2. in VSM for SQL Database Connect I was using "keep open connection" But when migrated to 10.3 the Model was throwing an error and then going offline until I enabled the "Use connection pool" feature. Still its unstable. Connection keep going offline. 
Environment:
DevTest 10.3 and up
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 you are 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 they can try

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