AdoNet Clustering: Missing CleanupDefunctSiloEntriesKey query

[m3nax]

Problem:

When i run the project an exception was thrown

System.ArgumentException: 'Not all required queries found. Missing are: CleanupDefunctSiloEntriesKey

The CleanupDefunctSiloEntriesKey query is missing from the SQL script SQLServer-Clustering.sql, checked main and v7.2.2 branches

• https://github.com/dotnet/orleans/blob/main/src/AdoNet/Orleans.Clustering.AdoNet/SQLServer-Clustering.sql
• https://github.com/dotnet/orleans/blob/v7.2.2/src/AdoNet/Orleans.Clustering.AdoNet/SQLServer-Clustering.sql

Used configuration:

file project.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Orleans.Clustering.AdoNet" Version="7.2.2" />
    <PackageReference Include="Microsoft.Orleans.Persistence.AdoNet" Version="7.2.2" />
    <PackageReference Include="Microsoft.Orleans.Server" Version="7.2.2">
      <TreatAsUsed>true</TreatAsUsed>
    </PackageReference>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.5">
      <TreatAsUsed>true</TreatAsUsed>
    </PackageReference>
  </ItemGroup>

</Project>

file: program.cs

const string connectionString = "***";

var builder = WebApplication.CreateBuilder(args);

builder.Host.UseOrleans(siloBuilder =>
{
    siloBuilder
        .Configure<ClusterOptions>(options =>
        {
            options.ClusterId = "Cluster42";
            options.ServiceId = "MyAwesomeService";
        })
        .UseAdoNetClustering(options =>
        {
            options.ConnectionString = connectionString;
            options.Invariant = "System.Data.SqlClient";
        })
        .ConfigureEndpoints(siloPort: 11111, gatewayPort: 30000);

    siloBuilder.AddAdoNetGrainStorage("batt", options =>
    {
        options.Invariant = "System.Data.SqlClient";
        options.ConnectionString = connectionString;
    });
});

var app = builder.Build();

app.Run();

Guide used:

• https://learn.microsoft.com/en-us/dotnet/orleans/host/configuration-guide/typical-configurations#reliable-production-deployment-using-sql-server

Sql script used for Database creation:

• https://learn.microsoft.com/en-us/dotnet/orleans/host/configuration-guide/adonet-configuration#main-scripts
• https://learn.microsoft.com/en-us/dotnet/orleans/host/configuration-guide/adonet-configuration#clustering
• https://learn.microsoft.com/en-us/dotnet/orleans/host/configuration-guide/adonet-configuration#persistence
• https://learn.microsoft.com/en-us/dotnet/orleans/host/configuration-guide/adonet-configuration#reminders

Similar issue:

• CleanupDefunctSiloEntries key insert is missing in sql files #8216

[snovak7]

Migrations folder has it
https://github.com/dotnet/orleans/blob/main/src/AdoNet/Orleans.Clustering.AdoNet/Migrations/SQLServer-Clustering-3.7.0.sql

[m3nax]

Thanks, can I open a pull request to add the missing query to the clustering SQL script?
Anyone who starts using this project from version 7.2.x with clustering gets an error like me during startup.

[snovak7]

I don't know what's the idea here, ideally there would be migration that would automatically deploy tables, I do this separately with init container in Kubernetes, other would be as a startup task? @ReubenBond any ideas or goals for this?