"The provider did not return a ProviderManifestToken string" MySQL with Entity Framework

Question

I have set up a new project in VS 2017. My intention is to use EF CodeFirst approach. So far I used Azure SQL Database, while in this test project I want to use my remote MySQL database. I have created the user and permissions are set just right. This remote database server is accessible to me over MySQL Workbench.

I have created a new blank MVC project and through Nuget I installed MySQL.Data.Entity (version 6.9.10).

My Context class:

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class WebDb : DbContext
{
    public WebDb() : base("WebDb")
    {

    }

    public DbSet<Candidate> Candidates { get; set; }
}

My web.config has these entries:

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
    </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.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

And my connection string is:

<connectionStrings>
    <add name="WebDb" providerName="MySql.Data.MySqlClient" connectionString="server=x.x.x.x;uid=dbuser;pwd=password;database=temp1;" />
</connectionStrings>

I have a simple domain class

public class Candidate
{
    public int Id { get; set; }
    public string Name { get; set; }
}

When I give Enable-Migrations -Force command, I get

Checking if the context targets an existing database... System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. ---> MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts. at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open() at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() at MySql.Data.MySqlClient.MySqlProviderServices.GetDbProviderManifestToken(DbConnection connection) at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) --- End of inner exception stack trace --- at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) at MySql.Data.Entity.MySqlManifestTokenResolver.ResolveManifestToken(DbConnection connection) at System.Data.Entity.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest) at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection) at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext) at System.Data.Entity.Internal.RetryLazy2.GetValue(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.LazyInternalContext.get_ModelBeingInitialized() at System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(DbContext context, XmlWriter writer) at System.Data.Entity.Utilities.DbContextExtensions.<>c__DisplayClass1.<GetModel>b__0(XmlWriter w) at System.Data.Entity.Utilities.DbContextExtensions.GetModel(Action1 writeXml) at System.Data.Entity.Utilities.DbContextExtensions.GetModel(DbContext context) at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration, DbContext usersContext, DatabaseExistenceState existenceState, Boolean calledByCreateDatabase) at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration) at System.Data.Entity.Migrations.Design.MigrationScaffolder..ctor(DbMigrationsConfiguration migrationsConfiguration) at System.Data.Entity.Migrations.Design.ToolingFacade.ScaffoldRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() The provider did not return a ProviderManifestToken string.

I have searched far and wide to the best of my knowledge to no avail. What's happening here? What am I missing?

Answer 1

I've been having the same problems as you recently. Everything worked fine in SQL Server, but I was having lots of problems converting to MySQL. Some things that worked for me are:

    Install-Package MySQL.Data -Version 6.9.9
    Install-Package MySql.Data.Entity -Version 6.9.10

The newer 8.0 packages of MySQL appear to have problems. When I reverted to an older version, it worked.

Your app.config should look like:

      <dependentAssembly>
        <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-6.9.9.0" newVersion="6.9.9.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.8.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
    </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.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
      <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.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
</configuration>

Even after that, there are some discrepancies in MySQL that I'm just finding out. For example, your migration "index" statements will not work. You'll have to edit the migration file and build the index yourself. Another thing that I just read, but haven't encountered, is that the MySQL driver does not allow multiple connections which may mean changing the way you retrieve async collections. Lastly, I had a problem with RowVersion being a byte[]. I used a variation from the following article to solve this (hopefully!).

Better way to implement a row version with EF Core and MySQL?