使用EF Core的UseMySQL连接db4free.net数据库遇权限问题

项目环境与代码 #

项目包含Local和Cloud两个实现IDatabase接口的数据库上下文，其中CloudDatabase的代码如下：

namespace <MyActualProjectName>.DatabaseCore
{
    public class CloudDatabase : DbContext, IDatabase
    {
        public DbSet<UserModel> Users { get; set; }

        private string? DbPath;
        private IConfiguration config;

        public CloudDatabase(IConfiguration configuration) {
            config = configuration;
            DbPath = config.GetConnectionString("DefaultConnection");
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySQL(DbPath!);
        }
    }
}

问题详情 #

已通过dotnet ef CLI成功生成迁移，但执行以下命令时触发错误：

dotnet ef database update --context <MyProjectName>.DatabaseCore.CloudDatabase

错误信息如下：

Build started...
Build succeeded.
Applying migration '20230830041438_CreateUserTable'.
Failed executing DbCommand (126ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE IF NOT EXISTS `<MyActualDatabaseName>`
MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user '<MyActualUsername>'@'%' to database '<MyActualDatabaseName>'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacketAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.NativeDriver.GetResultAsync(Int32 affectedRow, Int64 insertedId, Boolean execAsync)
   at MySql.Data.MySqlClient.Driver.GetResultAsync(Int32 statementId, Int32 affectedRows, Int64 insertedId, Boolean execAsync)
   at MySql.Data.MySqlClient.Driver.NextResultAsync(Int32 statementId, Boolean force, Boolean execAsync)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
Access denied for user '<MyActualUsername>'@'%' to database '<MyActualDatabaseName>'

异常点是执行CREATE DATABASE IF NOT EXISTS时权限被拒，但奇怪的是数据库中的__EFMigrationsHistory表已成功创建，却无法生成Users表。

当前连接字符串配置 #

"ConnectionStrings": {
    "DefaultConnection": "server=db4free.net;port=3306;database=<DatabaseName>;user=<username>;password=<password>"
}

已尝试的无效操作 #

• 给连接字符串添加http://或https://前缀（注：MySQL连接不需要此类前缀，属于错误操作）
• 反复确认用户名和密码正确性

解决方案建议 #

1. 检查db4free用户的数据库权限 #

db4free的用户权限默认限制较多，需登录db4free管理面板确认：

• 目标数据库<DatabaseName>是否已手动创建（EF自动执行CREATE DATABASE可能被服务器拦截，因为普通用户可能没有建库权限）
• 确保当前用户<username>拥有目标数据库的完整权限（至少包含CREATE、ALTER、INSERT、UPDATE等）

2. 禁用EF自动创建数据库的行为 #

修改CloudDatabase的OnConfiguring方法，跳过自动建库逻辑，因为数据库已存在：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySQL(DbPath!)
        .ConfigureWarnings(warnings => warnings.Ignore(Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.MigrationsTableMissing));
}

3. 替换为Pomelo.EntityFrameworkCore.MySql驱动 #

官方MySql.Data.EntityFrameworkCore与db4free可能存在兼容性问题，建议更换为社区维护的Pomelo驱动：

• 卸载原驱动：

  dotnet remove package MySql.Data.EntityFrameworkCore
  

• 安装对应EF Core版本的Pomelo驱动：

  dotnet add package Pomelo.EntityFrameworkCore.MySql --version <你的EF Core版本号>
  

• 修改OnConfiguring方法：

  optionsBuilder.UseMySql(DbPath!, ServerVersion.AutoDetect(DbPath!));
  

4. 修正连接字符串格式 #

确保连接字符串无多余前缀或符号，保持标准格式：

"DefaultConnection": "server=db4free.net;port=3306;database=<DatabaseName>;user=<username>;password=<password>"

内容的提问来源于stack exchange，提问作者Peter Bou Saada