SQL Connection errors Kentico.Libraries 12.0.99

Gabriel Rasdale asked on June 7, 2022 12:17

https://www.nuget.org/packages/Kentico.Libraries/ - Migrated from Kentico.Libraries 11 to Kentico.Libraries 12.0.99 - since the migration seeing a lot of SQL connection errors after 12-24 hours of uptime on the API:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is open.

System.InvalidOperationException: Not allowed to change the 'ConnectionString' property. The connection's current state is connecting.

System.InvalidOperationException: Invalid operation. The connection is closed. at System.Data.ProviderBase.DbConnectionClosed.CreateReferenceCollection() at System.Data.ProviderBase.DbConnectionInternal.AddWeakReference(Object value, Int32 tag) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

kentico libraries 12.0.99 sql

Recent Answers

Sean Wright answered on June 9, 2022 05:11

Did you run the full upgrade of your site from 11 to 12 or did you only upgrade the NuGet package version? The upgrade instructions are very detailed and require following the steps exactly.

0 votesVote for this answer Mark as a Correct answer

Gabriel Rasdale answered on June 9, 2022 12:03

Thanks Sean, Yes, I followed the instructions, made sure to run the admin panel on 12.0.0 before applying the patch to 12.0.99, the kentico admin is working fine, no issues. The issue only arises on a supplementary API where I upgraded the nuget package from 11 to 12.0.99 - every thing works fine, however after 12-24 hours of uptime I start seeing the sql connection errors. I did add an additional setting in the web.config / app.config for the api, CMSDisposeConnectionAfterClose to true, lets see if this resolves the issue.

0 votesVote for this answer Mark as a Correct answer

Gabriel Rasdale answered on June 10, 2022 10:41

No, adding the CMSDisposeConnectionAfterClose = true to the config did not resolve the issue

0 votesVote for this answer Mark as a Correct answer

Mohammad Qamar answered on June 10, 2022 17:09

@Sean : Is the Kentico sql connection code being wrapped with a Using statement? For example

using (SqlConnection connection = new SqlConnection(connectionString)) 
{
    //some code
}

This usually picks up this kind of errors?

0 votesVote for this answer Mark as a Correct answer

Gabriel Rasdale answered on June 13, 2022 19:02

Seems like there was a bug introduced in hotfix 12.0.73, versions before this do not experience the sql connection issues. In this case the method CMS.Membership.AuthenticationHelper.AuthenticateUser (https://devnet.kentico.com/docs/12_0/api/html/M_CMS_Membership_AuthenticationHelper_AuthenticateUser_1.htm) is not handling multiple requests, 2 users logging in simultaneously seems to cause the errors.

Specifically the call to UserInfoProvider.SetUserInfo(user) in FinalizeAuthenticationProcess(UserInfo user, int siteId) seems to cause the issue.

Wondering if the update for 12.0.73 - "Context API not working in async code Kentico API that relied on static contexts, such as 'SiteContext', 'ContactManagementContext', or 'CMSActionContext', did not work and returned empty values when called within custom asynchronous (async) methods. After applying the hotfix, the contexts correctly persist their values within async code." - has caused the issue?

0 votesVote for this answer Mark as a Correct answer