Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – An exception occurred while iterating over the results of a query for context type 'mytype'

In production, my ASP.NET project works well and returns the query below, in about 12 seconds:

        [HttpGet("suggest/{id1}/{id2}/{id3}")]
        public async Task<List<int>> GetRecommendedShows(int id1, int id2, int id3)
        {
            List<int> showIds = new List<int>() { id1, id2, id3 };

            // Fetch all shows from the database
            var allShows = await _context.Shows
                .Select(s => new { s.Id, s.VectorDouble })
                .ToListAsync();

            // Filter shows by the specified IDs
            var selectedShows = allShows.Where(s => showIds.Contains(s.Id)).ToList();

            if (selectedShows.Any())
            {
                // Calculate average vector
                double[] averageVector = VectorEngine.CalculateAverageVector(selectedShows.Select(s => s.VectorDouble).ToList());

                // Calculate similarities and get recommended show IDs
                List<int> recommendedShowIds = VectorEngine.GetSimilarities(allShows.Select(s => new ShowInfo { Id = s.Id, VectorDouble = s.VectorDouble }).ToList(), averageVector, 8);

                return recommendedShowIds;
            }
            else
            {
                // Return the input IDs if no shows are found
                return showIds;
            }
        }

However in the Ubuntu Cloud production server, after query for searching works at /api/input ( so server works ), it gets a timeout exception after about 20 seconds. This is the stack trace:

  FROM [Shows] AS [s]
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:       WHERE [s].[Name] LIKE @__input_0_rewritten ESCAPE N''
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:       ORDER BY CASE
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:           WHEN [s].[Name] LIKE @__input_0_rewritten ESCAPE N'' AND [s].[Name] IS NOT NULL THEN CAST(1 AS bit)
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:           ELSE CAST(0 AS bit)
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:       END DESC, CASE
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:           WHEN @__input_0 = N'' THEN 0
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:           ELSE CAST(CHARINDEX(@__input_0, [s].[Name]) AS int) - 1
Mar 16 18:53:14 debian-2gb-hel1-4 recommendit[26886]:       END DESC
Mar 16 18:53:19 debian-2gb-hel1-4 recommendit[26886]: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Mar 16 18:53:19 debian-2gb-hel1-4 recommendit[26886]:       Executed DbCommand (41ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
Mar 16 18:53:19 debian-2gb-hel1-4 recommendit[26886]:       SELECT [s].[Id], [s].[VectorDouble]
Mar 16 18:53:19 debian-2gb-hel1-4 recommendit[26886]:       FROM [Shows] AS [s]
Mar 16 18:54:19 debian-2gb-hel1-4 recommendit[26886]: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Mar 16 18:54:19 debian-2gb-hel1-4 recommendit[26886]:       Executed DbCommand (123ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
Mar 16 18:54:19 debian-2gb-hel1-4 recommendit[26886]:       SELECT [s].[Id], [s].[VectorDouble]
Mar 16 18:54:19 debian-2gb-hel1-4 recommendit[26886]:       FROM [Shows] AS [s]
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]: fail: Microsoft.EntityFrameworkCore.Query[10100]
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       An exception occurred while iterating over the results of a query for context type 'ShowPulse.Models.ShowContext'.
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 2 - Connection was terminated: Connection was terminated)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:        ---> System.ComponentModel.Win32Exception (258): Unknown error 258
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.Data.SqlClient.TdsParserStateObject.ReadAsyncCallbackCaptureException(TaskCompletionSource`1 source)
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:       --- End of stack trace from previous location ---
Mar 16 18:55:47 debian-2gb-hel1-4 recommendit[26886]:          at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellationToken)

at ShowPulse.Controllers.ShowsController.GetRecommendedShows(Int32 id1, Int32 id2, Int32 id3) in D:consoleProjectsShowPulseControllersShowsController.cs:line 6

full log here: https://pastebin.com/BX9UntZk

I’ve tried optimizing the query, since I thought this was a problem.

I also tried increasing timeout time:

builder.Services.AddDbContext<ShowContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("prodConnection"), sqlOptions =>
    {
        // Set the command timeout (in seconds)
        sqlOptions.CommandTimeout(120); 

        // Enable retry on failure (if needed)
        sqlOptions.EnableRetryOnFailure();
    });
});
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution was simply, in my case, to rescale my Ubuntu server to a higher RAM. I realized mine was using 2GB, when it probably needed at least 4GB to run a query on over 100 000 records. Rewriting the query did not seem to help in my case.


  2. 0

    Ok, so you ran a query with a 30 second CommandTimeout

    At Mar 16 18:54:19

    CommandType=’Text’, CommandTimeout=’30’]Executed DbCommand (123ms) [Parameters=[],

    And later it timed out.

    At Mar 16 18:55:47

    Execution Timeout Expired. The timeout period
    elapsed prior to completion of the operation or the server is not
    responding.

    The command timeout includes waiting for rows to be fetched, not just the wait for the first row. So it’s taking more than 30sec to run your query and fetch all the rows.

    So you need to figure out why this is taking so long, or run a query that doesn’t need so much time to execute and return the results.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search