Can't Automatically Removing Orphaned Tokens/Authorizations Cause MySql Error

[duc2475]

Is there an existing issue for this?

• I have searched the existing issues

Description

Please some one help me with this error
below is detail logs:

[3/23/2026 2:06 PM] Le Cuong: [07:06:12 INF] Lock is acquired for TokenCleanupBackgroundWorker
Mar 23 07:06:12 [07:06:12 INF] Start cleanup.
Mar 23 07:06:12 [07:06:12 INF] Start cleanup tokens.
Mar 23 07:06:12 [07:06:12 ERR] Failed executing DbCommand (18ms) [Parameters=[@__date_0='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='100']
Mar 23 07:06:12 DELETE FROM OpenIddictTokens AS o
Mar 23 07:06:12 WHERE o.Id IN (
Mar 23 07:06:12 SELECT o0.Id
Mar 23 07:06:12 FROM OpenIddictTokens AS o0
Mar 23 07:06:12 LEFT JOIN OpenIddictAuthorizations AS o1 ON o0.AuthorizationId = o1.Id
Mar 23 07:06:12 WHERE (o0.CreationDate < @__date_0) AND (((((o0.Status <> 'inactive') OR o0.Status IS NULL) AND ((o0.Status <> 'valid') OR o0.Status IS NULL)) OR (o1.Id IS NOT NULL AND ((o1.Status <> 'valid') OR o1.Status IS NULL))) OR (o0.ExpirationDate < UTC_TIMESTAMP()))
Mar 23 07:06:12 )
Mar 23 07:06:12 [07:06:12 ERR] You can't specify target table 'o' for update in FROM clause
Mar 23 07:06:12 MySql.Data.MySqlClient.MySqlException (0x80004005): You can't specify target table 'o' for update in FROM clause
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlStream.ReadPacketAsync(Boolean execAsync)
Mar 23 07:06:12 at MySql.Data.MySqlClient.NativeDriver.GetResultAsync(Int32 affectedRow, Int64 insertedId, Boolean execAsync)
Mar 23 07:06:12 at MySql.Data.MySqlClient.Driver.GetResultAsync(Int32 statementId, Int32 affectedRows, Int64 insertedId, Boolean execAsync)
Mar 23 07:06:12 at MySql.Data.MySqlClient.Driver.NextResultAsync(Int32 statementId, Boolean force, Boolean execAsync)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(Boolean execAsync, CancellationToken cancellationToken)
Mar 23 07:06:12 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Mar 23 07:06:12 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Mar 23 07:06:12 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Mar 23 07:06:12 at MySql.EntityFrameworkCore.Storage.Internal.MySQLExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)
Mar 23 07:06:12 at Volo.Abp.OpenIddict.Tokens.EfCoreOpenIddictTokenRepository.PruneAsync(DateTime date, CancellationToken cancellationToken)
Mar 23 07:06:12 at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
Mar 23 07:06:12 at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() Mar 23 07:06:12 at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation) Mar 23 07:06:12 at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
Mar 23 07:06:12 at Volo.Abp.OpenIddict.Tokens.AbpOpenIddictTokenStore.PruneAsync(DateTimeOffset threshold, CancellationToken cancellationToken)
[3/23/2026 2:06 PM] Le Cuong: Mar 23 07:06:12 at Volo.Abp.OpenIddict.Tokens.TokenCleanupService.CleanAsync()

Reproduction Steps

When ever TokenCleanupBackgroundWorker run this error appear

Expected behavior

I had older app version using abp 8.0 every thing works effectively but when i update abp version to 9.3 this error came out

Actual behavior

No response

Regression?

No response

Known Workarounds

No response

Version

9.3

User Interface

Common (Default)

Database Provider

EF Core (Default)

Tiered or separate authentication server

None (Default)

Operation System

Windows (Default)

Other information

No response

[maliming]

hi

This error is specific to MySQL — the DELETE ... WHERE Id IN (SELECT ... FROM same_table) syntax is not supported by MySQL, while SQL Server and PostgreSQL handle it without any issues.

This is actually a known limitation of the MySQL EF Core provider. EF Core's ExecuteDeleteAsync generates standard SQL, and the provider is responsible for translating it into MySQL-compatible SQL. The MySQL provider should rewrite the self-referencing subquery into a compatible form (e.g., using a temporary table), but it does not.

This issue has been discussed in the Pomelo.EntityFrameworkCore.MySql community:

• ExecuteDeleteAsync in combination with Take throws InvalidOperationException  PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1919

Could you let us know which MySQL EF Core provider and version you are using? We recommend reporting this issue to the provider's repository.

As a workaround, you can override the PruneAsync method in your own repository implementation to avoid the self-referencing subquery:

public override async Task<long> PruneAsync(DateTime date, CancellationToken cancellationToken = default)
{
    var tokenIds = await (from token in await GetQueryableAsync()
            join authorization in (await GetDbContextAsync()).Set<OpenIddictAuthorization>()
                on token.AuthorizationId equals authorization.Id into tokenAuthorizations
            from tokenAuthorization in tokenAuthorizations.DefaultIfEmpty()
            where token.CreationDate < date
            where (token.Status != OpenIddictConstants.Statuses.Inactive && token.Status != OpenIddictConstants.Statuses.Valid) ||
                  (tokenAuthorization != null && tokenAuthorization.Status != OpenIddictConstants.Statuses.Valid) ||
                  token.ExpirationDate < DateTime.UtcNow
            select token.Id)
        .ToListAsync(GetCancellationToken(cancellationToken));

    return await (await GetDbSetAsync()).Where(x => tokenIds.Contains(x.Id)).ExecuteDeleteAsync(GetCancellationToken(cancellationToken));
}

This splits the operation into two steps: first query the IDs to delete, then delete by those IDs, which avoids the MySQL limitation.

Thanks.

[duc2475]

I'm using Volo.Abp.EntityFrameworkCore.MySQL Version=9.3.6

[maliming]

Thanks for the information. The Volo.Abp.EntityFrameworkCore.MySQL package depends on Oracle's official MySql.EntityFrameworkCore provider, which does not handle the MySQL limitation of self-referencing subqueries in DELETE statements (MySQL Error 1093).

This is a well-known MySQL database limitation: https://bugs.mysql.com/bug.php?id=5037

We recommend using the workaround mentioned above to override the PruneAsync method in your own repository implementation. You can also consider reporting this issue to the MySql.EntityFrameworkCore provider repository.

[duc2475]

Thanks, I'll try to override the PruneAsync method. Once again thank for your supporting.