MySQL Bugs: #55701: lockAge in MySql.Web.SessionState.MySqlSessionStateStore

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:	Windows (Server 2008 R2)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any

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.

@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!

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

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

fixed in  6.1.5, 6.2.4, 6.3.4

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.