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.

[bill.king1]

Have you tried it at all without the 'connection-creation-retry-attempts="5"' section to see if you see any improvement/difference of opinion?

Also, what version of CVP is this?

[janinegraves · Accepted solution]

I'm surprised that TAC didn't find this problem, because one of my
former students had this issue many years ago and told me that the fix
was to remove the attribute named factory - it causes VXMLServer to NOT
re-connect after it loses connection to SQL.

I have this in my student guide:
*After 11.5: Do NOT use this factory method, as it causes VXML Server
not to reconnect if the SQL is restarted
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"*

[bill.king1]

Hi @janinegraves , I'm guessing this is the defect you're mentioning?

https://bst.cloudapps.cisco.com/bugsearch/bug/CSCvo75092

[chad_meyer]

Thank you both for the reply.  @bill.king1 I originally had that entry removed and added it to test.  @janinegraves We are running CVP v11.6 currently and assumed your note was for v12+.  I will remove that entry and give it another try.  

[janinegraves]

Be sure you restart VXML Server after making any changes in context.xml.

[chad_meyer]

Closing the gap on this, Janine that worked. Turns out since I setup the SQL database as an availability group for redundancy, when the primary would failover, at times would cause the disconnect to the database.  Removing the line from the context file worked.  Thanks!

[Ahmed Adeyemi]

Hello Chad, do you mind sharing an example of your XML file because I am getting an error that's strange for mine? Although I have multiple database types, the one I am truly trying to get to work is the MS SQL but i am getting the error "INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified."

[chad_meyer]

Ahmed,

Can you please share your xml file with me and I can take a look.  Also let me know which version of SQL/CVP you are using.

[Ahmed Adeyemi]

I am using CVP 12.6 with SQL 2016. Attached below is my context.xml file zipped.

[chad_meyer]

Ahmed,  I assume you are seeing this as a java error in the application error logs?  Tomcat doesn't like special characters so I am going on a limb here and saying the "@" in your password could potentially be hanging you up. You can either try escaping the character or omitting it entirely.  I propose the latter to ensure connectivity.

[Ahmed Adeyemi]

Thank you Chad. I had also tried using a simple password as "password" and i am getting the same error.

[janinegraves]

Ahmed,
I removed the white space from my context.xml and replaced it with line-breaks (like yours) and got the same error as you 'invalid character in XML'.
So, I'm pretty sure that if you add a space between each attribute, like this - it'll fix your problem.

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"/>

[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.