Bug #65144 Net Connector 6.4.4 Asp.Net Membership Database fails on MySql Db of UTF32

• Submitted: 28 Apr 2012 23:20
• Modified: 28 Sep 2012 18:59
• Reporter: Richard Lee
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 6.4.4
• OS: Any
• Assigned to: Fernando Gonzalez.Sanchez
• CPU Architecture: Any
• Tags: Net Connector Asp.Net Membership UTF32

[28 Apr 2012 23:20] Richard Lee

Description:
When using Dot Net Connector (C#) with autogenerate="true" on a database (MySql) set up as UTF32 then Tables only build to SchemaVersion 5 - running web app then gives a key to long error, and Website Administration Tool gives several errors depending on config, but will not connect to providers.

Note: Using MySql 5.5.23 on Win 78 Ultimate (locally) via Visual Studio 2010 SP1 Ultimate and SQL/Connector 6.4.4

How to repeat:
 Create a database in MySql:
drop database if exists MyTEST;
create database MyTEST character set = 'utf32' collate = 'utf32_general_ci';

Web Config (C# Web App) - only specifying relevent:
...
 <connectionStrings>
    <clear />
       <add name="LocalMySqlServer" connectionString="Database=MyTEST;Data Source=localhost;User Id=root;Password=password" 
         providerName="MySql.Data.MySqlClient" /> 
</connectionStrings>
<membership defaultProvider="MySqlMembershipProvider" >
      <providers >
        <clear/>
        <add name="MySqlMembershipProvider"
              type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"
                connectionStringName="LocalMySqlServer"
                enablePasswordRetrieval="false"
                enablePasswordReset="true"
                requiresQuestionAndAnswer="false"
                requiresUniqueEmail="true"
                passwordFormat="Hashed"
                maxInvalidPasswordAttempts="5"
                minRequiredPasswordLength="6"
                minRequiredNonalphanumericCharacters="0"
                passwordAttemptWindow="10"
                applicationName="/" 
                autogenerateschema="true"/>
      </providers>
    </membership>

    <roleManager enabled="true" defaultProvider="MySqlRoleProvider" >
      <providers>
        <clear />
        <add connectionStringName="LocalMySqlServer"
            applicationName="/"
            name="MySqlRoleProvider" 
            type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"
            autogenerateschema="true"/>
      </providers>
    </roleManager>

    <profile enabled="true" defaultProvider="MySqlProfileProvider" >
      <providers>
        <clear/>
        <add type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"
              name="MySqlProfileProvider"
              applicationName="/"
              connectionStringName="LocalMySqlServer" 
              autogenerateschema="true"/>
      </providers>
    </profile>
     
    <trust level="Full"/>
    <machineKey validationKey="AutoGenerate" validation="SHA1"/>
...

Build (F6)
Run (F5) crashes out with "Specified key was too long; max key length is 767 bytes".
or
Access Website Administration Tool (WSAT) -> Security Tab -> shows error such as "Unable to initialize provider. Missing or incorrect schema." -> Provider -> Advanced: Shows MySqlMembershipProvider and MySqlRoleProvider -> Click Test against either gives can not connect error (actually gives crap message about setting up Sql Server, but basically it is saying it can't connect to the provider).

Now run the following:
drop database if exists MyTEST;
create database MyTEST character set = 'utf8' collate = 'utf8_general_ci';

with all other settings the same, and it works.

Suggested fix:
At some point UTF32 needs to work - an easy work around is to user a separate DB (if have to use UTF32 for other reasons on main DB) for Membership Roles - note some ISP Hosting packages only allow one DB, so this could be an issue.

[22 May 2012 13:44] Bogdan Degtyariov

Verified in 6.4.4 and 6.5.4.
The problem occurs because `my_aspnet_sessions` has primary key that consists
of two columns:

SessionId VARCHAR(255)
ApplicationId INT

By default the table is created in Latin1 then converted to the database
default charset, which is UTF32 (4 bytes per character).
Hence, SessionId can exceed the limit of 768 bytes for the key: 255 * 4 = 1020

Reducing SessionId to VARCHAR(192) should help: 192 * 4 = 768

[28 Sep 2012 18:59] John Russell

Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

When using a MySQL database set up as UTF32 as an ASP.net membership
database, web applications could give a "key too long" error, and the
Website Administration Tool would not connect to providers. The cause
was that the column my_aspnet_sessions.SessionId, when converted from
Latin1 character set to UTF32 with 4 bytes per character, exceeded
the length limit for a primary key: Specified key was too long; max
key length is 767 bytes