Export service not available on Version 22.12.1

paulo_sergio · January 12, 2023, 3:46pm

Dear all:

Again I am seeking your support because we are unable to use the data export functionality within the web server. When we ran the diagnostic, we got:

Also, I am sharing an excerpt of the log of the export service where the table interview__references is referenced. Any suggestions are welcome.

2023-01-09 13:05:19.314 +01:00 [INF] Application is shutting down...
2023-01-09 13:05:24.324 +01:00 [INF] [1] Cancellation requested. Stopping JobWorker
2023-01-09 13:05:24.326 +01:00 [INF] CancellationToken cancel request received.
2023-01-09 13:05:24.328 +01:00 [INF] Stopped background service: CleanupService
2023-01-09 13:05:24.328 +01:00 [INF] Stopped background service: WorkCancellationTrackService
2023-01-09 13:05:24.328 +01:00 [INF] Stopped background service: JobWorkersManageService
2023-01-09 13:12:56.017 +01:00 [INF] Job scheduler schema migration completed
2023-01-09 13:12:56.027 +01:00 [INF] Started background service: CleanupService
2023-01-09 13:12:56.027 +01:00 [INF] Started background service: WorkCancellationTrackService
2023-01-09 13:12:56.037 +01:00 [INF] [1] Start new worker
2023-01-09 13:12:56.092 +01:00 [INF] Started background service: JobWorkersManageService
2023-01-09 13:12:56.093 +01:00 [INF] Export service starting. version 22.12.1 (build 33634)
2023-01-09 13:12:56.099 +01:00 [INF] JobProgressReporterBackgroundService started
2023-01-09 13:12:56.105 +01:00 [INF] Now listening on: http://127.0.0.1:54251
2023-01-09 13:12:56.105 +01:00 [INF] Application started. Press Ctrl+C to shut down.
2023-01-09 13:12:56.105 +01:00 [INF] Hosting environment: Production
2023-01-09 13:12:56.105 +01:00 [INF] Content root path: e:\Survey Solutions\Site
2023-01-09 16:52:26.122 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
CREATE TABLE interview__references (
    interview_id uuid NOT NULL,
    questionnaire_id text NULL,
    CONSTRAINT "PK_interview__references" PRIMARY KEY (interview_id)
);
2023-01-09 16:52:26.150 +01:00 [ERR] Connection id "0HMNI7BBAFE5J", Request id "0HMNI7BBAFE5J:00000002": An unhandled exception was thrown by the application.
Npgsql.PostgresException (0x80004005): 42P07: relation "interview__references" already exists
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, 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.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at WB.Services.Export.Infrastructure.TenantDbContext.CheckSchemaVersionAndMigrate(CancellationToken cancellationToken)
   at WB.Services.Export.Infrastructure.TenantDbContext.EnsureMigrated(CancellationToken cancellationToken)
   at WB.Services.Export.Host.Jobs.PostgresDataExportProcessesService.GetAllProcessesAsync(Boolean runningOnly, CancellationToken token)
   at WB.Services.Export.Host.Controllers.JobController.GetAllJobsList(CancellationToken token)
   at lambda_method36074(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: relation "interview__references" already exists
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\catalog\heap.c
    Line: 1164
    Routine: heap_create_with_catalog
2023-01-09 16:52:28.483 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
CREATE TABLE interview__references (
    interview_id uuid NOT NULL,
    questionnaire_id text NULL,
    CONSTRAINT "PK_interview__references" PRIMARY KEY (interview_id)
);
2023-01-09 16:52:28.485 +01:00 [ERR] Connection id "0HMNI7BBAFE5K", Request id "0HMNI7BBAFE5K:00000002": An unhandled exception was thrown by the application.
Npgsql.PostgresException (0x80004005): 42P07: relation "interview__references" already exists
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, 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.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at WB.Services.Export.Infrastructure.TenantDbContext.CheckSchemaVersionAndMigrate(CancellationToken cancellationToken)
   at WB.Services.Export.Infrastructure.TenantDbContext.EnsureMigrated(CancellationToken cancellationToken)
   at WB.Services.Export.Host.Jobs.PostgresDataExportProcessesService.GetAllProcessesAsync(Boolean runningOnly, CancellationToken token)
   at WB.Services.Export.Host.Controllers.JobController.GetAllJobsList(CancellationToken token)
   at lambda_method36074(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: relation "interview__references" already exists
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\catalog\heap.c
    Line: 1164
    Routine: heap_create_with_catalog
2023-01-09 16:52:30.742 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
CREATE TABLE interview__references (
    interview_id uuid NOT NULL,
    questionnaire_id text NULL,
    CONSTRAINT "PK_interview__references" PRIMARY KEY (interview_id)
);

sergiy · January 12, 2023, 5:24pm

regarding the yellow status in the health check page, we don’t know what is the actual server address:

regarding the errors present in the log, please try the maintenance procedure M1 from: Procedures for maintenance

paulo_sergio · January 12, 2023, 7:59pm

Dear Sergiy:

The actual server address is:

We have run the M1 procedure where we removed the hq_ schemas:

And here are the logs generated during the procedure.
export-service.20230111.log:
export-service.20230111.log.pdf (3.8 MB)
headquarters.20230111.log
headquarters.20230111.log.pdf (43.3 KB)

Please, let me know if require additional information.
Regards

sergiy · January 17, 2023, 3:49pm

The procedure M1 was recommended on January 12, 2023, but the logs are from January 11, 2023.
Please show the fresh logs AFTER the procedure M1 was performed.

mark_pearson · January 19, 2023, 9:50am

Hello,

Apologies for jumping in here, but I was coming to open a new forum post but found this from Paolo, which is about the same instance I am currently looking at (https://lpomex-justicia.un.org/).

I am currently unable to attach new export service logs as I am a new user, but below is part of the output from the start of a file in the hope this will help:

> 2023-01-18 03:02:48.519 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
> CREATE TABLE interview__references (
>     interview_id uuid NOT NULL,
>     questionnaire_id text NULL,
>     CONSTRAINT "PK_interview__references" PRIMARY KEY (interview_id)
> );
> 2023-01-18 03:02:48.529 +01:00 [ERR] Connection id "0HMNOKM9LIHP6", Request id "0HMNOKM9LIHP6:00000002": An unhandled exception was thrown by the application.
> Npgsql.PostgresException (0x80004005): 3F000: no schema has been selected to create in
> 
> POSITION: 14
>    at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
>    at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
>    at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
>    at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
>    at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, 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.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
>    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
>    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
>    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
>    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
>    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
>    at WB.Services.Export.Infrastructure.TenantDbContext.CheckSchemaVersionAndMigrate(CancellationToken cancellationToken)
>    at WB.Services.Export.Infrastructure.TenantDbContext.EnsureMigrated(CancellationToken cancellationToken)
>    at WB.Services.Export.Host.Jobs.PostgresDataExportProcessesService.GetAllProcessesAsync(Boolean runningOnly, CancellationToken token)
>    at WB.Services.Export.Host.Controllers.JobController.GetAllJobsList(CancellationToken token)
>    at lambda_method2203(Closure , Object )
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
>    at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
>    at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
>   Exception data:
>     Severity: ERROR
>     SqlState: 3F000
>     MessageText: no schema has been selected to create in
>     Position: 14
>     File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\catalog\namespace.c
>     Line: 499
>     Routine: RangeVarGetCreationNamespace
> 2023-01-18 03:02:50.846 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
> CREATE TABLE interview__references (
>     interview_id uuid NOT NULL,
>     questionnaire_id text NULL,
>     CONSTRAINT "PK_interview__references" PRIMARY KEY (interview_id)
> );

This production instance is running 22.12.1, and is setup across two servers, one for application and one for database.

We also have a pre-production environment which is running 22.12 and is running both app and db on the same server. The export option on here is working as expected. I have asked about upgrading to the newer version to see if this breaks the export, but unfortunately I am not in charge of this process.

mark_pearson · January 19, 2023, 2:23pm

I appear to have fixed this, or at least the export option seems to be available again. I haven’t exported anything as it’s not my data to export.

I noticed that the TenantName setting in the appsettings.production file was set to uppercase ‘HQ’. In all historic files that we’ve kept for other upgrades, and on this site, I noticed it was always specified in lowercase ‘hq’.

We changed this, restarted the application, and it seems to have fixed it. I presume it’s therefore case sensitive. I will confirm once someone has told me this is definitely working.

sergiy · January 19, 2023, 5:09pm

Hello @mark_pearson ,

please treat the configuration parameters in appsettings.production.ini file as case-sensitive.

That said, I feel the bigger problem is that that file became modified. Survey Solutions doesn’t modify this settings file. Can you work backwards to trace who has actually modified it and why?

Best, Sergiy