Exception while reading from stream postgresql как исправить

I am getting random error messages in my application log with the following stack trace. Most of the time everything is working well. I get about 10 errors and log messages like this a day in the production system. How do I figure out what is going on?

The issue

ERROR [18] 
 Npgsql.NpgsqlException (0x80004005): Exception while reading from 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: An existing connection was forcibly closed by the remote host
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    --- End of inner exception stack trace ---
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass31_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
    at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass31_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
 --- End of stack trace from previous location where exception was thrown ---
    at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
 --- End of stack trace from previous location where exception was thrown ---
    at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
 --- End of stack trace from previous location where exception was thrown ---
    at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
    at Npgsql.NpgsqlDataReader.NextResult()
    at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:projectsdapperDapperSqlMapper.cs:line 1051
    at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in C:projectsdapperDapperSqlMapper.cs:line 1079
    at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:projectsdapperDapperSqlMapper.cs:line 721
    at Dapper.FastCrud.SqlStatements.GenericSqlStatements`1.BatchSelect(IDbConnection connection, AggregatedSqlStatementOptions`1 statementOptions) in D:a1sDapper.FastCrudSqlStatementsGenericSqlStatements.cs:line 297
    at XXX.Core.Data.SqlRepository`1.FirstOrDefault(String where, Object param, String order)
    at XXXXX.App.Worker.Tasks.XXX.Process() in C:DropboxdevSrcshopXXXTasksShopifyWebhookTask.cs:line 13
    at XXX.App.Worker.TaskBase`1.ProcessMessage(String message) in C:DropboxdevSrcshopXXXTaskBase.cs:line 30
    at Shop.Core.Common.Queue.BaseTaskProcessor`1.Process(QueueMessage message) in C:DropboxdevSrcshopXXXQueueBaseTaskProcessor.cs:line 38

Exception message:
Stack trace:

Npgsql version: 4.0.10
PostgreSQL version: 10.10
Operating system: Azure Windows Function + Azure Managed PostgresSql.

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream #2962

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
—> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0. d.MoveNext()
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0. d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0. d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0. d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at LinqToDB.Data.DataConnection.ExecuteNonQuery()
at LinqToDB.Data.DataConnection.QueryRunner.ExecuteScalarImpl(DataConnection dataConnection, PreparedQuery preparedQuery)
at LinqToDB.Linq.QueryRunner.ScalarQuery(Query query, IDataContext dataContext, Expression expr, Object[] parameters)
at LinqToDB.Linq.QueryRunner.InsertWithIdentity`1.Query(IDataContext dataContext, T obj, String tableName, String databaseName, String schemaName)

The text was updated successfully, but these errors were encountered:

Thank you for reporting an issue, but there is not enough information to understand what’s happening. So please provide a minimal code example to reproduce the problem.

I get this error multiple times randomly while querying my database. I have used Linq2DB for db connection.

I tried by removing onresetonclose but no luck there.

Are you using pgbouncer as a connection pooler?

We have the same issue using pgbouncer instance between our .NET Core 3.1 app and the Postgres Server.

Could you send your select your are trying to execute?

Please see my issue if is not the same problem: #2966

Guys, there is nothing we can do with a simple exception message. Please check the PostgreSQL logs around the time of the exception, and if you suspect a real problem in Npgsql, then as @YohDeadfall wrote we need to see a runnable code sample that demonstrates the problem. Otherwise we can’t help you.

Am closing this as no code samples have been provided, and we can’t really do anything to investigate. If someone posts a code sample or at least some clear instructions on how to repro this, we can reopen and investigate.

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

Exception while reading from stream #4532

Before submitting

This repo is for Npgsql ADO.NET issues only. My code is working fine while debugging. We are deploying the code in PCF.
It throws an exception while trying open the connection to PostgreSQL Server in Linux.

Steps to reproduce

The code is having issues while trying to open the connection.

The issue

