Bug #37469 autogenerateschema optimizing
Submitted: 18 Jun 2008 4:00 Modified: 11 Jul 2008 12:52
Reporter: Poul Bak Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:5.2.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: autogenerateschema membershipprovider

[18 Jun 2008 4:00] Poul Bak
Description:
When 'autogenerateschema' is on for the Membership- and Roleprovider it checks for version and whether to upgrade or not. That's fine.
But the way it does it, leads to unnessecary traffic.

File: SchemaManager.cs line: 93 looks like this:

private static int GetSchemaVersion(string connectionString)
{
    // retrieve the current schema version
    using (MySqlConnection conn = new MySqlConnection(connectionString))
    {
        conn.Open();

        string[] restrictions = new string[4];
        restrictions[2] = "mysql_Membership";
        DataTable dt = conn.GetSchema("Tables", restrictions);
        if (dt.Rows.Count == 1)
            return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]);

        restrictions[2] = "my_aspnet_schemaversion";
        dt = conn.GetSchema("Tables", restrictions);
        if (dt.Rows.Count == 0) return 0;

        MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn);
        object ver = cmd.ExecuteScalar();
        if (ver == null)
            throw new ProviderException(Resources.MissingOrWrongSchema);
        return (int)ver;
    }
}

This code will normally go right through to the third call (except the one time, it upgrades from an old version). Waste of db calls.
I suggest the 3rd call is moved up front, like shown below, so it will normally only make one call to the db.

How to repeat:
You can try to set 'logging=true' in the connectionstring and watch the output window in VS (make sure you restart the asp.net application). You will see 3 calls just to get the version.

Suggested fix:
Change the code to:

private static int GetSchemaVersion(string connectionString)
{
    // retrieve the current schema version
    using (MySqlConnection conn = new MySqlConnection(connectionString))
    {
        conn.Open();

        MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn);
        object ver = cmd.ExecuteScalar();
        if (ver != null)
            return (int)ver;

        string[] restrictions = new string[4];
        restrictions[2] = "mysql_Membership";
        DataTable dt = conn.GetSchema("Tables", restrictions);
        if (dt.Rows.Count == 1)
            return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]);

        restrictions[2] = "my_aspnet_schemaversion";
        dt = conn.GetSchema("Tables", restrictions);
        if (dt.Rows.Count == 0) return 0;
        throw new ProviderException(Resources.MissingOrWrongSchema);

    }
}
[18 Jun 2008 5:59] Tonci Grgin
Hi Poul and thanks for your report.

Verified as described by looking into c/NET code.
[8 Jul 2008 18:33] 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/49234
[8 Jul 2008 18:34] Reggie Burnett
- Reduced network traffic for the normal case where the web provider schema is up
  to date (bug #37469)

Fixed in 5.2.3
[11 Jul 2008 12:52] Tony Bedford
An entry has been added to the 5.0.23 changelog:

Unnecessary network traffic was generated for the normal case where the web provider schema was up to date.