Database connection timing out in CVP Application

[chad_meyer]

Hey everyone,

I have a CVP application that is doing a simple query per call to pull caller data.  The solution is working as expected, however after a week or so (not the same time frame every occurrance) it will start erroring out and I see the following in the app ErrorLog:

The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. Connection reset by peer: socket write error The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error

I amended the CVP application to manually point to its backup database server in the event of a failure to the primary database, however I get an error stating it cannot bind the jdbc connection, although it exists in the context.xml file.

The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. There was a problem looking up the JNDI data source 'gshbckp'. The root cause was: javax.naming.NameNotFoundException: Name [gshbckp] is not bound in this Context. Unable to find [gshbckp].
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.

Restarting VXML services corrects this problem.  I have reached out to TAC who is saying the issue is with the database while our database team is pointing to an application configuration issue.  Below is the context.xml file:

<Resource auth="Container"
	  name="jdbc/gsh"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://dbserver:1433;user=user;password=******;DatabaseName=DB"
	  connection-creation-retry-attempts="5"
	  type="javax.sql.DataSource"
/>
	 
<Resource auth="Container"
	  name="jdbc/gshbckp"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://dbserver2:1433;user=user;password=******;DatabaseName=DB"
	  connection-creation-retry-attempts="5"
	  type="javax.sql.DataSource"
/>

 I am connecting to SQL Server 2019 standard with the mssql-jdbc-6.4.0.jre7.jar file.

Any assistance would be greatly appreciated.

[janinegraves]

Correction, REMOVE the line breaks from within the  <Resource> tags and use spaces instead. 

[janinegraves]

Ahmed, I see a few odd things in the context.xml

1) In each <Resource> tag, I don't see any white space between the attributes, just line breaks. I'd add blank spaces  before the name of each attribute- such as this:
<Resource name="jdbc/Customer"  auth="Container"  type="javax.sql.DataSource" DriverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://onl-mssql.voicelab.local:1433;DatabaseName=Cisco;user=sa;password=@DBAadmin;encrypt=true;trustServerCertificate=true"/> 

2) I'm not sure if the attribute names are case-sensitive, but assume everything is case-sensitive, the attribute named driverClassName in your first <Resource> tag has an upper-case D at the beginning. 

3) Remember to restart VXMLServer after each change of the context.xml

4) If you're still getting an error, remove all the <Resource> tags that you're not using. Restart.

5) If you're still getting the error, remove the attributes ;encrypt=true;trustServerCertificate=true"  and see if you get a different error. I've never included those attributes - so it's possible something's wrong with one of them.

[Ahmed Adeyemi]

Thanks for your response janinegraves. I tried the follow as the only content in my XML file and i received the same error.

<!-- The contents of this file will be loaded for each web application -->
<Context>

<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Manager pathname=""/>
<Resource name="jdbc/Customer" auth="Container" type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://onl-mssql.voicelab.local:1433;DatabaseName=Cisco;user=sa;password=@DBAadmin"/>
</Context>

[janinegraves]

can you also try changing DatabaseName=Cisco to start with lower-case:  databaseName=Cisco
And are you restarting VXMLServer?
Are you sure the error is the same?

<Resource name="jdbc/Customer" auth="Container" type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://onl-mssql.voicelab.local:1433;databaseName=Cisco;user=sa;password=@DBAadmin"/>

[janinegraves]

Below is the context.xml file that works in my classroom lab, on my CVP 12.6 with sql server. Why don't you replace yours with this and modify it to use your db details. In case there's some weird unprintable character in your context.xml file. 

<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Manager pathname="" />
<Resource name="jdbc/customer_db" auth="Container" type="javax.sql.DataSource" username="user" password="pass" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://tteucce125.tte.lab:1433;databaseName=Customer_DB" /></Context>

[Ahmed Adeyemi]

I'm officially lost because I used the same parameter you provided and I am still seeing the error which the log file is uploaded below. I have a TAC case going as well and they have not been able to find anything yet other than the new Tomcat is different in the way it looks at the parameter unlike before.

<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Manager pathname="" />
<Resource name="jdbc/Customer" auth="Container" type="javax.sql.DataSource" username="sa" password="onelab" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://onl-mssql.voicelab.local:1433;databaseName=Cisco" /></Context>