Describe what is not working as expected.

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
—> System.TimeoutException: Timeout during reading attempt
Npgsql.Internal.NpgsqlReadBuffer.g__EnsureLong|41_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
Npgsql.Internal.NpgsqlConnector.RawOpen(SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
Npgsql.Internal.NpgsqlConnector.g__OpenCore|191_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.ConnectorPool.g__RentAsync|28_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.NpgsqlConnection.g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)

Further technical details

Npgsql version: 6.0.4
PostgreSQL version: 13.3
Operating system: Linux 8.x

Other details about my project setup:

The text was updated successfully, but these errors were encountered:

@arunsaho there are no details above — an exception message isn’t enough for us to help you. Please post a runnable code sample which triggers the issue, or at least provide more context.

string connString =
String.Format(
«Server=<0>;Username=<1>;Database=<2>;Port=<3>;Password=<4>;SSLMode=Prefer;Timeout=300;CommandTimeout = 300″,
Host,
User,
DBname,
Port,
Password);

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
—> System.TimeoutException: Timeout during reading attempt
Npgsql.Internal.NpgsqlReadBuffer.g__EnsureLong|41_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
Npgsql.Internal.NpgsqlConnector.RawOpen(SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
Npgsql.Internal.NpgsqlConnector.g__OpenCore|191_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.ConnectorPool.g__RentAsync|28_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
Npgsql.NpgsqlConnection.g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
Npgsql.NpgsqlConnection.Open()

@arunsaho a network timeout during Open can occur for various reasons: you may have a firewall rule preventing the connection, there may be a misconfiguration, or if this happens intermittently, your network could be a bit flaky. In any case, this isn’t really related to Npgsql in any way — Npgsql simply establishes a standard TCP connection to the host and port you provide it.

If the error is reproducible and not intermittent, I’d recommend trying to connect to the same PostgreSQL (same host/port) from the same client machine where Npgsql is running, but from an external tool, e.g. the psql command-line tool. That would help isolate things.

Hello All — I’m getting a very similar error. We are using .net 6.0.7 Npgsql 6.0.5 AWS Arora Postgres 12.7 with RDS proxy. It is appearing in both our staging and prod environments. What is strange is it mainly appears in a simple query on a ver small table (prod

20 rows staging

5 rows) and the query is based on primary key. Our load can be high on prod at times but is extremely low in staging.

query
select * from public.companies where

What is more strange is that it is not a repeatable event. In other words, it only happens sometimes. We have been looking for the issue for about a month now. Any help would be appreciated. It is always this query . very strange not to see it on random queries if it was a connection issue. We have check the Postgres logs and see no errors of note.

Источник

Aleksey писал(а):И втрой вариант, реализовать свои методы отмены или передачи конекшена через свой кастомный адаптер для PostgreSQL

Здравствуйте. Подключение к базе я смог получить, но я не могу «поймать» создание команд (IDbCommand). Я пробовал написать классы, унаследованные от StiOracleConnector / StiOracleAdapterService / StiOracleDatabase, и наблюдать за вызовом virtual-методов, но никто из них не занимается заполнением StiDataSource.

Суть проблемы: узнать какие запросы отправляет отчёт.

Заранее благодарен за помощь.

Вот, кстати, реализация получения текущего подключения (через событие DbConnectionBase.StateChange можно отлавливать момент открытия соединения). Вдруг кому-то пригодится.

public class MyDatabase : StiOracleDatabase
{
    protected override string DataAdapterType { get; } = typeof(MyAdapter).FullName;

    public MyDatabase()
        : this(string.Empty, string.Empty)
    {
    }

    public MyDatabase(string name, string connectionString)
        : base(name, connectionString)
    {
    }

    public MyDatabase(string name, string alias, string connectionString)
        : base(name, alias, connectionString)
    {
    }

    public MyDatabase(string name, string alias, string connectionString, bool promptUserNameAndpassword)
        : base(name, alias, connectionString, promptUserNameAndpassword)
    {
    }

    public MyDatabase(string name, string alias, string connectionString, bool promptUserNameAndpassword, string key)
        : base(name, alias, connectionString, promptUserNameAndpassword, key)
    {
    }

    public override StiSqlAdapterService GetDataAdapter() => new MyAdapter();
}

Адаптер:

public class MyAdapter : StiOracleAdapterService
{
    public override void CreateConnectionInDataStore(StiDictionary dictionary, StiSqlDatabase database)
    {
        base.CreateConnectionInDataStore(dictionary, database);

        // Подключения можно получить из DataStore:
        var connection = dictionary.DataStore[0].Data as OracleConnection;
    }
}

Добавление своей реализации:

StiReport.Dictionary.Databases.Add(new MyDatabase(name, connectionString));

Я получаю эту ошибку с перерывами в моем коде. Иногда это случается раз за разом. Иногда это происходит 1-из-10 раз. Я не делаю ничего уникального или специального в своем SQL, в отличие от другого плаката на StackOverflow, который выполнял команду COPY. Все, что я делаю, это SELECT.

Вот трассировка стека:

Exception while reading from stream
at Npgsql.ReadBuffer.Ensure(Int32 count, Boolean dontBreakOnTimeouts)
at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode  dataRowLoadingMode, Boolean isPrependedMessage)
at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRow LoadingMode) 
at Npgsql.NpgsqlConnector.ReadMessage(DataRowLoadingMode dataRowLoadingMode) 
at Npgsql.NpgsqlConnector.ReadExpecting[T]() 
at Npgsql.NpgsqlDataReader.NextResultInternal() 
at Npgsql.NpgsqlDataReader.NextResult() 
at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) 
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior) 
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at System.Data.Common.DbCommand.ExecuteReader() 
at Npgsql.NpgsqlCommand.ExecuteReader() 
at JBetaFinder.Program.portfolioSimulation(String beginResult, String endResult) in c:UsersjDocumentsVisual Studio 2013ProjectsJBetaFinderJBetaFinderProgram.cs:line 571

