Блог

Главная - Вопросы по программированию - Azure mysql DB с ошибкой entity frameworkcore

Azure mysql DB с ошибкой entity frameworkcore

#mysql #azure #asp.net-core #entity-framework-core #azure-mysql-database

#mysql #azure #asp.net-ядро #entity-framework-core #azure-mysql-database

Вопрос:

я только что закончил сборку своего приложения и обнаружил, что созданная мной база данных Azure mysql выдает некоторые ошибки. Когда я использую обычную mysql db локально во время разработки, все работает отлично и отлично, когда я отправил некоторые данные в azure db из внешнего api, db сохранила изменения, но единственная проблема в том, что она не может видеть или сохранять изменения, сгенерированные моим приложением. Я использую клиент данных pomelo mysql 3.2.4 . когда я пытаюсь зарегистрировать пользователя в azure db, я получаю эту ошибку:

  Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (138ms) [Parameters=[@__normalizedUserName_0='?' (Size = 256)], CommandType='Text', CommandTimeout='30']
      SELECT `a`.`Id`, `a`.`AccessFailedCount`, `a`.`Bio`, `a`.`ConcurrencyStamp`, `a`.`CreatedAt`, `a`.`Email`, `a`.`EmailConfirmed`, `a`.`Gender`, `a`.`GravatarP
rofileImage`, `a`.`Instagram`, `a`.`LockoutEnabled`, `a`.`LockoutEnd`, `a`.`NormalizedEmail`, `a`.`NormalizedUserName`, `a`.`PasswordHash`, `a`.`PhoneNumber`, `a`.
`PhoneNumberConfirmed`, `a`.`SecurityStamp`, `a`.`Twitch`, `a`.`Twitter`, `a`.`TwoFactorEnabled`, `a`.`UserName`, `a`.`Youtube`
      FROM `AspNetUsers` AS `a`
      WHERE `a`.`NormalizedUserName` = @__normalizedUserName_0
      LIMIT 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'Persistance.DataContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
         at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 806
         at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 51
         at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 130
         at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cached
Procedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlC
lient/MySqlDataReader.cs:line 391
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBeh
avior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 62
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/My
Sql.Data.MySqlClient/MySqlCommand.cs:line 257
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancella
tionToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancella
tionToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancella
tionToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken
cancellationToken)
         at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucc
eeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 806
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 51
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProced
ures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/
MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior
ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Da
ta.MySqlClient/MySqlCommand.cs:line 257
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancel
lationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded,
 CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
