Jiwa

We are getting the following error either through JsonService client or the swagger ui

Code: Select all

{
  "ResponseStatus": {
    "ErrorCode": "InvalidOperationException",
    "Message": "Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.",
    "StackTrace": "[Authenticate: 05/02/2019 12:52:58 AM]:\n[REQUEST: {provider:credentials,UserName:Admin,Password:Removed,RememberMe:True}]\nSystem.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.\r\n   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\r\n   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)\r\n   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)\r\n   at System.Data.SqlClient.SqlConnection.Open()\r\n   at JiwaFinancials.Jiwa.JiwaODBC.database.GetNewConnection(String NewConnectionString)\r\n   at JiwaFinancials.Jiwa.JiwaODBC.database.Logon(AuthenticationModes AuthenticationMode, String Name, String Password, Boolean& rtnMustChangePassword, DateTime& rtnPasswordLastChangedDateTime, Boolean IsAPIKey)\r\n   at JiwaFinancials.Jiwa.JiwaODBC.database.Logon(AuthenticationModes AuthenticationMode, String Name, String Password, Boolean& rtnMustChangePassword, DateTime& rtnPasswordLastChangedDateTime)\r\n   at JiwaFinancials.Jiwa.JiwaApplication.Manager.Logon(String ServerName, String DatabaseName, AuthenticationModes AuthenticationMode, String JiwaUserName, String JiwaPassword, Boolean IsAPIKey)\r\n   at JiwaFinancials.Jiwa.JiwaApplication.Manager.Logon(String ServerName, String DatabaseName, AuthenticationModes AuthenticationMode, String JiwaUserName, String JiwaPassword)\r\n   at JiwaFinancials.Jiwa.JiwaServiceModel.JiwaAuthProvider.TryAuthenticate(IServiceBase authService, String userName, String password) in c:\\ProgramData\\Jiwa Financials\\Jiwa 7\\7.2.0\\JAProd\\mch-az-syd-sql1.database.windows.net\\Cassons7\\Plugins\\api\\Compile\\REST API\\REST API.cs:line 2929\r\n   at ServiceStack.Auth.CredentialsAuthProvider.Authenticate(IServiceBase authService, IAuthSession session, String userName, String password, String referrerUrl)\r\n   at ServiceStack.Auth.CredentialsAuthProvider.Authenticate(IServiceBase authService, IAuthSession session, Authenticate request)\r\n   at ServiceStack.Auth.AuthenticateService.Authenticate(Authenticate request, String provider, IAuthSession session, IAuthProvider oAuthConfig)\r\n   at ServiceStack.Auth.AuthenticateService.Post(Authenticate request)\r\n   at lambda_method(Closure , Object , Object )\r\n   at ServiceStack.Host.ServiceRunner`1.<ExecuteAsync>d__13.MoveNext()",
    "Errors": []
  }
}


How do we resolve this, strangely the problem seems to be intermittent in that its there but without restarting the api suddenly it will logon again?

SBarnes wrote:How do we resolve this, strangely the problem seems to be intermittent in that its there but without restarting the api suddenly it will logon again?

This would normally indicate connections being opened and not closed - aka "leaking" connections.

Run an sp_who2 against the database regularly to see if you can identify an excessive number of connections and the ProgramName responsible.

If you've any code (even in plugins) which open a connection, make sure they are closed when appropriate.

There is only 318 results related to the given database and most just say JiwaFiancials against them.

I am wondering if it could be related to this given ORMLite is calling stored procedures and ResolveService gets used?

https://stackoverflow.com/questions/19927765/timeout-expired-using-db-in-servicestack-service

SBarnes wrote:I am wondering if it could be related to this given ORMLite is calling stored procedures and ResolveService gets used?

https://stackoverflow.com/questions/19927765/timeout-expired-using-db-in-servicestack-service

Perhaps - although to me it looks like that would only be an issue if a service is being used from within another service.

You mentioned you saw 318 SQL connections, most with the ProgramName Jiwa Financials - Would it be safe to assume most of those connections are actually from users of the Jiwa Application and not the API? You can retrieve a list of active API sessions / users via the /Staff/Current/List route (requires authentication) - have look in there to see how many active API sessions there are just to eliminate connection leakage through the API.

As a work-around you could try increasing the Max Pool Size in the connection string that the API uses for ORMLite connections - if you suspect it's connection leakage from our use of ORMLite in the API.

We have a line of code in the REST API plugin that looks like this:

Code: Select all

Container.Register<ServiceStack.Data.IDbConnectionFactory>(new OrmLiteConnectionFactory(JiwaApplicationManager.Database.ConnectionString, ServiceStack.OrmLite.SqlServer2012Dialect.Provider));

So, you could append to the connection string a value for Max Pool Size (default is 100, so maybe triple it).

Code: Select all

Container.Register<ServiceStack.Data.IDbConnectionFactory>(new OrmLiteConnectionFactory(JiwaApplicationManager.Database.ConnectionString + ";Max Pool Size=300", ServiceStack.OrmLite.SqlServer2012Dialect.Provider));

It's not a fix, but an attempt to see if that alleviates the problem so we know where to look or where not to look for the issue.

Hi Mike,

We do have one service calling another but those have been changed as per stack overflow and the problem still persists, so I will follow your suggestion, is there the possibility this is just about timing out on the connection rather than the pool because the interesting thing is in the Swagger UI I can hit try it out on Auth and one second it doesn't work and produces the error and a second or two later it will work?

Hi Mike,

The change you suggested has made no difference we are still getting the error intermittently.

SBarnes wrote:Hi Mike,

The change you suggested has made no difference we are still getting the error intermittently.

Ok, so how many connections are there? and how many API sessions? count the connections by sp_who2, paste results into excel, filter on ProgramName Jiwa Financials. For API sessions count visit /Staff/Current/List

What do you mean by For API sessions count visit /Staff/Current/List

We have a route to show currrent API sessions - e.g:

https://api.jiwa.com.au/Staff/Current/List?apikey=CMvxNJ-yjmi-QmNmD4gazTYAVjsxu_DINuE-M8gBlA8

It requires authentication, and I've configured our api with an api key which has permission to visit /Staff/Current/List - it should show the current sessions.