Pomelo.EntityFrameworkCore.MySql icon indicating copy to clipboard operation
Pomelo.EntityFrameworkCore.MySql copied to clipboard

Published 20 hours ago • verified publisher icon PomeloFoundation

Insertion failed after upgrading .net 6 to .net8

Open lycoris-xmin opened this issue 9 months ago • 0 comments

Steps to reproduce

I tried to upgrade the .net6 project to .net8 to use the latest package, but after the upgrade, an error occurred when inserting data. I checked the log and found that there was something wrong with the insert statement. I don’t know what the problem was.

The database statement printed by the log is as follows. I found that there is no information after the statement and

I don’t know the reason yet, because the project was normal before on .net6

INSERT INTO `T_System_User_Token` (`LastTokenDelayTime`, `RefreshToken`, `RefreshTokenExpiredTime`, `RowVersion`, `Token`, `TokenExpiredTime`)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT `Id`, `LastToken`
FROM `T_System_User_Token`
WHERE ROW_COUNT() = 1 AND

In the database context, I overridden SaveChanges and SaveChangesAsync to support my transactional Aop


        /// <summary>
        /// ctor
        /// </summary>
        /// <param name="options"></param>
        public MySqlContext([NotNull] DbContextOptions options) : base(options)
        {
            // 关闭DbContext默认事务
            Database.AutoTransactionBehavior = AutoTransactionBehavior.Never;
        }

        /// <summary>
        /// 重写 SaveChanges 方法
        /// </summary>
        /// <returns></returns>
        public override int SaveChanges()
        {
            var entities = ChangeTracker.Entries().ToList();

            PropertyAutoProvider(entities);

            // 没有自动开启事务的情况下,保证主从表插入,主从表更新开启事务。
            var isManualTransaction = false;

            if (Database.AutoTransactionBehavior != AutoTransactionBehavior.Never && _transaction != null && !_transaction.IsStartingUow && entities.Count > 1)
            {
                isManualTransaction = true;
                Database.AutoTransactionBehavior = AutoTransactionBehavior.Always;
            }

            var result = base.SaveChanges();

            // 如果手工开启了自动事务,用完后关闭
            if (isManualTransaction)
                Database.AutoTransactionBehavior = AutoTransactionBehavior.Never;

            return result;
        }

        /// <summary>
        /// 重写 SaveChanges 方法
        /// </summary>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
        {
            var entities = ChangeTracker.Entries().ToList();

            PropertyAutoProvider(entities);

            // 没有自动开启事务的情况下,保证主从表插入,主从表更新开启事务。
            var isManualTransaction = false;

            if (Database.AutoTransactionBehavior != AutoTransactionBehavior.Never && _transaction != null && !_transaction.IsStartingUow && entities.Count > 1)
            {
                isManualTransaction = true;
                Database.AutoTransactionBehavior = AutoTransactionBehavior.Always;
            }

            var result = await base.SaveChangesAsync();

            // 如果手工开启了自动事务,用完后关闭
            if (isManualTransaction)
                Database.AutoTransactionBehavior = AutoTransactionBehavior.Never;

            return result;
        }

table class

 /// <summary>
 /// 系统用户令牌表
 /// </summary>
 [Table("System_User_Token")]
 public class SystemUserToken : MySqlBaseEntity<long>
 {
     /// <summary>
     /// 主键，用户编号
     /// </summary>
     [Key]
     public override long Id { get; set; }

     /// <summary>
     /// 访问令牌
     /// </summary>
     public string Token { get; set; } = string.Empty;

     /// <summary>
     /// 访问令牌过期时间
     /// </summary>
     public DateTime TokenExpiredTime { get; set; }

     /// <summary>
     /// 刷新令牌
     /// </summary>
     public string RefreshToken { get; set; } = string.Empty;

     /// <summary>
     /// 刷新令牌过期时间
     /// </summary>
     public DateTime RefreshTokenExpiredTime { get; set; }

     /// <summary>
     /// 延期令牌
     /// </summary>
     public string? LastToken { get; set; }

     /// <summary>
     /// 延期令牌过期时间
     /// </summary>
     public DateTime? LastTokenDelayTime { get; set; }

     private const string SALT = "PTRCFAKA";

     /// <summary>
     /// 
     /// </summary>
     /// <param name="userId"></param>
     /// <param name="expiredTime"></param>
     /// <returns></returns>
     public static string GenerateToken(long userId, DateTime expiredTime)
     {
         var value = $"{Guid.NewGuid()}|{userId}|{expiredTime:yyyy-MM-dd HH:mm:ss}|{Guid.NewGuid()}";
         return SecretHelper.CommonEncrypt(value, SALT);
     }

     /// <summary>
     /// 
     /// </summary>
     /// <param name="token"></param>
     public static (long? userId, DateTime? expiredTime) AnalyzeToken(string token)
     {
         var value = SecretHelper.CommonDecrypt(token, SALT);
         var tmp = value.Split('|');
         if (tmp.Length != 4)
             return (null, null);

         var userId = tmp[1].ToTryLong();
         var expiredTime = tmp[2].ToTryDateTime();

         return (userId, expiredTime);
     }
 }

