Bug #47219 Connector .NET Sessions are not compatible with server versions less than 5.0
Submitted: 9 Sep 2009 15:38 Modified: 5 Nov 2009 14:27
Reporter: Marc Relation Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.1.2 OS:Any
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: SessionProvider.cs, TIMESTAMPDIFF
Triage: D3 (Medium)

[9 Sep 2009 15:38] Marc Relation
Description:
The latest mysql connector with asp.net session support will not work with any mysql server less than 5.0 due to the fact that SessionProvider.cs uses a call to TIMESTAMPDIFF and that function does not exist in less than 5.0.  

Code could probably be modified to use subtraction and TIME_TO_SEC in order to work correctly.

******* RELEVANT CODE FROM SessionProvider.cs **************
"SELECT (NOW() > Expires) as Expired, SessionItems, LockId,  Flags, Timeout, " +
                      "  TIMESTAMPDIFF(SECOND, LockDate, NOW()) as lockAge " +
                      "  FROM my_aspnet_Sessions" +
                      "  WHERE SessionId = @SessionId AND ApplicationId = @ApplicationId", conn);

How to repeat:
Setup a session connector to a mysql 4.1 server and you will receive asp.net errors.

Suggested fix:
"SELECT (NOW() > Expires) as Expired, SessionItems, LockId,  Flags, Timeout, " +
                      "  TIME_TO_SEC(NOW() - LockDate) as lockAge " +
                      "  FROM my_aspnet_Sessions" +
                      "  WHERE SessionId = @SessionId AND ApplicationId = @ApplicationId", conn);
[13 Oct 2009 11:33] Tonci Grgin
Hi Marc and thanks for your report.

Verified as described by looking into latest c/NET sources (trunk):
  SessionProvider.cs
    Ln 462: private SessionStateStoreData GetSessionStoreItem(
      Ln 524: "  TIMESTAMPDIFF(SECOND, LockDate, NOW()) as lockAge " +
[4 Nov 2009 15:46] 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/89327

793 Vladislav Vaintroub	2009-11-04
      - fixed SessionProvider to be compatible with 4.x MySQL, replaced TIMESTAMPDIFF with TIME_TO_SEC
        (bug#47219)
[4 Nov 2009 18:07] 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/89345

796 Vladislav Vaintroub	2009-11-04
      - fixed SessionProvider to be compatible with 4.x MySQL, replaced TIMESTAMPDIFF with TIME_TO_SEC
       (bug#47219)
[5 Nov 2009 14:27] Tony Bedford
An entry has been added to the 6.2.1 changelog:

MySQL Connector/NET session support did not work with MySQL Server versions prior to 5.0, as the Session Provider used a call to TIMESTAMPDIFF, which was not available on servers prior to 5.0.