AspNetCore.Diagnostics.HealthChecks icon indicating copy to clipboard operation
AspNetCore.Diagnostics.HealthChecks copied to clipboard
verified publisher icon Xabaril

Database exception when using AspNetCore.HealthChecks.NpgSql with EFCore.NamingConventions

Open kamil-oberaj opened this issue 2 years ago • 3 comments

Hey! What happened:

While running the .NET 8 application I got the error: Npgsql.PostgresException (0x80004005): 42P01: relation "configurations" does not exist.

The error is thrown when using method from AspNetCore.HealthChecks.NpgSql. Something like that:

builder.Services
    .AddHealthChecksUI()
    .AddPostgreSqlStorage(
        databaseConfiguration.GetReadConnectionString(),
        options =>
        {
            options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
            options.UseSnakeCaseNamingConvention(); // this line breaks the whole code 
        })
    .AddPostgreSqlStorage(databaseConfiguration.GetWriteConnectionString(),
        options =>
        {
            options.UseSnakeCaseNamingConvention(); // this line breaks the whole code
        });

Exception stack:

info: HealthChecks.UI.Core.HostedService.UIInitializationHostedService[0]
      Initializing UI Database
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT EXISTS (
          SELECT 1
          FROM configurations AS c)
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'HealthChecks.UI.Data.HealthChecksDb'.
      Npgsql.PostgresException (0x80004005): 42P01: relation "configurations" does not exist

      POSITION: 41
         at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "configurations" does not exist
          Position: 41
          File: parse_relation.c
          Line: 1452
          Routine: parserOpenTable
      Npgsql.PostgresException (0x80004005): 42P01: relation "configurations" does not exist

      POSITION: 41
         at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "configurations" does not exist
          Position: 41
          File: parse_relation.c
          Line: 1452
          Routine: parserOpenTable
fail: Microsoft.Extensions.Hosting.Internal.Host[11]
      Hosting failed to start
      Npgsql.PostgresException (0x80004005): 42P01: relation "configurations" does not exist

      POSITION: 41
         at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
         at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
         at HealthChecks.UI.Core.HostedService.UIInitializationHostedService.InitializeDatabaseAsync(IServiceProvider sp) in /_/src/HealthChecks.UI/Core/HostedService/UIInitializationHostedService.cs:line 62
         at HealthChecks.UI.Core.HostedService.UIInitializationHostedService.StartAsync(CancellationToken cancellationToken) in /_/src/HealthChecks.UI/Core/HostedService/UIInitializationHostedService.cs:line 34
         at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
         at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "configurations" does not exist
          Position: 41
          File: parse_relation.c
          Line: 1452
          Routine: parserOpenTable
Unhandled exception. Npgsql.PostgresException (0x80004005): 42P01: relation "configurations" does not exist

POSITION: 41
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at HealthChecks.UI.Core.HostedService.UIInitializationHostedService.InitializeDatabaseAsync(IServiceProvider sp) in /_/src/HealthChecks.UI/Core/HostedService/UIInitializationHostedService.cs:line 62
   at HealthChecks.UI.Core.HostedService.UIInitializationHostedService.StartAsync(CancellationToken cancellationToken) in /_/src/HealthChecks.UI/Core/HostedService/UIInitializationHostedService.cs:line 34
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
   at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Program.<Main>$(String[] args) in C:\Users\Sh0w3D\RiderProjects\XXX-XXX\XXX.Api\Program.cs:line 94
   at Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: relation "configurations" does not exist
    Position: 41
    File: parse_relation.c
    Line: 1452
    Routine: parserOpenTable

Process finished with exit code -532,462,766.

I am entirely sure that adding this convention would require creating a migration.

What you expected to happen:

The convention should be applied and app should run without any error.

How to reproduce it (as minimally and precisely as possible):

Create API project with class library, add 2 database instances - one for read and one for write. Add HealthChecks for both instances, they should use the UseSnakeCaseNamingConvention() method. NuGet references below

Source code sample:

Service registration:

  1. Add both databases using AddDbContextFactory:
var databaseConfiguration = configuration.GetRequiredSection(DatabaseConfiguration.JsonNode)
    .Get<DatabaseConfiguration>();

ArgumentNullException.ThrowIfNull(databaseConfiguration);

