JDBC connection with Shiny - Crashes frequently

[Arun]

I am connecting to Teradata from R and executing some queries. This is perfectly working on R Studio. I moved this to shiny server for other users to access the tool. 

The first time folks use the tool its working perfectly fine - connecting well and giving out results. Once the folks close the tool and open it like 10-12 hours after that, the queries are taking really long time and failing with error below. I opened up the console of chrome to see the logs and below is the content of the log:

/***Start****/

The application unexpectedly exited 

diagnostic information has been dumped to javascript console

/***End*****/

After i again login to the my machine and copy all the files from source to /srv/shiny-server, the tool is working fine and same error repeats after some time.

I am assuming this is happening as the previous connection is not getting closed clearly.

Anyone facing such issue?

 Appreciate your help

/*************************Start************************/

  Unhandled error in observer: Unable to retrieve JDBC result set for select

  SITE,

  CHNL,

  pool   ,

  SUM(cnt) as cnt

  FROM

  abc.table_name

  where run_dt = '2016-02-29'

  group by 1,2,3

  order by 4 desc; ([Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1000] [SQLState 08S01] Login failure for Packet transmit Tue Mar 08 18:53:28 UTC 2016 socket orig=server.xxx.xxx.com local=0.0.0.0/0.0.0.0:46982 remote=server.xxx.xxx.com/10.102.20.23:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=11 connecttotaltime=11 connectattempts=1 connectfailures=0 failurecache={} cid=540984b sess=39053461 java.net.SocketException: Socket closed  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:116)  at java.net.SocketOutputStream.write(SocketOutputStream.java:153)  at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.write(TD [... truncated]

Warning in run(timeoutMs) :

  

Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: Unable to retrieve JDBC result set for select

  SITE,

  CHNL,

  pool   ,

  SUM(cnt) as cnt

  FROM

  abc.table_name

  where run_dt = '2016-02-29' and id = 123

  group by 1,2,3

  order by 4 desc; ([Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1000] [SQLState 08S01] Login failure for Packet transmit Tue Mar 08 21:37:37 UTC 2016 socket orig=server.xxx.xxx.com local=0.0.0.0/0.0.0.0:35998 remote=server.xxx.xxx.com/10.102.20.17:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=10 connecttotaltime=11 connectattempts=1 connectfailures=0 failurecache={} cid=6d79953c sess=39059509 java.net.SocketException: Socket closed  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:116)  at java.net.SocketOutputStream.write(SocketOutputStream.java:153)  at com.teradata [... truncated]

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtonb)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: Unable to retrieve JDBC result set for select

  SITE,

  CHNL,

  pool   ,

  SUM(cnt) as cnt

  FROM

  abc.table_name

  where run_dt = '2016-02-29'

  group by 1,2,3

  order by 4 desc; ([Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1000] [SQLState 08S01] Login failure for Packet transmit Wed Mar 09 05:26:06 UTC 2016 socket orig=server.xxx.xxx.com local=0.0.0.0/0.0.0.0:42840 remote=server.xxx.xxx.com/10.102.20.13:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=9 connecttotaltime=9 connectattempts=1 connectfailures=0 failurecache={} cid=2e716cb7 sess=39064275 java.net.SocketException: Socket closed  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:116)  at java.net.SocketOutputStream.write(SocketOutputStream.java:153)  at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.write(TDN [... truncated]

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtona)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$gopools)

Warning in run(timeoutMs) :

  Unhandled error in observer: Unable to retrieve JDBC result set for select

  SITE,

  CHNL,

  pool   ,

  SUM(cnt) as cnt

  FROM

  abc.table_name

  where run_dt = '2016-02-29' and id = 123

  group by 1,2,3

  order by 4 desc; ([Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1000] [SQLState 08S01] Login failure for Packet transmit Thu Mar 10 23:44:51 UTC 2016 socket orig=server.xxx.xxx.com local=0.0.0.0/0.0.0.0:34081 remote=server.xxx.xxx.com/10.102.20.15:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=8 connecttotaltime=10 connectattempts=1 connectfailures=0 failurecache={} cid=62c2ee15 sess=39117088 java.net.SocketException: Socket closed  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:116)  at java.net.SocketOutputStream.write(SocketOutputStream.java:153)  at com.teradata. [... truncated]

Warning in run(timeoutMs) :

  Unhandled error in observer: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

observeEvent(input$goButtons)

shiny-server.js:238 Fri Mar 11 2016 10:09:15 GMT-0800 (PST): Connection closed. Info: {"type":"close","code":1000,"reason":"Normal closure","wasClean":true}

/*******************End************************/

[Mike Stanley]

The Teradata JDBC driver is up to version 15.10.00.14 so you may find upgrading helps.

Are you using the RJDBC package, or have you written your own interface? Perhaps if you showed some of your code we'd be able to test some more.

[Joe Cheng]

"Cannot call a method on closed connection" looks suspicious... Mike is right, seeing your code would help.

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/shiny-discuss/a9b500ff-ddfe-4dda-b23d-da1ca3e00a49%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Arun]

Thanks Mike/Joe.

We are using a customized library within our company to connect to TD from R. Can you please let me know if you want to share my code or the library code as that is used for the connections. 

shiny-server.js:224 Warning in run(timeoutMs) :

  Unhandled error in observer: Unable to retrieve JDBC result set for select

  column1,

  column2 ,

  column3   ,

  SUM(column4) as cnt

  FROM

  app.xxxx

  where run_dt = '2016-03-08'

  group by 1,2,3

  order by 4 desc; ([Teradata JDBC Driver] [TeraJDBC 14.00.00.05] [Error 1000] [SQLState 08S01] Login failure for Packet transmit Fri Mar 11 23:57:06 UTC 2016 socket orig=server.xx.xxx.com local=0.0.0.0/0.0.0.0:39451 remote=server.xx.xxx.com/xx.xxx.xx.xx:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=9 connecttotaltime=11 connectattempts=1 connectfailures=0 failurecache={} cid=1395dd5b sess=39219988 java.net.SocketException: Socket closed  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:116)  at java.net.SocketOutputStream.write(SocketOutputStream.java:153)  at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.write(TD [... truncated]

Warning in run(timeoutMs) :

[Mike Stanley]

If you're using custom code, then it's highly likely that the problem lies there. There are lots of things about the implementation of the Teradata drivers that might be snagging you (though I'm not very familiar with the 14.xx series so I can't necessarily help you with specifics there). Without seeing any code it's going to be absolutely impossible, though. What exactly you're doing with the JVM, either via rJava or some other method; how exactly you're managing creating and destroying connections; lots of other things - could all affect this. It could also just be a simple case of the network being faulty and dropping your connection, or the Teradata server being configured in such a way that it's dropping or refusing your connection. There is also this known issue (among many others!) with the 14.xx series of drivers, so you should definitely upgrade if you can.

On Friday, March 11, 2016 at 8:04:23 PM UTC+1, Arun wrote: