Bug #46939 Connector/NET generates exception when using Session State Provider
Submitted: 26 Aug 2009 13:26 Modified: 9 Sep 2009 9:50
Reporter: Tony Bedford Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.1.1 OS:Microsoft Windows (XP Pro sp3)
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: Connector/Net, Session State Provider

[26 Aug 2009 13:26] Tony Bedford
Description:
When using Connector/NET 6.1.1 Session State Provider with MySQL 5.1 and VS Pro 2008, the following exception occurs (see below for details). There are a couple of weird things - the application *did* work - and then generated the exception below, and will no longer run without throwing the exception. 

Previously created projects configured to use the session state provider continue to work fine. However, newly created projects that use the MySQL Website Configuration tool to select use of the MySQL session state provider do *not* work - even if the code does not actually use a Session object.

The exception generated is shown below:

Server Error in '/WebSite7' Application.
--------------------------------------------------------------------------------

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MINUTEWHERE SessionId = 'dtmgga55x35oi255nrfrxe45' AND ApplicationId = 1 AND Loc' at line 1 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MINUTEWHERE SessionId = 'dtmgga55x35oi255nrfrxe45' AND ApplicationId = 1 AND Loc' at line 1

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 

[MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MINUTEWHERE SessionId = 'dtmgga55x35oi255nrfrxe45' AND ApplicationId = 1 AND Loc' at line 1]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +216
   MySql.Data.MySqlClient.NativeDriver.ReadResult() +91
   MySql.Data.MySqlClient.ResultSet.NextResult() +366
   MySql.Data.MySqlClient.MySqlDataReader.NextResult() +400
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +998
   MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() +36
   MySql.Web.SessionState.MySqlSessionStateStore.ReleaseItemExclusive(HttpContext context, String id, Object lockId) +286

[ProviderException: An exception occurred. Please check the event log.]
   MySql.Web.SessionState.MySqlSessionStateStore.HandleMySqlException(MySqlException e, String action) +160
   MySql.Web.SessionState.MySqlSessionStateStore.ReleaseItemExclusive(HttpContext context, String id, Object lockId) +351
   System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +807
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

The application I was testing is as follows: Note the exception still occurs even with the key lines commented out:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //TextBox1.Text = "Enter some text";
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //Session["SessionItem"] = TextBox1.Text;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //TextBox1.Text = "";
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        //TextBox1.Text = (string)Session["SessionItem"];
    }
}

How to repeat:
1) Create a new ASP.NET web project

2) Use the MySQL Website Configuration tool to select to use MySQL to store session data.

3) Also select the Autogenerate Schema checkbox

4) Build and run the project the exception occurs

Note the application at this point doesn't even need to access a session object for the exception to occur.

I am not quite sure what caused this to happen - I have written several Session State Provider applications and this did not occur before.

Suggested fix:
I have no idea! Sorry!
[26 Aug 2009 14:03] Tony Bedford
Further info:

If I reference a 'dummy' session state variable in Page_Load the code works every time.  As long as I reference the session state variable in Page_Load everything seems to be fine. See the following working code. 

Is this right? Or just my poor understanding?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           TextBox1.Text = "Enter some text"; 
        }
        Session["Dummy"] = "Dummy string";
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Session["SessionItem"] = TextBox1.Text;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        TextBox1.Text = (string)Session["SessionItem"];
    }
}
[27 Aug 2009 6:18] Tonci Grgin
Hi Tony and thanks for your report.

Let's us discuss it when you're on-line.
[27 Aug 2009 9:19] Tony Bedford
Visual Studio Solution file

Attachment: WebSite7_sln.zip (application/x-zip-compressed, text), 2.79 KiB.

[27 Aug 2009 9:19] Tony Bedford
Project source and config files

Attachment: WebSite7_files.zip (application/x-zip-compressed, text), 3.33 KiB.

[27 Aug 2009 14:56] 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/81745

753 Reggie Burnett	2009-08-27
      - fixed some bugs with session state provider (bug #46939)
      - fixed typo in asp.net config wizard
[27 Aug 2009 14:56] Reggie Burnett
fixed in 6.1.2
[9 Sep 2009 9:50] Tony Bedford
An entry has been added to the 6.1.2 changelog:

MySQL Connector/NET generated the following exception when using the Session State provider:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'MINUTEWHERE SessionId =
'dtmgga55x35oi255nrfrxe45' AND ApplicationId = 1 AND Loc' at line 1 
Description: An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and where it
originated in the code. 

Exception Details: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near 'MINUTEWHERE SessionId = 'dtmgga55x35oi255nrfrxe45' AND ApplicationId =
1 AND Loc' at line 1