CallStudio DB (with failover) custom element - time out

[ruzeljamilon]

Rebuild and tested - March 19
Cutover - March 25

19 -

hello. we have a custom DB element that accepts to jndiNames for DB failover.
We tested this two ways and the second scenario is causing the IVR to crash and calls just drops.

First scenario - in our context.xml, i purposely put in an unreachable IP Add - and then it goes over and connects to the back up DB succesfully.
Second scenario - I shut down the DB server, and then this is where the call crashes.

This is the code for DB retry:

  try{
     conn =ds.getConnection();
    }catch (Exception E){
    actionData.addToLog("Primary DB fail.Succesfully closed primary connection", jndiName);
    actionData.addToLog("Getting failover connection", jndiNameBkup);
    }finally{
    
    }
   
   if (conn == null) { 
    close(conn);
    ds = getDataSource(jndiNameBkup);
   
    try{
     conn = ds.getConnection();
    }catch (Exception E){
     actionData.addToLog("Failover DB failed to connect", jndiNameBkup);
     }finally{
     
     }
   
    if (conn == null){
     throw new AudiumException("Unable to acquire free connection from pool.");
    }else{
     actionData.addToLog("Connected to failover DB", jndiNameBkup);
    }
   }else{
    actionData.addToLog("Connected to DB", jndiName);
   }
  
And this is the log we get:
DB_Validate_PIN,enter,
DB_Validate_PIN,custom,Primary DB fail.Succesfully closed primary connection,UCServices
DB_Validate_PIN,custom,Getting failover connection,UCServices_B
DB_Validate_PIN,custom,Connected to failover DB,UCServices_B
DB_Validate_PIN,interaction,audio_group,initial_audio_group
DB_Validate_PIN,interaction,utterance,1231231234
DB_Validate_PIN,interaction,inputmode,dtmf
DB_Validate_PIN,interaction,interpretation,1231231234
DB_Validate_PIN,interaction,confidence,1
DB_Validate_PIN,element,error,error.badfetch
Application_Error,enter,
Application_Error,interaction,audio_group,initial_audio_group
Application_Error,exit,done

