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:
-
Baixe a versão 5.2.5 ou superior do MySql Connector;
-
Salve os arquivos Mysql.Data.dll e MySql.Web.dll na pasta bin do seu site;
-
Execute o código abaixo no seu banco de dados:
- 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; -
INSERT INTO my_aspnet_Applications VALUES (1,’appName’,'MySQL Profile provider’);
- DROP TABLE IF EXISTS `db_name`.`my_aspnet_Applications`;
- Configure seu web.config:
- 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”/> - 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> - 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>
- session connectionStrings
Ps0a
Referências:
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
Novaes,
Primeiramente ótimo post.
Mas apliquei a solução que você apresentou e ocorreu o erro que você já citou “Unable to initialize provider. Missing or incorrect schema”, no roleManager.
Fiz tudo que orienta seu post mas não resolveu.
Você tem alguma sugestão?
Obrigado,
André