Любые предложения о том, как избежать этой ошибки? Это проблема с Npgsql и postgres?

Вот мое SQL-выражение, которое представляется наиболее проблематичным:

select leg1.trade_date, sum(p.qty) as totalqty, max(gas.net_change)*10000 as avggaschange,  
            sum(((leg1.settlement_price - leg2.settlement_price) - (leg3.settlement_price - leg4.settlement_price))*qty*1000000) as spread_value_weight
            from quant_portfolio p
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg1
                            on p.leg1 = leg1.strip
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg2
                            on p.leg2 = leg2.strip and leg1.trade_date = leg2.trade_date                
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg3
                            on p.leg3 = leg3.strip and leg1.trade_date = leg3.trade_date                
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg4
                            on p.leg4 = leg4.strip and leg1.trade_date = leg4.trade_date  
            inner join (select distinct trade_date, hub, product, strip, contract, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') gas
                            on gas.strip = (select min(leg1) from quant_portfolio where commodity = 'NG') and gas.trade_date = leg1.trade_date                         
            where p.commodity = 'NG'
            AND (leg1.trade_date>='xxx' and leg1.trade_date<='yyy')
            group by leg1.trade_date
            order by leg1.trade_date

Я попытался переустановить SQL, чтобы вытащить sub-SELECTS и сделать их все соединения; не помогло, такая же ошибка.

Вот код С#, вызывающий Npgsql:

query = new NpgsqlCommand(getFullQuantPortBeta.ToString().Replace("xxx", beginResult.ToString()).Replace("yyy", endResult.ToString()), conn);
            dr = query.ExecuteReader();//code does not get past this line!
            beta = 0;
            while (dr.Read())
            {
                baselineData.Add(double.Parse(dr[2].ToString()));
                responseData.Add(double.Parse(dr[3].ToString()));
                if (baselineData.Count > 3)
                {
                    Tuple<double, double> result = MathNet.Numerics.LinearRegression.SimpleRegression.Fit(baselineData.ToArray(), responseData.ToArray());
                    beta = result.Item2 * BETA_MULT;
                    Console.WriteLine("WEIGHT BETA = " + beta);
                }
            }
            dr.Close();

posted on June 2nd, 2020