Call Studio DB Element - Connection to DB getting closed Randomly

[ln33147]

Hello,

This regarding a CVP environment version 12.0
We have a Call Studio script that includes a database element.
Sometimes, the connection to the database is getting closed (from CVP to the database created under AW) as per the below error. Please find attached Tomcat logs and the issue gets solved after restarting CVP server. To note that both CVP and AW services are up and running.

The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. The connection is closed. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
                at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:416)
                at com.audium.server.controller.Controller.goToAction(Controller.java:4446)
                at com.audium.server.controller.Controller.goToElement(Controller.java:4082)
                at com.audium.server.controller.Controller.continueCall(Controller.java:3762)
                at com.audium.server.controller.Controller.doPost(Controller.java:1175)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
                at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
                at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:412)
                at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
                at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:754)
                at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1385)
                at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
                at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
                at java.lang.Thread.run(Thread.java:748)
Caused by: com.audium.server.AudiumException: The connection is closed.
                at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:367)
                at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:390)
                ... 27 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
                at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
                at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:390)
                at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:1885)
                at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:498)
                at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
                at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
                at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
                at com.sun.proxy.$Proxy55.setAutoCommit(Unknown Source)
                at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:359)

Has anyone faced a similar issue and can advise?

thanks in advance.

[Gerry O'Rourke]

Can you post the 'Resource' section contents of your context.xml file?

I created the below - to document some details which are useful to know when creating a CVP / Database JNDI setup
https://orourke.tv/web/doku.php?id=vendors:cisco:uc:cvp:jndi-cvp


A config like below - might resolve your issue:

   <Resource name="jdbc/cvp_database"
   maxWait="10000"
   maxActive="16"
   maxIdle="8"
   minIdle="4"
   auth="Container"
   type="javax.sql.DataSource"
   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
   removeAbandoned="true"
   removeAbandonedTimeout="300"
   testOnBorrow="true"
   validationQuery="SELECT 1"
   timeBetweenEvictionRunsMillis="300000"
   minEvictableIdleTimeMillis="1800000"
   url="jdbc:sqlserver://192.168.1.123:1433;databaseName=cvp_database;user=bob;password=Pa$$w0rd"
   />