fucntion

public async Task<LoginDto> LoginAsync(SystemLoginVerificationDto input, bool remember)
{
    var userToken = await _userToken.GetAsync(input.Id) ?? new SystemUserToken() { Id = 0 };

    if (!userToken.Token.IsNullOrEmpty())
    {
        // 移除旧令牌
        await _authorizeCache.RemoveUserLoginStateAsync(userToken.Token);
    }

    if (!userToken.LastToken.IsNullOrEmpty())
    {
        // 移除旧令牌
        await _authorizeCache.RemoveUserLoginStateAsync(userToken.LastToken!);
        userToken.LastToken = "";
        userToken.LastTokenDelayTime = null;
    }

    userToken.TokenExpiredTime = DateTime.Now.AddMinutes(15);
    userToken.Token = SystemUserToken.GenerateToken(input.Id, userToken.TokenExpiredTime);

    userToken.RefreshTokenExpiredTime = DateTime.Now.AddDays(remember ? 7 : 1);
    userToken.RefreshToken = SystemUserToken.GenerateToken(input.Id, userToken.TokenExpiredTime);

    // 创建令牌
    if (userToken.Id == 0)
    {
        await _userToken.CreateAsync(userToken);
    }
    else
    {
        await _userToken.UpdateAsync(userToken);
    }

    // 缓存登录令牌
    var cache = new SystemUserLoginCacheDto()
    {
        Id = input.Id,
        NickName = input.NickName!,
        Avatar = input.Avatar!,
        Status = input.Status,
        RoleId = input.RoleId,
        Token = userToken.Token,
        TokenExpiredTime = userToken.TokenExpiredTime
    };

    await _authorizeCache.SetUserLoginStateAsync(cache);

    return userToken.ToMap<LoginDto>();
}

The issue

[10:41:13 ERR] Failed executing DbCommand (11ms) [Parameters=[@p0=NULL (DbType = DateTime), @p1='3EF3037DCE4A4355CC96D5EA48FA7ADF8735A4C0E3DB5C88B0217C3763022D1E55D836B6FA4CEBC0AA3A7FB91BD4FFD497799A47403C70E4AEA006970A6B765DD5D87DB9F6DA34ADBC9734CFFEBF410248ED98856EF999CFBE4BBA9C7260BCA31CDC9EEE0AEAE7F7' (Nullable = false) (Size = 300), @p2='2024-05-22T10:41:13.6754210+08:00' (DbType = DateTime), @p3=NULL (DbType = DateTime), @p4='25F2DBD55E3A7E4EE6395AC6DE1DEAD841BEA8BA36E8DE9A1F13E7C608E04017F2F8744BD3F507C65DA15E5A985CB5CD54C3B89974CAB188BCA1C811579BBE9382844A27C652C38061BFFBB011F191DB125F0AB3853E2382969A149148CDAEA0831D5116F0B06C9A' (Nullable = false) (Size = 300), @p5='2024-05-21T10:56:13.6695454+08:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='60']
INSERT INTO `T_System_User_Token` (`LastTokenDelayTime`, `RefreshToken`, `RefreshTokenExpiredTime`, `RowVersion`, `Token`, `TokenExpiredTime`)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT `Id`, `LastToken`
FROM `T_System_User_Token`
WHERE ROW_COUNT() = 1 AND ;
[10:41:13 ERR] An exception occurred in the database while saving changes for context type 'Faka.EntityFrameworkCore.Contexts.MySqlContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
   at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 357
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 350
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Further technical details

MySQL version: 8.2.0 Operating system: win10 Pomelo.EntityFrameworkCore.MySql version: 8.0.2 Microsoft.AspNetCore.App version: .net 8

I haven't tested the update and delete part yet as the insert is failing at the moment

lycoris-xmin avatar May 21 '24 02:05 lycoris-xmin