API Gateway: Error "Cannot retrieve a C3P0 DataSource: No connections available for '<databaseName>'"

Products

• CA API Gateway

Issue/Introduction

This article will discuss how to troubleshoot a common error where a Perform JDBC Query assertion will fail with the following error message:

"Perform JDBC Query" assertion failed due to: Cannot retrieve a C3P0 DataSource: No connections available for 'OAuth'

Of course, the "OAuth" database name can be replaced with any JDBC connection name. The name simply represents which JDBC connection which is experiencing the issue.

Environment

This article applies to all supported versions of the API Gateway.

Cause

This typically can be caused by two different situations:

• The backend database server has reached its allowed max connections. This affects incoming requests to the database server.
• The Gateway nodes themselves have reached the pool size allocated to the JDBC connection. This affects outgoing requests.

Resolution

First, the root cause needs to be identified so that the solution can then be identified. To do this, follow the instructions below:

1. If there is any tooling available that shows how many connections are present on the database server from the Gateway, review that data and determine if the connections are all used or if there is ample connections available. If the maximum connections set on the database is 2000, for example, see if nearly all 2000 connections are utilized, or if it's well under that.
2. If there is any tooling available that shows how many JDBC connections are consumed/utilized on the API Gateway nodes, then this should be used to review the data to see if they are all utilized or if there is ample connections available from the JDBC C3P0 pool.

If the database server connections are all utilized...

1. The database connection size should either be increased to allow for more connections, or the database can simply be restarted to clear out all active current connections. The restart is considered to be more of a workaround though in this case as ultimately there were too many connections to the database and it would need to be able to handle that traffic volume. Typically there is a property for the database server that specifics the maximum number of connections allowed.

If the database server connections are not all utilized...

1. This would generally suggest the issue is on the Gateway nodes where the pool size for the JDBC connection has been fully utilized. This means no more outgoing requests can be made from the Gateway to the JDBC destination. In such a case, the pool should be increased to allow for more JDBC connections outbound from the Gateway nodes. Temporarily, a restart of the SSG service can provide relief as it will terminate all active JDBC connections.