BatchDelete fails on Linux/Ubuntu & MySql

[muratcakir]

Partial stack trace:

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 ''Log' AUTO_INCREMENT = 1' at line 1
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 910
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 107
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 450
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 264
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)
...

The caller is quite simple:

var numDeleted = await _db.Logs
    .Where(x => x.CreatedOnUtc <= maxAgeUtc && x.LogLevelId < (int)maxLevel)
    .BatchDeleteAsync(cancelToken);

The entity:

/// <summary>
/// Represents a log level
/// </summary>
public enum LogLevel
{
    Verbose = 0,
    Debug = 10,
    Information = 20,
    Warning = 30,
    Error = 40,
    Fatal = 50
}

internal class LogMap : IEntityTypeConfiguration<Log>
{
    public void Configure(EntityTypeBuilder<Log> builder)
    {
        builder.HasOne(c => c.Customer)
            .WithMany()
            .HasForeignKey(c => c.CustomerId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

/// <summary>
/// Represents a log record
/// </summary>
[Index(nameof(Logger), Name = "IX_Log_Logger")]
[Index(nameof(LogLevelId), Name = "IX_Log_Level")]
[Index(nameof(CreatedOnUtc), Name = "IX_Log_CreatedOnUtc")]
[Hookable(false)]
[CacheableEntity(NeverCache = true)]
public partial class Log : BaseEntity
{
    public Log()
    {
    }

    private Log(ILazyLoader lazyLoader) 
        : base(lazyLoader)
    {
    }

    /// <summary>
    /// Gets or sets the log level identifier
    /// </summary>
    public int LogLevelId { get; set; }

    /// <summary>
    /// Gets or sets the short message
    /// </summary>
    [Required, StringLength(4000)]
    public string ShortMessage { get; set; }

    /// <summary>
    /// Gets or sets the full exception
    /// </summary>
    [MaxLength]
    public string FullMessage { get; set; }

    /// <summary>
    /// Gets or sets the IP address
    /// </summary>
    [StringLength(200)]
    public string IpAddress { get; set; }

    /// <summary>
    /// Gets or sets the customer identifier
    /// </summary>
    public int? CustomerId { get; set; }

    /// <summary>
    /// Gets or sets the page URL
    /// </summary>
    [StringLength(1500)]
    public string PageUrl { get; set; }

    /// <summary>
    /// Gets or sets the referrer URL
    /// </summary>
    [StringLength(1500)]
    public string ReferrerUrl { get; set; }

    /// <summary>
    /// Gets or sets the date and time of instance creation
    /// </summary>
    public DateTime CreatedOnUtc { get; set; }

    /// <summary>
    /// Gets or sets the logger name
    /// </summary>
    [Required, StringLength(400)]
    public string Logger { get; set; }

    /// <summary>
    /// Gets or sets the HTTP method
    /// </summary>
    [StringLength(10)]
    public string HttpMethod { get; set; }

    /// <summary>
    /// Gets or sets the user name
    /// </summary>
    [StringLength(100)]
    public string UserName { get; set; }

    /// <summary>
    /// Gets or sets the log level
    /// </summary>
    [NotMapped]
    public LogLevel LogLevel
    {
        get => (LogLevel)LogLevelId;
        set => LogLevelId = (int)value;
    }

    private Customer _customer;
    /// <summary>
    /// Gets or sets the customer
    /// </summary>
    public Customer Customer
    {
        get => _customer ?? LazyLoader.Load(this, ref _customer);
        set => _customer = value;
    }
}

SQL schema (from SMO):

CREATE TABLE [dbo].[Log](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LogLevelId] [int] NOT NULL,
    [ShortMessage] [nvarchar](4000) NOT NULL,
    [FullMessage] [nvarchar](max) NULL,
    [IpAddress] [nvarchar](200) NULL,
    [CustomerId] [int] NULL,
    [PageUrl] [nvarchar](1500) NULL,
    [ReferrerUrl] [nvarchar](1500) NULL,
    [CreatedOnUtc] [datetime] NOT NULL,
    [Logger] [nvarchar](400) NOT NULL,
    [HttpMethod] [nvarchar](10) NULL,
    [UserName] [nvarchar](100) NULL,
 CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Log] ADD  DEFAULT ('') FOR [Logger]
GO

ALTER TABLE [dbo].[Log]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Log_dbo.Customer_CustomerId] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Log] CHECK CONSTRAINT [FK_dbo.Log_dbo.Customer_CustomerId]
GO

[yang-er]

Can you also share the version you are using, and the failed SQL command translated by this plugin?

BTW this seems not related to this plugin, this plugin doesn’t use RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync in published versions.

[muratcakir]

False alarm. Sorry to have bothered you with this. I picked the wrong log entry, guess I was too tired :-) Of course it works:

DELETE `l`
FROM `Log` AS `l`
WHERE (`l`.`CreatedOnUtc` <= timestamp('2022-01-08 11:03:09.417538')) AND (`l`.`LogLevelId` < 40)