Description:
In SessionProvider.cs, in several methods, the connection object is declared in the try block like this:
public override void CreateUninitializedItem(System.Web.HttpContext context, string id, int timeout)
{
MySqlTransaction mySqlTransaction = null;
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
...
}
}
If something wrong happens, the catch block tries to rollback the transaction, like this:
catch (MySqlException e)
{
if (mySqlTransaction != null)
{
try
{
Trace.WriteLine("CreateUninitializedItem: Attempt to rollback");
mySqlTransaction.Rollback();
}
catch (MySqlException ex)
{
HandleMySqlException(ex, "CreateUninitializedItem: Rollback Failed");
}
}
HandleMySqlException(e, "CreateUninitializedItem");
}
But the way the connection is declared, it has already been disposed once in the catch block, so this code thows an System.InvalidOperationException.
I think that the connection object should be declared before the first try block.
This pattern happens several times in the SessionProvider.cs source code, also in the CleanupOldSessions callback, hence the original bug report found here http://bugs.mysql.com/bug.php?id=67665.
Even though I already have described the problem in bug #67665, I recreated it here as a new bug item hoping that it will some more chances to be caught. Even though it is critical, Bug #67665 has no comments from Oracle team since it was first published, that is late november 2012.
How to repeat:
Simply follow the tutorial http://dev.mysql.com/doc/refman/5.5/en/connector-net-tutorials-asp-provider-session-state:
Web.config connectionString:
<connectionStrings>
<add name="SessionTestConnectionString" connectionString="server=localhost;port=3306;user id=root;password=password;database=session_test" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
Web.config sessionState:
<sessionState mode="Custom" cookieless="false" timeout="2" regenerateExpiredSessionId="true" customProvider="MySqlSessionStateProvider">
<providers>
<add name="MySqlSessionStateProvider" type="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" description="Session Test" connectionStringName="SessionTestConnectionString" writeExceptionsToEventLog="True" autogenerateschema="True" enableExpireCallback="False"/>
</providers>
</sessionState>
Get once the Default.aspx page,
Verify that the session is present in my_aspnet_sessions
Wait 2 minutes or so for the session to expire
Refresh the page in the browser and you get:
[InvalidOperationException: Connection must be valid and open to rollback transaction]
MySql.Data.MySqlClient.MySqlTransaction.Rollback() +137
MySql.Web.SessionState.MySqlSessionStateStore.CreateUninitializedItem(HttpContext context, String id, Int32 timeout) +486
System.Web.SessionState.SessionStateModule.CreateUninitializedSessionState() +47
System.Web.SessionState.SessionStateModule.GetSessionStateItem() +190
System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487
System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155
If you refresh once more you get:
[InvalidOperationException: Nested transactions are not supported.]
MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception) +227
MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex) +18
MySql.Data.MySqlClient.MySqlConnection.BeginTransaction(IsolationLevel iso) +144
MySql.Data.MySqlClient.MySqlConnection.BeginTransaction() +9
MySql.Web.SessionState.MySqlSessionStateStore.GetSessionStoreItem(Boolean lockRecord, HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +328
MySql.Web.SessionState.MySqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actions) +31
System.Web.SessionState.SessionStateModule.GetSessionStateItem() +117
System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487
System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155
The only way to overcome this without restarting both MySQL Server and IIS is the following: using MySQLWorkbench, kill the connection, then delete the orphaned session in the my_aspnet_sessions table. Hit refresh and it works again.
If you do not kill the server connection first, you might no be able to delete the orphaned session because it's locked, and the client would time-out:
ERROR 1205: Lock wait timeout exceeded; try restarting transaction
SQL Statement:
DELETE FROM `session_test`.`my_aspnet_sessions` WHERE `SessionId`='nobozc2i3p5k1l45ofnec1zq' and`ApplicationId`='1'
This has been tested on Windows 7 x64, .NET 4.0, MySql 5.1.66 / 5.5.29 and Connector.NET 6.6.4. To the contrary of what have been said by the original reporter, we do face that issue also with the previous GA release 6.5.5 Connector.NET.
Suggested fix:
Declare the connection object earlier and outside the try/catch blocks.
Description: In SessionProvider.cs, in several methods, the connection object is declared in the try block like this: public override void CreateUninitializedItem(System.Web.HttpContext context, string id, int timeout) { MySqlTransaction mySqlTransaction = null; try { using (MySqlConnection conn = new MySqlConnection(connectionString)) { ... } } If something wrong happens, the catch block tries to rollback the transaction, like this: catch (MySqlException e) { if (mySqlTransaction != null) { try { Trace.WriteLine("CreateUninitializedItem: Attempt to rollback"); mySqlTransaction.Rollback(); } catch (MySqlException ex) { HandleMySqlException(ex, "CreateUninitializedItem: Rollback Failed"); } } HandleMySqlException(e, "CreateUninitializedItem"); } But the way the connection is declared, it has already been disposed once in the catch block, so this code thows an System.InvalidOperationException. I think that the connection object should be declared before the first try block. This pattern happens several times in the SessionProvider.cs source code, also in the CleanupOldSessions callback, hence the original bug report found here http://bugs.mysql.com/bug.php?id=67665. Even though I already have described the problem in bug #67665, I recreated it here as a new bug item hoping that it will some more chances to be caught. Even though it is critical, Bug #67665 has no comments from Oracle team since it was first published, that is late november 2012. How to repeat: Simply follow the tutorial http://dev.mysql.com/doc/refman/5.5/en/connector-net-tutorials-asp-provider-session-state: Web.config connectionString: <connectionStrings> <add name="SessionTestConnectionString" connectionString="server=localhost;port=3306;user id=root;password=password;database=session_test" providerName="MySql.Data.MySqlClient"/> </connectionStrings> Web.config sessionState: <sessionState mode="Custom" cookieless="false" timeout="2" regenerateExpiredSessionId="true" customProvider="MySqlSessionStateProvider"> <providers> <add name="MySqlSessionStateProvider" type="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" description="Session Test" connectionStringName="SessionTestConnectionString" writeExceptionsToEventLog="True" autogenerateschema="True" enableExpireCallback="False"/> </providers> </sessionState> Get once the Default.aspx page, Verify that the session is present in my_aspnet_sessions Wait 2 minutes or so for the session to expire Refresh the page in the browser and you get: [InvalidOperationException: Connection must be valid and open to rollback transaction] MySql.Data.MySqlClient.MySqlTransaction.Rollback() +137 MySql.Web.SessionState.MySqlSessionStateStore.CreateUninitializedItem(HttpContext context, String id, Int32 timeout) +486 System.Web.SessionState.SessionStateModule.CreateUninitializedSessionState() +47 System.Web.SessionState.SessionStateModule.GetSessionStateItem() +190 System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487 System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155 If you refresh once more you get: [InvalidOperationException: Nested transactions are not supported.] MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception) +227 MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex) +18 MySql.Data.MySqlClient.MySqlConnection.BeginTransaction(IsolationLevel iso) +144 MySql.Data.MySqlClient.MySqlConnection.BeginTransaction() +9 MySql.Web.SessionState.MySqlSessionStateStore.GetSessionStoreItem(Boolean lockRecord, HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +328 MySql.Web.SessionState.MySqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actions) +31 System.Web.SessionState.SessionStateModule.GetSessionStateItem() +117 System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487 System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155 The only way to overcome this without restarting both MySQL Server and IIS is the following: using MySQLWorkbench, kill the connection, then delete the orphaned session in the my_aspnet_sessions table. Hit refresh and it works again. If you do not kill the server connection first, you might no be able to delete the orphaned session because it's locked, and the client would time-out: ERROR 1205: Lock wait timeout exceeded; try restarting transaction SQL Statement: DELETE FROM `session_test`.`my_aspnet_sessions` WHERE `SessionId`='nobozc2i3p5k1l45ofnec1zq' and`ApplicationId`='1' This has been tested on Windows 7 x64, .NET 4.0, MySql 5.1.66 / 5.5.29 and Connector.NET 6.6.4. To the contrary of what have been said by the original reporter, we do face that issue also with the previous GA release 6.5.5 Connector.NET. Suggested fix: Declare the connection object earlier and outside the try/catch blocks.