Bug #55701 lockAge in MySql.Web.SessionState.MySqlSessionStateStore
Submitted: 3 Aug 2010 5:44 Modified: 12 Aug 2010 15:19
Reporter: Whikiey Yan Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.3.3 beta OS:Microsoft Windows (Server 2008 R2)
Assigned to: Vladislav Vaintroub CPU Architecture:Any

[3 Aug 2010 5:44] Whikiey Yan
Description:
I'm developing with VS2010, so i must use Connector/Net V6.3+ .
when i use the session state, sometimes, it will appear an error follow:

System.Data.SqlTypes.SqlNullValueException

Stack:
--------------------------------------------------------------------
   MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull) +185
   MySql.Data.MySqlClient.MySqlDataReader.GetInt32(Int32 i) +56
   MySql.Web.SessionState.MySqlSessionStateStore.GetSessionStoreItem(Boolean lockRecord, HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +1310
   MySql.Web.SessionState.MySqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actions) +57
   System.Web.SessionState.SessionStateModule.GetSessionStateItem() +115
   System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +768
   System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +96
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184
-------------------------------------------------------------------

and i found that position from the source code, this is it, error is from a SQL:
----------------------------------------------
File: SessionProvider.cs
line 524: "  TIME_TO_SEC(NOW() - LockDate)) as lockAge " +
----------------------------------------------

please fix it thank you.

How to repeat:
this TIME_TO_SEC function returns NULL when (NOW() > LockDate) and (the second of NOW() < the second LockDate) and (the second LockDate - the second of NOW() < 40)

you can write two test datetime type vars, to test the return value of TIME_TO_SEC .

Suggested fix:
change "TIME_TO_SEC(NOW() - LockDate))"
 into "TIME_TO_SEC(NOW()) - TIME_TO_SEC(LockDate))".
and it will work fine.
[3 Aug 2010 16:29] Vladislav Vaintroub
@Whikiey, thanks for reporting, we should check it.
Can you perhaps clarify what you said in "How to repeat section": TIME_TO_SEC returns NULL if ... (and then you lost me, the condition seems to be to complicated)

Thanks!
[3 Aug 2010 21:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114968

838 Vladislav Vaintroub	2010-08-03
      Bug #55701: fix calculation of lockAge in SessionProvider .
      
      Do not use datimetime arithmetic in SQL, because it is either
      
      -  buggy (like TIME_TO_SEC(d1 - d2)  returns NULL sometimes)
      or
      
      - works only for restricted ranges
      TIME_TO_SEC(TIMEDIFF(d1, d2) would throw a warning
      if d1 and d2 have  more than 800+ hours  difference
      
      or
      - non-portable 
      TIMESTAMPDIFF() works only with version 5.0 and later
      
      
      Instead, do datetime calculations entirely in .NET
[3 Aug 2010 21:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114970

886 Vladislav Vaintroub	2010-08-03 [merge]
      merge bug#55701
[3 Aug 2010 21:58] Vladislav Vaintroub
fixed in  6.1.5, 6.2.4, 6.3.4
[12 Aug 2010 15:20] Tony Bedford
An entry has been added to the 6.1.5, 6.2.4, and 6.3.4 changelogs:

The calculation of lockAge in the Session Provider sometimes generated a System.Data.SqlTypes.SqlNullValueException.