CODEWITHSUNDEEP
asp.netmysqlentity-framework
Spoke
Spoke

Reputation: 76

Mysql syntax error while creating Database for Entity Framework

I'm playing around with asp.net for the first time. I want to use it with a MySQL database because this is what is offered by my hosting service and I don't want to upgrade/change services. I'm using visual web developer 2010 express. I created an MVC 4 project from the default template. The template created the ASP.NET Simple Membership objects which is what I'm trying to get working. The project builds and runs correctly when using the default database connection string. When I change the web.config file to point to MySQL I get the following error when I attempt to navigate to any of the pages in the account folder.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY, RoleName nvarc' at line 2

When I open the MySQL work bench and connect to the local server I notice that the database has been created. If I drop the DB and run the app again it gets recreated. I'm note sure if it was created correctly or if the entire database was created but there is something there.

Obviously there is an issue with the SQL syntax that is created by the Entity Framework. Do I need to add something to the web.config file to tell it what syntax it should use when creating the queries?

I've been searching for an answer to this for the past two days. any help pointing in the right direction would be appreciated.

I'm using mysql server version 5.5.27. and connector 6.5.4.0

here is the mysql part of my web.config file:

  <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=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet-MyWebPage-20120817115958;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
    <add name="myDatabaseConnection" connectionString="server=localhost;Port=3306;uid=root;pwd=****;database=myDatabase;" providerName="MySql.Data.MySqlClient" />        
  </connectionStrings>

Edit adding code

 [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
public sealed class InitializeSimpleMembershipAttribute : ActionFilterAttribute
{
    private static SimpleMembershipInitializer _initializer;
    private static object _initializerLock = new object();
    private static bool _isInitialized;

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        // Ensure ASP.NET Simple Membership is initialized only once per app start
        LazyInitializer.EnsureInitialized(ref _initializer, ref _isInitialized, ref _initializerLock);
    }

    private class SimpleMembershipInitializer
    {
        public SimpleMembershipInitializer()
        {
            Database.SetInitializer<UsersContext>(null);

            try
            {
                using (var context = new UsersContext())
                {
                    if (!context.Database.Exists())
                    {
                        // Create the SimpleMembership database without Entity Framework migration schema
                        ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                    }
                }

                WebSecurity.InitializeDatabaseConnection("LocalMySqlServer", "UserProfile", "UserId", "UserName", autoCreateTables: true);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException("The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588", ex);
            }
        }
    }
}



    public class UsersContext : DbContext
{
    public UsersContext()
        : base("LocalMySqlServer")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }
}

Upvotes: 2

Views: 2597

Answers (2)

Pseudo Nym
Pseudo Nym

Reputation: 41

The role provider is still defaulting to the standard ASP one which is expecting a SQLServer DB on the end of the connection, "Identity" is SQLServerese for "autoinc".

You can set the default providers in the web.config like this:-

<configuration>
  <system.web>
    <profile defaultProvider="MySQLProfileProvider"></profile>
    <roleManager defaultProvider="MySQLRoleProvider"></roleManager>
  </system.web>
</configuration>

Upvotes: 1

Umesh Kukreti
Umesh Kukreti

Reputation: 11

Try to modify the source of the SMP and remove the syntax specific to ms sql server.

Upvotes: 1

Related Questions