fail: Api.Middlewares.Errors.ErrorHandlingMiddleware[0]
      SERVER ERROR
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'a.CreatedAt' in 'field list'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 806
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 51
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProced
ures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/
MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior
ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Da
ta.MySqlClient/MySqlCommand.cs:line 257
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationTo
ken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancel
lationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded,
 CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken
 cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken
 cancellationToken)
   at Microsoft.AspNetCore.Identity.UserManager`1.FindByNameAsync(String userName)
   at Microsoft.AspNetCore.Identity.UserValidator`1.ValidateUserName(UserManager`1 manager, TUser user, ICollection`1 errors)
   at Microsoft.AspNetCore.Identity.UserValidator`1.ValidateAsync(UserManager`1 manager, TUser user)
   at Microsoft.AspNetCore.Identity.UserManager`1.ValidateUserAsync(TUser user)
   at Microsoft.AspNetCore.Identity.UserManager`1.CreateAsync(TUser user)
   at Microsoft.AspNetCore.Identity.UserManager`1.CreateAsync(TUser user, String password)
   at Application.Auth.Register.Handler.Handle(Command request, CancellationToken cancellationToken) in C:UsersAsusDesktopc#QlipApplicationAuthRegister.cs:
line 73
   at MediatR.Pipeline.RequestExceptionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionActionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionActionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestPostProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestPreProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at Api.Controllers.UserController.Register(Command command) in C:UsersAsusDesktopc#QlipapiControllersUserController.cs:line 22
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor execu
tor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionR
esultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask,
State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(Stateamp; next, Scopeamp; scope, Objectamp; state, Booleanamp; isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State
 next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope
scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Api.Middlewares.Errors.ErrorHandlingMiddleware.Invoke(HttpContext context) in C:UsersAsusDesktopc#QlipapiMiddlewaresErrorHandlingMiddleware.cs:line 3
0

если я удалю строку подключения azure mysql-db и заменю ее локальной строкой подключения mysql, код будет работать так, как ожидалось.
мой класс контекста данных:

  public class DataContext : IdentityDbContext<ApplicationUser>
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options)
        {
            
        }

        public DbSet<Clip> Clips { get; set; }
        public DbSet<Game> Games { get; set; }
        public DbSet<UserClip> UserClips { get; set; }
        public DbSet<DislikeUserClip> DislikeUserClips{ get; set; }
        public DbSet<UserFollowings> Followings { get; set; }
        public DbSet<Comment> Comments { get; set; }
        public DbSet<View> Views { get; set; }
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<UserClip>().HasKey(uc => new {uc.ClipId, uc.ApplicationUserId});

            modelBuilder.Entity<UserClip>().HasOne(au => au.ApplicationUser).WithMany(au => au.UserClips)
                .HasForeignKey(au => au.ApplicationUserId);

            modelBuilder.Entity<UserClip>().HasOne(c => c.Clip).WithMany(uc => uc.UserClips)
                .HasForeignKey(c => c.ClipId);

            modelBuilder.Entity<DislikeUserClip>().HasKey(duc => new {duc.ClipId, duc.ApplicationUserId});

            modelBuilder.Entity<DislikeUserClip>().HasOne(au => au.ApplicationUser).WithMany(au => au.DislikeUserClips)
                .HasForeignKey(au => au.ApplicationUserId);

            modelBuilder.Entity<DislikeUserClip>().HasOne(c => c.Clip).WithMany(c => c.DislikeUserClips)
                .HasForeignKey(c => c.ClipId);

            modelBuilder.Entity<UserFollowings>().HasKey(uf => new {uf.ObserverId, uf.TargetId});

            modelBuilder.Entity<UserFollowings>().HasOne(o => o.Observer).WithMany(uf => uf.Followings)
                .HasForeignKey(o => o.ObserverId);
            modelBuilder.Entity<UserFollowings>().HasOne(t => t.Target).WithMany(uf => uf.Followers)
                .HasForeignKey(t => t.TargetId);
            
            // view table
            modelBuilder.Entity<View>().HasKey(v => new {v.ApplicationUserId, v.ClipId});

            modelBuilder.Entity<View>().HasOne(au => au.User).WithMany(v => v.Views)
                .HasForeignKey(x => x.ApplicationUserId);

            modelBuilder.Entity<View>().HasOne(c => c.Clip).WithMany(v => v.Views).HasForeignKey(x => x.ClipId);
        }
    }

Примечание: когда я впервые запустил свое приложение с помощью azure db, оно создало все таблицы, необходимые моему приложению, поэтому я не понимаю, почему это происходит.
Последние несколько дней я ломал голову из-за этой ошибки, и я, честно говоря, не могу понять, что происходит.
Пожалуйста, кто-нибудь может помочь мне с этой проблемой, с которой я сталкиваюсь?

Комментарии:

1. Как и в случае исключения, у вас есть повторная синхронизация в схеме базы данных, а столбец CreatedAt отсутствует в Azure DB.

2. Это странно, в моем классе пользовательских сущностей я создал его как свойство. Вы предлагаете мне добавить его вручную или я пытаюсь использовать ef core и добавить миграцию?

3. Если в Azure уже есть данные, попробуйте сделать это вручную. Тогда обязательно используйте миграции.

4. да, сделал это вручную, теперь все работает нормально. Большое спасибо!, не могу поверить, что что-то столь незначительное нарушает всю кодовую базу, лол