services.AddDbContextFactory<XXXReadDbContext>((_, options) =>
{
    options.UseNpgsql(databaseConfiguration.GetReadConnectionString(),
            opt => { opt.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery); })
        .UseSnakeCaseNamingConvention()
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}, ServiceLifetime.Scoped);
services.AddDbContextFactory<XXXWriteDbContext>(
    (_, options) =>
    {
        options.UseNpgsql(databaseConfiguration.GetWriteConnectionString(),
                opt => { opt.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery); })
            .UseSnakeCaseNamingConvention();
    }, ServiceLifetime.Scoped);

return services;
  1. Register HealthChecks.UI:
var databaseConfiguration = builder.Configuration.GetRequiredSection(DatabaseConfiguration.JsonNode)
    .Get<DatabaseConfiguration>();

ArgumentNullException.ThrowIfNull(databaseConfiguration);

builder.Services
    .AddHealthChecksUI()
    .AddPostgreSqlStorage(
        databaseConfiguration.GetReadConnectionString(),
        options =>
        {
            options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
            options.UseSnakeCaseNamingConvention(); // this line breaks the whole code
        })
    .AddPostgreSqlStorage(databaseConfiguration.GetWriteConnectionString(),
        options =>
        {
            options.UseSnakeCaseNamingConvention(); // this line breaks the whole code
        });
  1. Register actual HealthChecks:
builder.Services.AddHealthChecks()
    .AddNpgSql(databaseConfiguration.GetReadConnectionString(),
        name: "Read Database")
    .AddNpgSql(databaseConfiguration.GetWriteConnectionString(),
        name: "Write Database");
  1. After builder.Build() register app.HealthChecks
app.UseHealthChecks("/health");
app.UseHttpsRedirection();

app
    .UseRouting()
    .UseEndpoints(config =>
    {
        config.MapHealthChecks("/health", new HealthCheckOptions
        {
            Predicate = _ => true,
            ResponseWriter = UIResponseWriter.WriteHealthCheckUIResponse
        });
    });

Anything else we need to know?:

Project configuration: API:

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="AspNetCore.HealthChecks.NpgSql" Version="8.0.0" />
    <PackageReference Include="AspNetCore.HealthChecks.UI" Version="8.0.0" />
    <PackageReference Include="AspNetCore.HealthChecks.UI.Client" Version="8.0.0" />
    <PackageReference Include="AspNetCore.HealthChecks.UI.PostgreSQL.Storage" Version="8.0.0" />
    <PackageReference Include="MediatR" Version="12.2.0" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
      <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="8.0.1"/>
      <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.1">
          <PrivateAssets>all</PrivateAssets>
          <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      </PackageReference>
      <PackageReference Include="Swashbuckle.AspNetCore.Filters" Version="8.0.0"/>
  </ItemGroup>
</Project>

DataStorage:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
      <PackageReference Include="LinqKit" Version="1.2.5"/>
    <PackageReference Include="MediatR" Version="12.2.0" />
    <PackageReference Include="EFCore.NamingConventions" Version="8.0.3"/>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="8.0.1"/>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.1"/>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Extensions.Identity.Stores" Version="8.0.1"/>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.1"/>
    <PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="8.0.1"/>
    <PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="8.0.0"/>
  </ItemGroup>
</Project>

Environment:

  • .NET version: 8
  • Healthchecks version:
Include="AspNetCore.HealthChecks.NpgSql" Version="8.0.0" />
Include="AspNetCore.HealthChecks.UI" Version="8.0.0" />
Include="AspNetCore.HealthChecks.UI.Client" Version="8.0.0" />
Include="AspNetCore.HealthChecks.UI.PostgreSQL.Storage" Version="8.0.0" />
  • Operative system: Windows 11
  • Others:
  • Postgres 16

kamil-oberaj avatar Jan 21 '24 21:01 kamil-oberaj

I just bumped into this issue myself. @kamil-oberaj - Do you have a workaround for this issue yet, or are you still stuck?

willibrandon avatar Oct 25 '24 19:10 willibrandon

Same setup as you and same issue. The tables and columns made by the migration use pascal case but when the health checks data context queries for data it then uses the snake case naming convention.

davenport1 avatar Oct 25 '24 19:10 davenport1

It appears migrations are custom per db provider. Afaik this can't be done in any other way than to make the table and column names snake_case in their migrations, for example the initial migration is here https://github.com/Xabaril/AspNetCore.Diagnostics.HealthChecks/blob/master/src/HealthChecks.UI.PostgreSQL.Storage/Migrations/20200410133103_initial.cs

this is not a huge deal and I could prepare PR for it, but the authors should let us know if it's ok to do it this way

brgrz avatar Nov 11 '24 09:11 brgrz