MySql 5.6 with EntityFramework 6 schema error 0040

Question

I have an ASP.NET MVC site using EF6 code first which work perfectly against MS SQL Server (both on local dev machine and Azure Websites/SQL). Now I am moving it to production which uses MySql 5.6 DBS and its giving me headaches.

My solution is separated into layers (web, view models, models, data layer interfaces) which are persistence ignorant (using UnitOfWork and GenericRepository) and a single data project referencing EF assemblies.

Now I thought I would branch my solution switch EF providers from SQL Server to MySql. So I added MySql.Data.Entities NuGet package and made following changes to web.config:

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
  </providers>
</entityFramework>

The MySql Connector/Net version is MySql.Data 6.8.3.0

I have also added [DbConfigurationType(typeof(DbContextConfiguration))] to my DbContext class and SetExecutionStrategy(MySqlProviderInvariantName.ProviderName, () => new MySqlExecutionStrategy()); to my DbContextConfiguration class which inherits from MySqlEFConfiguration

I was able to run my code-first migrations (with the help of SetHistoryContextFactory(MySqlProviderInvariantName.ProviderName, (conn, schema) => new MySqlHistoryContext(conn, schema));) and create the database schema.

The database schema seems correct as far as I can tell. Columns that were nvarchar in SQL Server are longtext or varchar (depending on the max length specified on entity properties) in MySql and datetime2 are datetime.

Now when I run the application I am getting the following exception:

System.Data.DataException was unhandled by user code
  HResult=-2146233087
  Message=An exception occurred while initializing the database. See the InnerException for details.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
       at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)
       at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
       at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
       at Attendance.Web.Controllers.ControllerWithCurrentUser.GetCurrentUser() in .....
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.InvokeActionMethodFilterAsynchronouslyRecursive(Int32 filterIndex)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.InvokeActionMethodFilterAsynchronouslyRecursive(Int32 filterIndex)
  InnerException: System.Data.Entity.Core.MetadataException
       HResult=-2146232007
       Message=Schema specified is not valid. Errors: 
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
       Source=EntityFramework
       StackTrace:
            at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors()
            at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.LoadItems(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths)
            at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader..ctor(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths, Boolean throwOnError, IDbDependencyResolver resolver)
            at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Init(IEnumerable`1 xmlReaders, IEnumerable`1 filePaths, Boolean throwOnError, IDbDependencyResolver resolver, DbProviderManifest& providerManifest, DbProviderFactory& providerFactory, String& providerInvariantName, String& providerManifestToken, Memoizer`2& cachedCTypeFunction)
            at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(IEnumerable`1 xmlReaders)
            at System.Data.Entity.Utilities.XDocumentExtensions.GetStorageMappingItemCollection(XDocument model, DbProviderInfo& providerInfo)
            at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(XDocument sourceModel, XDocument targetModel, Lazy`1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator)
            at System.Data.Entity.Internal.InternalContext.ModelMatches(XDocument model)
            at System.Data.Entity.Internal.ModelCompatibilityChecker.CompatibleWithModel(InternalContext internalContext, ModelHashCalculator modelHashCalculator, Boolean throwIfNoMetadata)
            at System.Data.Entity.Internal.InternalContext.CompatibleWithModel(Boolean throwIfNoMetadata)
            at System.Data.Entity.Database.CompatibleWithModel(Boolean throwIfNoMetadata)
            at System.Data.Entity.CreateDatabaseIfNotExists`1.<>c__DisplayClass1.<InitializeDatabase>b__0()
            at System.Data.Entity.Internal.MigrationsChecker.IsMigrationsConfigured(InternalContext internalContext, Func`1 databaseExists)
            at System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)
            at System.Data.Entity.Internal.InternalContext.<>c__DisplayClasse`1.<CreateInitializationAction>b__d()
            at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       InnerException: 

Note the

Schema specified is not valid. Errors: 
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(0,0) : error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.

I am at my wits ends. Cant figure out what to do with this or how to make EF work with the MySql provider properly. There is nothing useful about this on SO or the web in general. All I found was regarding something similar with Oracle DBS at http://forums.devart.com/viewtopic.php?t=24678

I will be grateful for any ideas. I am starting to think that it would be better to move to NHibernate, if only I knew it would solve this - I was wondering if the problem was related to how EF maps entities and generates SQL statements or some bug in the MySql provider/connector.

Answer 1

The problem exists when you have a migration created for SQL Server and then you change the engine to MySQL and try to update the database. I fixed that by:

1. Removing all of the migrations classes
2. Dropping __migrationhistory mysql table (if present)
3. Configuring Entity Framework to use MySQL
4. Creating new initial migration.

The config file needs this:

<connectionStrings>
    <add name="PrimaryDatabase" providerName="MySql.Data.MySqlClient"
        connectionString="server=localhost;port=3306;database=mydatabase;uid=root;password=root"/>
</connectionStrings>
<entityFramework  codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
    </providers>
</entityFramework>
<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.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
</system.data>

The DbContext class uses the connection string named PrimaryDatabase, which was defined above:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base("PrimaryDatabase")
    {
    }

}

Finally in the migrations Configuration file:

internal sealed class Configuration : DbMigrationsConfiguration<MyProject.ApplicationDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
    }
}

You can find detailed informations here.