Not able to export data

We are not able to export data. We get this “Loading. Please wait” message on the data export page.

The diagnostic page shows this “No connection to export services”.

How do I get them connected again?

We restarted the server, that fixed it.

And they are disconnected again…

Restarting the server didn’t help this time.

Is there any errors in headquarters log file?

The export service log errors attached below:

2020-09-22 12:32:22.378 +05:30 [ERR] [1] Job worker got an exception. Log, ignore and continue working
Npgsql.NpgsqlException (0x80004005): Exception while writing to stream
 ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
 ---> System.Net.Sockets.SocketException (10054): An existing connection was forcibly closed by the remote host.
   --- End of inner exception stack trace ---
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlCommand.SendExecute(NpgsqlConnector connector, Boolean async)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(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.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobWorker.StartAsync(CancellationToken token)
2020-09-22 12:32:22.391 +05:30 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
select pg_advisory_xact_lock (-777)
2020-09-22 12:32:22.392 +05:30 [ERR] [1] Job worker got an exception. Log, ignore and continue working
Npgsql.NpgsqlException (0x80004005): Exception while writing to stream
 ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
 ---> System.Net.Sockets.SocketException (10054): An existing connection was forcibly closed by the remote host.
   --- End of inner exception stack trace ---
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlCommand.SendExecute(NpgsqlConnector connector, Boolean async)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(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.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobWorker.StartAsync(CancellationToken token)
2020-09-22 12:32:22.395 +05:30 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
select pg_advisory_xact_lock (-777)
2020-09-22 12:32:22.395 +05:30 [ERR] [1] Job worker got an exception. Log, ignore and continue working
Npgsql.NpgsqlException (0x80004005): Exception while writing to stream
 ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
 ---> System.Net.Sockets.SocketException (10054): An existing connection was forcibly closed by the remote host.
   --- End of inner exception stack trace ---
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlWriteBuffer.Flush(Boolean async)
   at Npgsql.NpgsqlCommand.SendExecute(NpgsqlConnector connector, Boolean async)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(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.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobService.GetFreeJobAsync(CancellationToken token)
   at WB.Services.Scheduler.Services.Implementation.JobWorker.StartAsync(CancellationToken token)
2020-09-22 12:32:23.415 +05:30 [ERR] An error occurred using the connection to database 'SurveySolutions' on server 'tcp://127.0.0.1:5432'.

This was followed by the following logs, and the export services are working fine since.

2020-09-22 12:33:14.657 +05:30 [INF] Job scheduler schema migration completed
2020-09-22 12:33:14.667 +05:30 [INF] Started background service: CleanupService
2020-09-22 12:33:14.667 +05:30 [INF] Started background service: WorkCancellationTrackService
2020-09-22 12:33:14.672 +05:30 [INF] [1] Start new worker
2020-09-22 12:33:14.687 +05:30 [INF] Started background service: JobWorkersManageService
2020-09-22 12:33:14.688 +05:30 [INF] Export service starting. version 20.09.0.2346
2020-09-22 12:33:14.693 +05:30 [INF] JobProgressReporterBackgroundService started
2020-09-22 12:33:14.701 +05:30 [INF] Now listening on: http://127.0.0.1:49685
2020-09-22 12:33:14.701 +05:30 [INF] Application started. Press Ctrl+C to shut down.
2020-09-22 12:33:14.702 +05:30 [INF] Hosting environment: Production
2020-09-22 12:33:14.702 +05:30 [INF] Content root path: C:\Survey Solutions\Site
2020-09-22 13:10:55.192 +05:30 [INF] Application is shutting down...
2020-09-22 13:41:52.536 +05:30 [INF] Job scheduler schema migration completed
2020-09-22 13:41:52.546 +05:30 [INF] Started background service: CleanupService
2020-09-22 13:41:52.546 +05:30 [INF] Started background service: WorkCancellationTrackService
2020-09-22 13:41:52.552 +05:30 [INF] [1] Start new worker
2020-09-22 13:41:52.561 +05:30 [INF] Started background service: JobWorkersManageService
2020-09-22 13:41:52.562 +05:30 [INF] Export service starting. version 20.09.0.2346
2020-09-22 13:41:52.565 +05:30 [INF] JobProgressReporterBackgroundService started
2020-09-22 13:41:52.576 +05:30 [INF] Now listening on: http://127.0.0.1:51066
2020-09-22 13:41:52.576 +05:30 [INF] Application started. Press Ctrl+C to shut down.
2020-09-22 13:41:52.576 +05:30 [INF] Hosting environment: Production
2020-09-22 13:41:52.576 +05:30 [INF] Content root path: C:\Survey Solutions\Site

First error indicates that connection string to database is wrong

We haven’t faced the same issue again. Export services are working. Should we be worried or do something?

Dear Ashwini,

I think if we rephrase Andrew’s answer as “the HQ couldn’t reach the database using the stored connection string” rather then "the connection string to database is wrong" then yes, you should be worried if the database is sometimes reachable and sometimes not. Yet the cause of that temporal or selective availability is likely outside of Survey Solutions scope. To give you an example: I have had exactly the same message thrown at me in a different situation and it turned out that in that particular network the network administrator had to approve/clear the access to a particular resource, even though that is publicly available and outside of that network.

Best, Sergiy

The database and the SS are on the same server. I will have a technical person have a look at it. Thanks.

1 Like

Just make sure that connection between database and survey solutions server is not broken. Also such error may occur while export service is being stopped. Not an issue if it is working now