Layer 7 API Management

Expand all | Collapse all

JDBC connection goes stale (CA API 9.4)

Jump to Best Answer
  • 1.  JDBC connection goes stale (CA API 9.4)

    Posted 08-29-2019 08:55 AM
    Hello all,
    I have created a JDBC connection to a SQLServer 2012 database, I test the connection (let's say CONNECTION1) and it works fine.
    The connection name is then used in the different API policies.
    After a while, the APIs using that connection ALL fail.

    When I retest the JDBC connection, I have: Testing failed: unexpected problem during connection.
    Note: the database itself has had no change.

    Actions I did: 
    a) When I rename CONNECTION1 under another name (CONNECTION2) -> CONNECTION2 works.
    b) I then remove CONNECTION1, wait 30 minutes, and then recreate it using the same name CONNECTION1 -> it continues to fail.
    c) I then rename CONNECTION1 as CONNECTION3 -> CONNECTION3 works.

    Obviously I do not want to change all the policies using the database connection.

    1) Is that a known problem? If yes, is there a software fix to it?
    2) Is there a workaround for this in the meantime?






    ------------------------------
    Regards,
    Laurent LANDREAU
    +61.451.838.336
    ------------------------------


  • 2.  RE: JDBC connection goes stale (CA API 9.4)

    Posted 08-29-2019 12:42 PM
    Hi, Laurent.

    if you restart gateway CONNECTION1 works again?
    If you rename CONNECTION2 or CONNECTION3 to CONNECTION1, it works?

    Try look at gateway logs to see if there is any other message related to this issue. You may have to change log level to FINE or FINER in log sink to get some connection pool detailed error.


    ------------------------------
    Sr. Consultant Services
    HCL Enterprise Studio
    ------------------------------



  • 3.  RE: JDBC connection goes stale (CA API 9.4)

    Posted 08-29-2019 07:19 PM
    One way out is the following:
    * delete CONNECTION1
    * restart the gateway
    * recreate CONNECTION1

    ... not something we can do when we go to production.
    So that is somewhat acceptable in development, but not when we reach business critical platforms.


  • 4.  RE: JDBC connection goes stale (CA API 9.4)
    Best Answer

    Posted 08-30-2019 02:50 PM
    Dear Laurent,

    I guess you don't have much traffic on this JDBC connection. Gateway uses c3p0 database connection pooling and c3p0 closes connections on inactivity.

    I believe if you set following additional properties in JDBC connection it might help:

    testConnectionOnCheckout   =>   to true  ,  c3p0 checked
    testConnectionOnCheckout   => to true,        c3p0  checked
    preferredTestQuery  => Select 1                , c3p0 checked

    Regards


  • 5.  RE: JDBC connection goes stale (CA API 9.4)

    Posted 09-04-2019 06:03 PM
    Thanks - will try that.