PSOA

Depois de muito choro e ranger de dentes consegui fazer o Mysql funcionar como provider para a autenticação aspnet na locaweb.

Alguns dos erros com os quais tive de lidar:

“Unable to initialize provider. Missing or incorrect schema”

“Column count doesn’t match value count at row 1″

“Unknown column ‘r.name’ in ‘field list’ at…”

A solução abaixo funcionou para mim, espero que ajude:

1. Baixe a versão 5.2.5 ou superior do MySql Connector;
2. Salve os arquivos Mysql.Data.dll e MySql.Web.dll na pasta bin do seu site;
3. Execute o código abaixo no seu banco de dados:
   1. DROP TABLE IF EXISTS `db_name`.`my_aspnet_Applications`;
      CREATE TABLE  `db_name`.`my_aspnet_Applications` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(256) default NULL,
      `description` varchar(256) default NULL,
      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_Membership`;
      CREATE TABLE  `db_name`.`my_aspnet_Membership` (
      `userId` int(11) NOT NULL default ’0′,
      `email` varchar(128) default NULL,
      `Comment` varchar(255) default NULL,
      `Password` varchar(128) NOT NULL,
      `PasswordKey` char(32) default NULL,
      `PasswordFormat` tinyint(4) default NULL,
      `PasswordQuestion` varchar(255) default NULL,
      `PasswordAnswer` varchar(255) default NULL,
      `IsApproved` tinyint(1) default NULL,
      `LastActivityDate` datetime default NULL,
      `LastLoginDate` datetime default NULL,
      `LastPasswordChangedDate` datetime default NULL,
      `CreationDate` datetime default NULL,
      `IsLockedOut` tinyint(1) default NULL,
      `LastLockedOutDate` datetime default NULL,
      `FailedPasswordAttemptCount` int(10) unsigned default NULL,
      `FailedPasswordAttemptWindowStart` datetime default NULL,
      `FailedPasswordAnswerAttemptCount` int(10) unsigned default NULL,
      `FailedPasswordAnswerAttemptWindowStart` datetime default NULL,
      PRIMARY KEY  (`userId`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’3′;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_Profiles`;
      CREATE TABLE  `db_name`.`my_aspnet_Profiles` (
      `userId` int(11) NOT NULL,
      `valueindex` longtext,
      `stringdata` longtext,
      `binarydata` longblob,
      `lastUpdatedDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`userId`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_Roles`;
      CREATE TABLE  `db_name`.`my_aspnet_Roles` (
      `id` int(11) NOT NULL auto_increment,
      `applicationId` int(11) default NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_SchemaVersion`;
      CREATE TABLE  `db_name`.`my_aspnet_SchemaVersion` (
      `version` int(11) default NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_Users`;
      CREATE TABLE  `db_name`.`my_aspnet_Users` (
      `id` int(11) NOT NULL auto_increment,
      `applicationId` int(11) default NULL,
      `name` varchar(256) default NULL,
      `isAnonymous` tinyint(1) default NULL,
      `lastActivityDate` datetime default NULL,
      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

      DROP TABLE IF EXISTS `db_name`.`my_aspnet_UsersInRoles`;
      CREATE TABLE  `db_name`.`my_aspnet_UsersInRoles` (
      `userId` int(11) NOT NULL default ’0′,
      `roleId` int(11) NOT NULL default ’0′,
      PRIMARY KEY  (`userId`,`roleId`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

   2. INSERT INTO my_aspnet_Applications VALUES   (1,’appName’,'MySQL Profile provider’);
4. Configure seu web.config:
   1. session connectionStrings
      <remove name=”localCn”/>
      <add name=”localCn” connectionString=”Database={db_name};Data Source=’{db host}’;User Id={usuario};Password={senha}; pooling=false” providerName=”MySql.Data.SqlClient”/>
   2. session system.data
      <DbProviderFactories>
      <remove invariant=”MySql.Data.MySqlClient”/>
      <add name=”MySQL Data Provider” invariant=”MySql.Data.MySqlClient” description=”.Net Framework Data Provider for MySQL” type=”MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”/>
      </DbProviderFactories>
   3. session system.web
      <roleManager enabled=”true” defaultProvider=”MySQLRoleProvider”>
      <providers>
      <remove name=”MySQLRoleProvider”/>
      <add connectionStringName=”localCn”
      applicationName=”{/appName}”
      name=”MySQLRoleProvider”
      type=”MySql.Web.Security.MySQLRoleProvider,MySql.Web, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”/>
      </providers>
      </roleManager>
      <membership defaultProvider=”MySQLMembershipProvider”>
      <providers>
      <remove name=”MySQLMembershipProvider”/>
      <add connectionStringName=”localCn”
      enablePasswordRetrieval=”true”
      enablePasswordReset=”true”
      requiresQuestionAndAnswer=”true”
      applicationName=”{/appName}”
      requiresUniqueEmail=”false”
      passwordFormat=”Clear”
      maxInvalidPasswordAttempts=”5″
      minRequiredPasswordLength=”6″
      minRequiredNonalphanumericCharacters=”1″
      passwordAttemptWindow=”10″
      passwordStrengthRegularExpression=”"
      name=”MySQLMembershipProvider”
      type=”MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”/>
      </providers>
      </membership>
      <profile enabled=”true” defaultProvider=”MySQLProfileProvider” automaticSaveEnabled=”false”>
      <providers>
      <remove name=”MySQLProfileProvider”/>
      <add name=”MySQLProfileProvider”
      type=”MySql.Web.Profile.MySQLProfileProvider,MySql.Web, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”
      connectionStringName=”localCN”
      applicationName=”{/appName}”/>
      </providers>
      </profile>
      <compilation debug=”false”>
      <assemblies>
      <add assembly=”MySql.Web, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”/>
      <add assembly=”MySql.Data, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”/>
      </assemblies>
      </compilation>

Ps0a

Referências:

Download Connector/Net 5.2

Koders – MySQLMembershipProvider.cs

MySQL Forums :: .NET :: Unable to initialize provider. Missing or incorrect schema

Implement .NET Membership and Roles using MySql Connector 5.2.3 on GoDaddy

nolanbailey.com – MySQL ASP.NET Membership and Role Provider

Suite of MySQL Provider Implementations for ASP.NET 2.0

Examining ASP.NET 2.0′s Membership, Roles, and Profile – Part 8