Bug #62382 Unable to initialize provider. Missing or incorrect schema. (DB is utf8)
Submitted: 8 Sep 2011 12:07 Modified: 8 Sep 2011 12:29
Reporter: David Mc Kenzie Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:MySQL 5.1 Connector Net 6.4.3 OS:Microsoft Windows (XP PRO)
Assigned to: CPU Architecture:Any
Tags: DotNet Connector, utf8 Schema

[8 Sep 2011 12:07] David Mc Kenzie
Description:
The MySql Membership and Role providers can successfully create their tables when the MySql 5.1 database is created with the default schema.

However, when theMySql database is created with:
CREATE DATABASE `****` /*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Then the connector fails after creating 3 tables, and returns the error:
Unable to initialize provider.  Missing or incorrect schema.

The tables created are:
mysql_membership
mysql_roles
mysql_usersinroles

Connection string used:
connectionString="server=.;user=****;password=****;database=****;protocol=pipe;"

How to repeat:
0. Create a brand new VS2010 Express MVC Web Application with the starter pack that includes the home and about pages and the default membership, role and profile functionality.

1. Simply create a database using:
CREATE DATABASE `****` /*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

2. Then configure the web.config to:

  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="server=.;user=****;password=****;database=****;protocol=pipe;"
         providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
    <membership defaultProvider="MySqlMembershipProvider">
      <providers>
        <clear/>
        <add name="MySqlMembershipProvider"
             type="MySql.Web.Security.MySQLMembershipProvider, mysql.web"
             connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false"
             enablePasswordReset="true"
             requiresQuestionAndAnswer="false"
             requiresUniqueEmail="true"
             passwordFormat="Hashed"
             maxInvalidPasswordAttempts="5"
             minRequiredPasswordLength="6"
             minRequiredNonalphanumericCharacters="0"
             passwordAttemptWindow="10"
             applicationName="nubCoreDev"
             autogenerateschema="true"
        />
      </providers>
    </membership>

    <profile enabled="false" defaultProvider="MySqlProfileProvider">
      <providers>
        <clear/>
        <add type="MySql.Web.Security.MySqlProfileProvider, mysql.web"
             name="MySqlProfileProvider"
             applicationName="nubCoreDev"
             connectionStringName="ApplicationServices"
             autogenerateschema="true"/>
      </providers>
    </profile>

    <roleManager enabled="false" defaultProvider="MySqlRoleProvider">
      <providers>
        <clear/>
        <add connectionStringName="ApplicationServices"
             applicationName="nubCoreDev"
             name="MySqlRoleProvider"
             type="MySql.Web.Security.MySQLRoleProvider, mysql.web"
             autogenerateschema="true"/>
        <add name="AspNetWindowsTokenRoleProvider" 
             type="System.Web.Security.WindowsTokenRoleProvider" 
             applicationName="nubCoreDev" />
      </providers>
    </roleManager>

3. Remember that the database should be empty.

4. Remember to force the web-app to reload (force rebuild of the MVC app) after you drop the tables, otherwise the provider just returns the previous error issued without trying to re-create the tables.

Suggested fix:
A) I suspect that something is happening with the checking the database-schema when the profile tables are about to be created, and the profile tables fail to create, which prevents the schema - version table from being created.

B) I suspect that something is happening related to schema's when the schema - version table is about to be created, similar to (A) above.

Perhaps indicate what the supported schema's are in the error message, or at least have the error message indicate which provider is causing the error, so that I can write a replacement without having to re-write them all.
[8 Sep 2011 12:15] David Mc Kenzie
Never mind, something is wrong with the permissions I gave the user ... checking the permissions.

*sigh*

When you're dead sure that you've checked everything, after you submit the bug, THEN your subconscious reminds you of something you overlooked.

Apologies.
[8 Sep 2011 12:27] David Mc Kenzie
Found the permission problem:

The user was missing the ALTER permission, which I had decided was not required.

The my_aspnet_membership table had a comment of 1 at the fail point, compared to a comment of 2 when all tables were created. I almost immediately recognised that the table was being ALTERED instead of created straight.

Permissions that work:
GRANT SELECT, SHOW VIEW, CREATE, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `****`.* TO `****`@`localhost`;
[8 Sep 2011 12:29] David Mc Kenzie
Just making sure that the status is closed.
[8 Sep 2011 16:29] Reggie Burnett
No problem.  Glad you got it sorted.