Clarity PPM1

Expand all | Collapse all

Database Connection Issues

  • 1.  Database Connection Issues

    Posted 08-12-2015 04:50 AM

    Hi all,

     

    during the last few days we have noticed a strange issue in that the application appears to lose it's connection to the database. But then it re-establishes connection after a time.

     

    examples of the errors are as follows:

     

    FATAL 2015-08-12 01:24:30,727 [http-bio-8080-exec-7798] union.persistence (clarity:unknown:none:union.notFound) Failed to determine analytic function support

    java.lang.RuntimeException: Unexpected invocation exception: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

    FATAL 2015-08-12 01:24:30,726 [http-bio-8080-exec-7798] union.persistence (clarity:unknown:none:union.notFound)

    java.sql.SQLNonTransientConnectionException: [CA Clarity][SQLServer JDBC Driver]Connection reset by peer: socket write error

     

    FATAL 2015-08-12 01:24:30,728 [http-bio-8080-exec-7798] union.persistence (clarity:unknown:none:union.notFound) Failed to retrieve database version

    java.lang.RuntimeException: Unexpected invocation exception: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

    [http-bio-8080-exec-7512] union.persistence (clarity:none:none:none)

    java.sql.SQLNonTransientConnectionException: [CA Clarity][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.

     

     

     

    In the past I have seen these types of errors when the db or app servers briefly lose network connectivity. But I have checked in with our network and VMware guys and they assure me that no work was taking place at the times we see the errors. It has been happening for the last 3 days now at roughly the same time (between 01:30 and 03:30)

     

    Is there anything other than potential hardware/VMware issues that could explain above errors?



  • 2.  Re: Database Connection Issues

    Posted 08-12-2015 04:53 AM

    You should see the similar error at your database logs also, do you have any monitoring in place to ensure there is no packet drops.



  • 3.  Re: Database Connection Issues

    Posted 08-12-2015 09:13 AM

    Hi Suman,

     

    I checked Management -> SQL Server Logs but I cannot see anything in there which coincides with the errors I see in the app logs. I checked the SQL Server, Server Agent and Windows logs for anything occurring around that time but cannot see any causes....



  • 4.  Re: Database Connection Issues

    Posted 08-13-2015 10:53 AM

    Getting the same errors today and the application has become unavailable.

     

    In VMware everything looks fine. The servers are up and pingable. I have checked for any CPU and memory issues but there are none.

    Yet in the logs all the errors seem to indicate a loss of connection to the database.

     

    FATAL 2015-08-13 10:01:36,386 [http-bio-8080-exec-9533] union.persistence (clarity:x199689:199920848__04F0CA37-DC8A-4D25-A502-171C87C0F0AF:odf.objectDefinitionAttributeChangeType)

    java.sql.SQLNonTransientConnectionException: [CA Clarity][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.

     

    FATAL 2015-08-13 10:01:36,426 [http-bio-8080-exec-9533] union.persistence (clarity:x199689:199920848__04F0CA37-DC8A-4D25-A502-171C87C0F0AF:odf.objectDefinitionAttributeChangeType) Failed to determine analytic function support

    java.lang.RuntimeException: Unexpected invocation exception: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

     

    FATAL 2015-08-13 10:01:36,427 [http-bio-8080-exec-9533] union.persistence (clarity:x199689:199920848__04F0CA37-DC8A-4D25-A502-171C87C0F0AF:odf.objectDefinitionAttributeChangeType) Failed to retrieve database version

    java.lang.RuntimeException: Unexpected invocation exception: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

     

    Can anyone suggest any other troubleshooting route we could take?



  • 5.  Re: Database Connection Issues

    Posted 08-14-2015 03:15 AM

    HI,

     

    You have mentioned VMware so this may be applicable.

    We have an environment where our 4 Application servers are virtual running in our server farm.

    We saw similar issues if one of the application servers was moved to a different Host to the others.

    We had to set a policy that all application servers were kept on a single host at all times.

     

    Dave



  • 6.  Re: Database Connection Issues

    Posted 08-17-2015 06:03 AM

    We only have one application server and one database server in this particular environment and both are on the same host.

     

    We really are stumped here:

     

    Again today we see a temporary drop in database connection and then after a while it re-establishes connection again:

     

    ERROR 2015-08-17 01:03:02,497 [Dispatch pool-4-thread-4 : bg@SERVER (tenant=clarity)] persistence.PersistenceController (clarity:X163479:199931018__DFC41A43-EF4A-4883-B437-CD4590B6ED7F:Time Slicing) Exception due to DB Connection failure

     

    ERROR 2015-08-17 01:03:02,550 [Dispatch pool-4-thread-4 : bg@SERVER (tenant=clarity)] niku.blobcrack (clarity:X163479:199931018__DFC41A43-EF4A-4883-B437-CD4590B6ED7F:Time Slicing) Exception during blobcrack process

    java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

    FATAL 2015-08-17 01:03:02,560 [Dispatch pool-4-thread-4 : bg@SERVER (tenant=clarity)] union.persistence (clarity:X163479:199931018__DFC41A43-EF4A-4883-B437-CD4590B6ED7F:Time Slicing) Failed to determine analytic function support

    java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

    FATAL 2015-08-17 01:03:02,568 [Dispatch pool-4-thread-4 : bg@SERVER(tenant=clarity)] union.persistence (clarity:X163479:199931018__DFC41A43-EF4A-4883-B437-CD4590B6ED7F:Time Slicing) Failed to retrieve database version

    java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

     

    ERROR 2015-08-17 01:03:02,652 [Dispatch pool-4-thread-4 : bg@SERVER (tenant=clarity)] niku.blobcrack (clarity:X163479:199931018__DFC41A43-EF4A-4883-B437-CD4590B6ED7F:Time Slicing) Exception during blobcrack process

    com.niku.union.persistence.PersistenceException:

    SQL error code: 0

    Error message: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

    Executed:

    {call PRJ_BLB_SLICE_RESET_ALL_SP(?,?)}

    Derived from statement:

    <?xml version="1.0" encoding="UTF-8"?>

    <statement sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">

      <sql dbVendor="all">

        <text>

                  {call PRJ_BLB_SLICE_RESET_ALL_SP(?,?)}

                </text>

        <param type="string" direction="IN" constantValue="PRJ_RESOURCES" expressionListDelimiter=","/>

        <param name="sliceStatusFlag" type="int" direction="IN" expressionListDelimiter=","/>

      </sql>

    </statement>

     

     

    Referenced by:

    <?xml version="1.0" encoding="UTF-8"?>

    <statementRef id="blobcrack.setAllResourcesModified_set_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

     

     

    Using input:

    {sliceStatusFlag=3, parameters=[int:sliceStatusFlag:3:]}

     

     

      at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2019)

      at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2123)

      at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2662)

      at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:852)

      at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:752)

      at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:560)

      at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:290)

      at com.niku.xql2.pmd.PMDDataSource.execute(PMDDataSource.java:204)

      at com.niku.blobcrack.BlobCrackDB.resetRequest(BlobCrackDB.java:191)

      at com.niku.blobcrack.BlobCrackDB.resetRequests(BlobCrackDB.java:221)

      at com.niku.blobcrack.BlobCrackDB.prepareSliceStatus(BlobCrackDB.java:770)

      at com.niku.blobcrack.BlobCrackDB.sliceCurves(BlobCrackDB.java:589)

      at com.niku.blobcrack.BlobCrackDB.createSlices(BlobCrackDB.java:245)

      at com.niku.blobcrack.xql2.handlers.BlobCracker.processExpiredRequests(BlobCracker.java:158)

      at com.niku.blobcrack.xql2.handlers.BlobCracker.postProcess(BlobCracker.java:44)

      at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1355)

      at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210)

      at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94)

      at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

      at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

      at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:36)

      at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:257)

      at com.niku.njs.xbl.XBLService.process(XBLService.java:127)

      at com.niku.njs.xbl.XBLService.process(XBLService.java:75)

      at com.niku.njs.listeners.XBLJob.scheduledEventFired(XBLJob.java:44)

      at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:290)

      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

      at java.lang.Thread.run(Thread.java:745)

    Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver]Object has been closed.

      at com.ca.clarity.jdbc.sqlserverbase.ddb7.b(Unknown Source)

      at com.ca.clarity.jdbc.sqlserverbase.ddb7.a(Unknown Source)

      at com.ca.clarity.jdbc.sqlserverbase.ddb6.b(Unknown Source)

      at com.ca.clarity.jdbc.sqlserverbase.ddb6.a(Unknown Source)

      at com.ca.clarity.jdbc.sqlserverbase.BaseConnection.prepareCall(Unknown Source)

      at com.ca.clarity.jdbc.sqlserverbase.BaseConnection.prepareCall(Unknown Source)

      at com.niku.union.persistence.utils.Utils.createStatementInternal(Utils.java:669)

      at com.niku.union.persistence.utils.Utils.createStatement(Utils.java:633)

      at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2504)

      ... 26 more



  • 7.  Re: Database Connection Issues

    Posted 09-08-2015 07:27 PM

    Hi Colin - Any luck yet?



  • 8.  Re: Database Connection Issues

    Posted 09-09-2015 04:49 AM

    Hi Chris,

     

    in the end we disabled all scheduled jobs running in this environment. Usually, this environment just serves Sandbox requirements, e.g. testing patch updates, version upgrades, bug fixes etc.

     

    But because our Development environment was/is in the process of being upgraded to 14.2, our developers temporarily were using the Sandbox as their environment for doing Development work. The additional usage, coupled with scheduled jobs running appeared to be adding too much strain to the environment and causing funky things to happen in VMware. But because this was only a temporary situation, it was decided that we would not seek to add additional resources to the environment. We disabled all scheduled jobs and after this, while performance wasn't spectacular, it was deemed to be adequate for the developers' needs.