MySql Membership provider – Missing or incorrect schema

We have an ASP.NET site running on IIS 7.5 with a MySQL database using the .net connector, the membership/role providers are set in the web.config correctly and they work – but only temporarily.

Historically (.net connector 6.7.4.0 and prior) we always had an issue after restoring site backups including the membership tables, we'd get an error on the page saying we had a "missing or incorrect schema" for the membership provider, which is nonsense because we can see the tables clearly in the DB.

To solve this we'd simply delete the membership tables from the DB, set autogenerateschema=true in the web.config, let the role provider auto-generate the tables again, which works. We can then restore from our backup (the same backup!) and overwrite the new blank tables with our existing accounts and everything works fine. We figured this was just a quirk with the .net connector and because it only happened after restoring the DB from backups we weren't too fussed.

Now we've upgraded to .net connector 6.9.4.0 we're getting the same problem, only it's worse because now if the app pool is restarted for that website it triggers the same "missing or incorrect schema" message and we have apply the fix above. Doing things like making changes to the web.config trigger an app pool cycle which means we cannot make changes to the web.config without downing the site!

This is the role provider from the web.config with some details omitted, it's never changed other than us manually updating the version number.

<roleManager defaultProvider="MySQLRoleProvider" enabled="true">
      <providers>
        <clear />
        <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.9.4.0, Culture=neutral, PublicKeyToken=xxxxxx" applicationName="xxxxx" connectionStringName="xxxxxx" autogenerateschema="True" />
      </providers>
    </roleManager>

It should be noted that after triggering that error and applying our fix, we restore the membership tables from the exact same backup, this means the tables are identical before/after the fix, so it cannot be a problem with the database, it's some internal state of IIS.