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.

did you manage to fix your issue? i'm getting similar issue when trying to to run update-database command. — Mindaugas, Jan 20 '15 at 08:04

score 5 · Answer 1 · answered Mar 05 '16 at 16:12

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:

Removing all of the migrations classes
Dropping __migrationhistory mysql table (if present)
Configuring Entity Framework to use MySQL
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.

Worked first time. +1. I had a left over SQL connection string in my database too from SQL Server which I cleaned up after migration. — Mike Upjohn, Aug 17 '17 at 10:48

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

You want to use your app with MySQL but the configured default connection factory seems to be for SqlServer. As a result you get SqlServer store types instead of MySQL types and things break. You should use the MySQL connection factory - see this thread Entity Framework 5.0 code-first with MySQL Connector 6.6.5.0 on .Net 4.5 they have all config setting there (including MySQL specific connection factory).

edited May 23 '17 at 12:08

Community

1
1

answered Mar 07 '14 at 23:47

Pawel

31,342
4
73
104

I've tried what you suggest and changed the part of web.config to ``. Now I am getting exption - Unable to cast object of type 'MySql.Data.MySqlClient.MySqlClientFactory' to type 'System.Data.Entity.Infrastructure.IDbConnectionFactory'. – mr.martan Mar 07 '14 at 23:56
I made another change to ``. This seems to make more sense. I am still getting the same error as in the beggining. – mr.martan Mar 08 '14 at 00:00
Make sure that the app is using the right config. I would also remove the entries you added for the execution strategy and history context if you are not sure that you really need them. Check your DbConfiguration (if you have one) although config should wind. The information you posted (given you have fixed the config) does not seem to be sufficient to troubleshoot more. – Pawel Mar 08 '14 at 00:24
It is obviously using the right config, as changes to the default connection factory had an effect. The strategy is there to deal with transient errors (not necessarily needed) and history context is there to make code-first migrations work with MySql. I dont know what more to add, I've included all changes from MS SQL branch, which is working. – mr.martan Mar 08 '14 at 04:13
I know this was from 10 months ago, but did you ever get any resolution on your issue? I'm seeing the same problem. – Adam Jan 20 '15 at 20:55
WHat does your connection string look like Adam? – Burt Jan 21 '15 at 13:06
I have the same issue here. The connection string looks like this: – Mindaugas Jan 24 '15 at 11:14

MySql 5.6 with EntityFramework 6 schema error 0040

2 Answers2