Phase 1: ***ERROR*** In ExecuteSQL; Exception while reading from stream, SQL was: update ad.studies k

[michelescarlato]

08/09/2024 : 07:05 :   Transferred 969519 study record data, from aggs DB to IEC DB, in total

+++++++++++++++++++++++++++++++++++++++
08/09/2024 : 07:05 :   ***ERROR*** In ExecuteSQL; Exception while reading from stream, 
SQL was: update ad.studies k
               set display_title = s.display_title, 
               brief_description = s.brief_description, 
               iec_level_id = s.iec_level,               
               study_start_year = s.study_start_year, study_start_month = s.study_start_month, 
               study_type_id = s.study_type_id, study_enrolment = s.study_enrolment, 
               study_gender_elig_id = s.study_gender_elig_id, 
               min_age = s.min_age, min_age_units_id = s.min_age_units_id,
               max_age = s.max_age, max_age_units_id = s.max_age_units_id, 
               datetime_of_data_fetch = k.datetime_of_data_fetch
            from aggs_st.studies s
            where k.study_id = s.id  and k.id >= 1 and k.id < 25001 
+++++++++++++++++++++++++++++++++++++++

08/09/2024 : 07:05 :   Updated 0 study records, with study details, ids 1 to 25000

[michelescarlato]

AG -I 2024-09-08 070001.log

[michelescarlato]

Same error also:

• 1st September,
• 25th August,
• 11th August

The 18th August, and 4th August run were ok. Also 28th July runs without errors.

22nd July I can see the same error mentioned in #13.

[michelescarlato]

AG -I 2024-07-22 152258.log AG -I 2024-07-22 175645.log AG -I 2024-07-22 193537.log AG -I 2024-07-23 091716.log AG -I 2024-07-23 114218.log AG -I 2024-07-28 070001.log AG -I 2024-08-04 070001.log AG -I 2024-08-11 070001.log AG -I 2024-08-18 070001.log AG -I 2024-08-25 070001.log AG -I 2024-08-30 211744.log AG -I 2024-08-31 120650.log AG -I 2024-09-01 070001.log AG -I 2024-09-08 070001.log AG -J 2023-09-08 192743.log

[michelescarlato]

Still same error for 8th and 15th September.

08/09/2024 : 07:05 :   Transferred 969519 study record data, from aggs DB to IEC DB, in total

+++++++++++++++++++++++++++++++++++++++
08/09/2024 : 07:05 :   ***ERROR*** In ExecuteSQL; Exception while reading from stream, 
SQL was: update ad.studies k
               set display_title = s.display_title, 
               brief_description = s.brief_description, 
               iec_level_id = s.iec_level,               
               study_start_year = s.study_start_year, study_start_month = s.study_start_month, 
               study_type_id = s.study_type_id, study_enrolment = s.study_enrolment, 
               study_gender_elig_id = s.study_gender_elig_id, 
               min_age = s.min_age, min_age_units_id = s.min_age_units_id,
               max_age = s.max_age, max_age_units_id = s.max_age_units_id, 
               datetime_of_data_fetch = k.datetime_of_data_fetch
            from aggs_st.studies s
            where k.study_id = s.id  and k.id >= 1 and k.id < 25001 
+++++++++++++++++++++++++++++++++++++++

08/09/2024 : 07:05 :   Updated 0 study records, with study details, ids 1 to 25000

15/09/2024 : 07:05 :   Transferred 970448 study record data, from aggs DB to IEC DB, in total

+++++++++++++++++++++++++++++++++++++++
15/09/2024 : 07:06 :   ***ERROR*** In ExecuteSQL; Exception while reading from stream, 
SQL was: update ad.studies k
               set display_title = s.display_title, 
               brief_description = s.brief_description, 
               iec_level_id = s.iec_level,               
               study_start_year = s.study_start_year, study_start_month = s.study_start_month, 
               study_type_id = s.study_type_id, study_enrolment = s.study_enrolment, 
               study_gender_elig_id = s.study_gender_elig_id, 
               min_age = s.min_age, min_age_units_id = s.min_age_units_id,
               max_age = s.max_age, max_age_units_id = s.max_age_units_id, 
               datetime_of_data_fetch = k.datetime_of_data_fetch
            from aggs_st.studies s
            where k.study_id = s.id  and k.id >= 1 and k.id < 25001 
+++++++++++++++++++++++++++++++++++++++

15/09/2024 : 07:06 :   Updated 0 study records, with study details, ids 1 to 25000

[sergiocontrino]

from @iPr0ger :

In Dapper (C# Micro ORM which is used in the aggregator) when it establishes SQL connection, it uses a connection string. In this connection string there are parameters: CommandTimeout, KeepAlive and Timeout. So the possible issue here could be that it breaks connection with the database before the command (transaction) can be executed because of these timeouts. So try to increase these timeouts and the issue could be resolved. Hope it will help :) The code with the connection string creation is in the 'Credentials.cs' file:

It already has the 'KeepAlive' parameter, so try to play around with other timeout parameters.

[michelescarlato]

Thanks for the suggestion.

I added

public string GetConnectionString(string database_name)
    {
        NpgsqlConnectionStringBuilder builder = new()
        {
            Host = _host,
            Username = _username,
            Password = _password,
            Port = _port,
            Database = database_name,
            KeepAlive = 300,
            IncludeErrorDetail = true,
            // Set Command Timeout here (in seconds)
            CommandTimeout = 300 // 5 minutes
        };
        return builder.ConnectionString;
    }

Yet, same error. I will increase the timeout.

AG -I 2024-09-16 160521.log

[michelescarlato]

Increasing to 600 KeepAlive and CommandTimeout fixed it. AG -I 2024-09-16 164953.log

public string GetConnectionString(string database_name)
    {
        NpgsqlConnectionStringBuilder builder = new()
        {
            Host = _host,
            Username = _username,
            Password = _password,
            Port = _port,
            Database = database_name,
            KeepAlive = 600,
            IncludeErrorDetail = true,
            // Set Command Timeout here (in seconds)
            CommandTimeout = 600 // 5 minutes
        };
        return builder.ConnectionString;
    }