MySQL Error The provider did not return a ProviderManifestToken

Recently, I upgraded MySQL Entity connector library for this website from 6.x to latest 8.x version. What looked like a simple nuget package update, ended up in strange errors that took more than 4 hours to track and find the cause. Just thought to make it as a article, so that it can save someone's precious time.

 When I started the upgrade, my MySQL packages were aligned to 6.10 version as follows

<package id="MySql.Data" version="6.10.7" targetFramework="net47" />
<package id="MySql.Data.Entity" version="6.10.7" targetFramework="net47" />
<package id="MySql.Web" version="6.9.9" targetFramework="net461" />

I just proceeded to 'Manage Nuget for solution' and upgraded all above three packages to latest version. It completed without any errors and compiled successfully. On first run, i.e., when database connection was made by the application it resulted in following error

An exception of type 'System.Data.Entity.Core.ProviderIncompatibleException' occurred in EntityFramework.dll
The provider did not return a ProviderManifestToken string.

Searching on web turned out with different issues and it certainly looked like a huge showstopper with no solution. After spending handful of time trying to do changes to the DbContext constructor, web.config etc.,

Then I started analyzing the changes in web.config, packages.config and realized there is a mismatch between the version number between MySql.Data.Entity (6.10.7) and  MySql.Data (8.0.11) and there is another new namespace reference MySql.Web.Profile.MySqlProfileProvider for ProfileProvider in web.config file - instead of MySql.Web.Security.MySqlProfileProvider, MySql.Web.

Solution:

1. Oracle has decided to rename and create a new nuget package for EntityFramework. i.e., you have to remove the reference to existing 'MySql.Data.Entity.EF6.dll' and replaced it with 'MySql.Data.EntityFramework.dll'. So the new package to add will be MySql.Data.EntityFramework.

So proper package references from packages.config shared below

<package id="MySql.Data" version="8.0.11" targetFramework="net47" />
<package id="MySql.Data.EntityFramework" version="8.0.11" targetFramework="net47" />
<package id="MySql.Web" version="8.0.11" targetFramework="net47" />

2. They have moved MySqlProfileProvider from MySql.Web.Security namespace to MySql.Web.Profile. So, ProfileProvider reference in web.config should be as follows

<profile defaultProvider="MySQLProfileProvider">
  <providers>
    <clear />
    <!--<add type="MySql.Web.Security.MySqlProfileProvider, MySql.Web" name="MySqlProfileProvider" applicationName="/" connectionStringName="DefaultConnection" autogenerateschema="true" />-->
    <remove name="MySQLProfileProvider" />
    <add applicationName="/" connectionStringName="DefaultConnection" name="MySQLProfileProvider" type="MySql.Web.Profile.MySqlProfileProvider" autogenerateschema="true" />
  </providers>
</profile>

3. Update the providers with the new namespace type within the entityFramework section. So, change the old namespace within <entityFramework> section to new namespace

<providers>
  <!-- <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" /> -->
  <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.EntityFramework" />
</providers>

4. Finally, delete the old dll from the Bin folder.

5. With MySql.Web 8.x, the sslmode is on by default. So the database connections are requested through secure ssl connection. But, if your MySql database server doesn't have certificate installed. Then you may encounter following error.

The host xxx.xx.xxx.xx does not support SSL connections.

 In order to turn-off the sslmode, you need to set it OFF in the connection string as follows

<add name="DefaultConnection" connectionString="server=xxx.xx.xxx.xx;port=yyyy;User Id=user_id;password=psswd;Persist Security Info=True;database=zzz_datastore;SslMode=none" providerName="MySql.Data.MySqlClient" />

Hope this saves your time.