I printed out some more logs to trace and its okay up until this code:
  if (type.equals("single")){
    actionData.addToLog("Test1", test);
    ps = conn.prepareStatement(query);
    actionData.addToLog("Test2", test);
    rs = ps.executeQuery();
    actionData.addToLog("Test3", test); --> did not go out the log.
   
I also got this in the error log:
10.1.253.197.1393539161593.0.SD_Dealer_IVR,02/27/2014 15:14:47.469,An error was encountered while attempting to log an event with the ID "elementExit". The error was: java.lang.IllegalStateException: getAttribute: Session already invalidated The error was: java.lang.IllegalStateException: getAttribute: Session already invalidated
java.lang.IllegalStateException: getAttribute: Session already invalidated
at org.apache.catalina.session.StandardSession.getAttribute(StandardSession.java:1073)
at org.apache.catalina.session.StandardSessionFacade.getAttribute(StandardSessionFacade.java:110)
at com.audium.server.session.ControllerData.removeLoggerScratchData(ControllerData.java:3964)
at com.audium.server.session.ControllerData.setLoggerScratchData(ControllerData.java:3944)
at com.audium.server.session.LoggerAPI.setLoggerScratchData(LoggerAPI.java:127)
at com.audium.logger.application.activity.ActivityLogger.doElementExit(ActivityLogger.java:500)
at com.audium.logger.application.activity.ActivityLogger.log(ActivityLogger.java:139)
at com.audium.server.logger.ApplicationLoggerBase.runLogger(ApplicationLoggerBase.java:163)
at com.audium.server.logger.LoggerManager$LoggerRunner.run(LoggerManager.java:66)
at com.audium.server.util.ThreadPool$Worker.run(ThreadPool.java:757)
at java.lang.Thread.run(Thread.java:662)

Appreciate any inputs and suggestions, Thanks!

Ruzel

[hemal.mehta]

What is your session timeout ?

[ruzeljamilon]

Thank you Hemal.

Originally I had loginTimeout=5 in my jdbc URL:

url="jdbc:sqlserver://XXXXXXXXX.XXX.LAB;databaseName=UCServices;user=XXXX;password=XXXX;loginTimeout=5;socketTimeout=3000; connectTimeout=3000;"

I have since revised it to include socket and connecTimeout, but still having the same error:

url="jdbc:sqlserver://XXXXXXXXX.XXX.LAB;databaseName=UCServices;user=XXXX;password=XXXX;loginTimeout=5;socketTimeout=3000; connectTimeout=3000;"

[hemal.mehta]

What is your session timeout for the app ?

Hemal

[janinegraves]

By Session Timeout, I assume Hemal means in the Studio project,

Right-click and select Properties. Then go to CallStudio/General tab.

What is your Session Timeout?

[ruzeljamilon]

Thank you - didnt know about the session timeout.

Session timeout is set to 30minutes.

The call goes to error after a minute or so.

[hemal.mehta]

You need to reduce that to 3 mins or so.

Hemal

[ruzeljamilon]

Thanks Hemal. WIll do that. Can I ask, how this session timeout affects the app? Sorry, I know I can look that up online later but sometimes you guys explain it better for someone to my level than the PDFs online.

Hi Janine, I have tried setLoginTimeout but getting exception

DB_Validate_PIN,custom,Exception,Not supported by BasicDataSource

-Will look more into this. But if you have any pointers for me, that will really be helpful.

Also, with this testing, I have found out that the element is failing as well even if its not waiting as long for the connection. Its doing the exact error - but this time, just dropiing the call and not exiting gracefully out of IVR.

Also, I switched DB in one of our VXML Server with UAT ongoing. I forced the app to look into the backup DB first, without failover as I am doing the testing on another server. And I noticed that - without failover - its still doing the same thing. THis might be a DB issue - I would say - and looking more into this now. Any inputs?

[hemal.mehta]

You do not want a session to be hanging around for 30 mins, in case the app were to get aborted or have some errors.

Hemal

[janinegraves]

Session Timeout is a VXML Server inactivity timer.

Like if you log into your bank online and then walk away, the bank logs

you out after a minute of inactivity.

When VXML Server sends a page to the gateway (any element with a square

on it, Audio, Digits, Currency, Menu, Form, Subdialog Invoke, etc) the

gateway has that long to execute it and return with a response. If it

expires, VXML Server frees the license (assuming the call's gone away).

I don't see how that affects your DB problem.

[janinegraves]

Hemal,

That's true (and should be a rule of thumb to *always *shorten the

Session Timeout to

just a few minutes), but it won't help with his VXML Server not

returning from the 'down' database any sooner than 1 minute.

*Hemal, isn't there a way to configure VxmlServer/Tomcat to timeout on

the DB query after just a few seconds?

*One (ugly) workaround would be to shorten the fetchtimeout VoiceXML

Property in the Audio element just before his custom DB element (to

say, 5s) then the gateway will throw error.badfetch after 5 seconds and

he could catch that error.badfetch in a HotEvent element, and assume it

means the primary DB is down, and then try the secondary DB.

[hemal.mehta]

Not saying that it is affecting the DB issue.  What is happening here is that the backend is getting aborted leaving a hanging state.  So you need to make sure it is not hanging for 30 mins. Also  need to  have the code catch this error and exit gracefully.   Just briefly looked at the code.  Why are you having this:

if (conn == null) { 
    close(conn);

Why will you close if connection is null ?  What is there to close and you are passing null object to close

[ruzeljamilon]

@Hemal- A misconception on my part. I thought that once we try to open connection, but failed, i thought it'd be cleaner if I close it before I try to open another one again.

Update on the Issue - the DB element is not timing out on trying to connect to the down primary DB. But its timing out on the stored proc which is running in the secondary DB. The stored proc is actually trying to connect to the primary DB for replication purposes. We are now correcting this one up, since the same thing will happen if Primary is up and Secondary is down.

I am looking up on how to set timeout for ps.executeQuery since there is where the timeout is actually happening. 

[janinegraves]

The DriverManager class in java has a way to set a timeout. I think the problem is that Tomcat is waiting too long for a connection to the down system. In the meantime, your VXML Gateway is timing out waiting for vxml server to send it something to continue interacting with the caller, so you're getting error.badfetch

static void

setLoginTimeout(int seconds)             Sets the maximum time in seconds that a driver will wait while attempting to connect to a database.

[ruzeljamilon]

Thanks Janine, I tried including that in the code but its not doing anything so i maybe missing something. Will read more on that today!.