57014 error canceling statement due to user request

I am having this phantom problem in my application where one in every 5 request on a specific page (on an ASP.NET MVC application) throws this error:

Npgsql.NpgsqlException: ERROR: 57014: canceling statement due to user request
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRow(Boolean clearPending)
   at Npgsql.ForwardsOnlyDataReader.Read()
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   ...

On the npgsql github page I found the following bug report: 615

It says there:

Regardless of what exactly is happening with Dapper, there’s
definitely a race condition when cancelling commands. Part of this is
by design, because of PostgreSQL: cancel requests are totally
«asynchronous» (they’re delivered via an unrelated socket, not as part
of the connection to be cancelled), and you can’t restrict the
cancellation to take effect only on a specific command. In other
words, if you want to cancel command A, by the time your cancellation
is delivered command B may already be in progress and it will be
cancelled instead.

Although they have made «changes to hopefully make cancellations much safer» in Npgsql 3.0.2 my current code is incompatible with this version because the need of migration described here.

My current workaround (stupid): I have commented the code in Dapper that says command.Cancel(); and the problem seems to be gone.

if (reader != null)
                {
                    if (!reader.IsClosed && command != null)
                    {
                        //command.Cancel();
                    }
                    reader.Dispose();
                    reader = null;
                }

Is there a better solution to the problem? And secondly what am I loosing with the current fix (except that I have to remember the change every time I update Dapper)?

Configuration:
NET45,
Npgsql 2.2.5,
Postgresql 9.3

LibraryConsoleApplication Critical: 0 : Npgsql.PostgresException (0x80004005): 57014: canceling statement due to user request
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.Consume(Boolean async)
   at Npgsql.NpgsqlDataReader.Close(Boolean connectionClosing, Boolean async)
   at Npgsql.NpgsqlDataReader.Close()
   at Npgsql.NpgsqlDataReader.Dispose(Boolean disposing)
   at System.Data.Common.DbDataReader.Dispose()
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+<>m__Finally1() in /_/Dapper/SqlMapper.cs:line 1122
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+System.IDisposable.Dispose()
   at LibraryConsoleApplication.Program2.LoadBibs() in C:UsersjemillerDocumentsVisual Studio 2017ProjectsLibrary5LibraryConsoleApplicationProgram2.cs:line 268
   at LibraryConsoleApplication.Program2.Main(String[] args) in C:UsersjemillerDocumentsVisual Studio 2017ProjectsLibrary5LibraryConsoleApplicationProgram2.cs:line 134
  Exception data:
    Severity: ERROR
    SqlState: 57014
    MessageText: canceling statement due to user request
    File: postgres.c
    Line: 3124
    Routine: ProcessInterrupts
    ThreadId=1
    DateTime=2020-12-01T20:15:39.6113755Z

NpgsqlException 57014: canceling statement due to user request #3052

Hi, i use version(4.1.4) from GitHUb> Fix4.1.4 branch,
I get this Exception, after execution application that call single select query.
that this occurs only when the I was open connection to excute SQL Command

SQL Command：Select payment.payment_id,payment.customer_id,payment.staff_idFrom public.payment

Further technical details

Npgsql version:4.1.4.0
PostgreSQL version:PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
Operating system: Windows 10 Enterprise

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

The exception indicates that someone is cancelling your query (e.g. by calling NpgsqlCommand.Cance or triggering a cancellation token), but it’s not possible to know who that is only from the exception trace posted above. Any chance you can post a minimal code sample that shows the error? Also, are you using any additional layers on top of Npgsql (e.g. Dapper)?

It’s possible that the command was cancelled even by a different instance of the application if pgbouncer is used with enabled pooling. Why so? To cancel a command you need the second connection because of the nature of PostgreSQL, and the connection Id is required too. The driver remembers the received id, but it’s a physical id which doesn’t correspond to the current connection from bouncer.

There is something to watch out for regarding this error. I’ve seen the error when using Dapper and iterating over a result set using a foreach. The results are being read a row at a time (not read into an array/list and then iterated over). The exception below results in an NpgsqlException (the one you list above) in my global exception handler that is outside the foreach loop. To see the actual exception, I found that I have to put a try/catch block within the foreach loop itself. So, I think the exception can still appear even if the connection wasn’t cancelled on the back end. Though I think I am actually running into that problem also (intermittent dropped connections when querying a PostgreSQL instance in Amazon’s cloud). I just tested it by intentionally throwing an exception in the body of the foreach to see what kind of exception is throw to the global exception handler I have in my Main() method.

I think the behavior that I’m seeing may actually be a bug. Here’s my stack trace. The problem is that Dispose() is throwing an exception. The rules for Dispose() say that it should never throw an exception if I remember correctly.

@jemiller0 which version of Npgsql are you using? #2372 should have taken care of this for 5.0.0. Regardless, can you please open a separate issue if there’s a problem?

Thanks @roji. I’m using .NET Framework. It looked like 5.0.0 required .NET 5. So, I’m currently using 4.1.6.

Don’t be cheated by the version number. It’s accidentally aligned with .NET 5 because Microsoft skipped version for due to .NET Framework. The provider is available for .NET Standard 2 and above.

Thanks @YohDeadfall. I can confirm that 5.0.0 does indeed appear to fix the problem. I like the new behavior much better. The old behavior was very confusing.

Good, closing the issue then.

@YohDeadfall I don’t know if it fixes the problem that @jack0718 had when he created this issue. Mine was actually the one in #2372.

@roji @YohDeadfall I just wanted to say, I think you guys are doing a great job. I can’t remember what the other issues were, but, I ran into a weird issue here or there and remember @roji responding and problems being fixed. It is very refreshing to run into a problem like the one with Dispose() throwing exceptions and having developers fix it straight away. This stands in stark contrast to a lot of other projects that I’ve had experience with. So, thanks, and keep up the good work.

Mostly all cancellation stuff was made by @vonzshik, he did a lot just before 5.0 and now is a member of the crew. Another big feature was made by @Brar, it’s replication support. So it’s not just Shay and me (: Anyway, thank you for the kind words!

Источник

«SQLSTATE: 57014, SQLCODE: -952» error when you use the BizTalk Adapter for DB2 in a Host Integration Server 2010 environment

Symptoms

When you use the Microsoft BizTalk Adapter for DB2 to issue queries against an IBM DB2 database, queries that take longer than 30 seconds to return any data may fail. Additionally, you receive an error message that resembles the following:

Processing was cancelled due to an interrupt. SQLSTATE: 57014, SQLCODE: -952

Cause

This problem occurs because the CommandTimeout property in the DB2 Transport properties is hard-coded to a time-out value of 30 seconds. Other time-out values that you may set do not override the hard-coded value.

Resolution

Cumulative update information

The fix that resolves this problem is included in cumulative update package 7 for Host Integration Server 2010. For more information about how to obtain the cumulative update package, see Cumulative update package 7 for Host Integration Server 2010.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

More Information

This update adds support for a configurable CommandTimeout property for the BizTalk Adapter for DB2.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Источник

How do I troubleshoot an AWS DMS task that is failing with error message «ERROR: canceling statement due to statement timeout»?

Last updated: 2022-10-12

I’m migrating data to or from my on-premises PostgreSQL database using AWS Database Migration Service (AWS DMS). The AWS DMS task runs normally for a while, and then the task fails with an error. How do I troubleshoot and resolve these errors?

Short description

If the PostgreSQL database is the source of your migration task, then AWS DMS gets data from the table during the full load phase. Then, AWS DMS reads from the write-ahead logs (WALs) that are kept by the replication slot during the change data capture (CDC) phase.

If the PostgreSQL database is the target, then AWS DMS gets the data from the source and creates CSV files in the replication instance. Then, AWS DMS runs a COPY command to insert those records into the target during the full load phase.

But, during the CDC phase, AWS DMS runs the exact DML statements from the source WAL logs in transactional apply mode. For batch apply mode, AWS DMS also creates CSV files during the CDC phase. Then, it runs a COPY command to insert the net changes to the target.

When AWS DMS tries to either get data from source or put data in the target, it uses the default timeout setting of 60 seconds. If the source or target is heavily loaded or there are locks in the tables, then AWS DMS can’t finish running those commands within 60 seconds. So, the task fails with an error that says «canceling statement due to statement timeout,» and you see one of these entries in the log:

«]E: RetCode: SQL_ERROR SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout; Error while executing the query [1022502] (ar_odbc_stmt.c:2738)»

«]E: test_decoding_create_replication_slot(. ) — Unable to create slot ‘lrcyli7hfxcwkair_00016402_8917165c_29f0_4070_97dd_26e78336e01b’ (on execute(. ) phase) [1022506] (postgres_test_decoding.c:392))»

To troubleshoot and resolve these errors, follow these steps:

• Identify the cause of long run times for commands.
• Increase the timeout value and check the slot creation timeout value.
• Troubleshoot slot creation issues.

Resolution

Identify the cause of long run times for commands

To find the command that failed to run during the timeout period, review the AWS DMS task log and the table statistics section of the task. You can also find this information in the PostgreSQL error log file if the parameter log_min_error_statement is set to ERROR or a lower severity. After identifying the command that failed, you can find the failed table names. See this example error message from the PostgreSQL error log:

To find locks on the associated tables, run this command in the source or target (depending where the error is appearing):

If you find any PIDs that are blocked, stop or terminate the blocked PID by running this command:

Because dead rows, or «tuples,» can increase SELECT time, check for large numbers of dead rows in the source tables by running this command:

Check to see if the failed target table has primary keys or unique indexes. If the table has no primary keys or unique indexes, then a full table scan is performed during the running of any UPDATE statement. This table scan can take a long time.

Increase the timeout value

AWS DMS uses the executeTimeout extra connection attribute in both the source and target endpoints. The default value for executeTimeout is 60 seconds, so AWS DMS times out if a query takes longer than 60 seconds to run.

If the error appears in Source_Unload or Source_Capture, then set the timeout value for executeTimeout in the source. If the error appears in Target_Load or Target_Apply, set the timeout value for executeTimeout in the target. Increase the timeout value setting by following these steps:

2. Choose Endpoints from the navigation pane.

3. Choose the PostgreSQL endpoint.

4. Choose Actions, and select Modify.

5. Expand the Endpoint-specific settings section.

6. In the field for Extra connection attributes, enter this value:

8. From the Endpoints pane, choose the name of your PostgreSQL endpoint.

9. From the Connections section, the Status of the endpoint changes from Testing to Successful.

You can increase (in milliseconds) the statement_timeout parameter in the PostgreSQL DB instance. The default value is , which turns off timeouts for any query. You can also increase the lock_timeout parameter. The default value is , which turns off timeouts for locks.

Troubleshoot slot creation issues

If the timeout occurred when you created the replication slot in the PostgreSQL database, then you see log entries similar to the following:

«]E: test_decoding_create_replication_slot(. ) — Unable to create slot ‘lrcyli7hfxcwkair_00016402_8917165c_29f0_4070_97dd_26e78336e01b’ (on execute(. ) phase) [1022506] (postgres_test_decoding.c:392)»

You can increase this timeout by configuring the TransactionConsistencyTimeout parameter in the Task settings section. The default value is 600 seconds.

PostgreSQL can’t create the replication slot if there are any active locks in the database user tables. Check for locks by running this command:

Then, to test whether the error has been resolved, run this command to manually create the replication slot in the source PostgreSQL database:

If the command still can’t create the slot, then you might need to work with a PostgreSQL DBA to identify the bottleneck and configure your database. If the command is successful, delete the slot that you just created as a test:

Источник

Postgresql error: отмена заявления из-за запроса пользователя

Что вызывает эту ошибку в postgresql?

Мои версии программного обеспечения:

PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit».

Мой драйвер postgresql: postgresql-9.2-1000.jdbc4.jar

Использование версии java: Java 1.7

Clue: моя база данных postgresql находится на твердотельном жестком диске, и эта ошибка происходит случайно, а иногда и вовсе.

ОТВЕТЫ

Ответ 1

Мы выяснили причину этой проблемы. Это объясняется ошибкой реализации setQueryTimeout() в последних драйверах JDBC 9.2-100x. Это может не произойти, если вы открываете/закрываете соединение вручную, но очень часто происходит с пулом соединений на месте, а autocommit — false. В этом случае setQueryTimeout() следует вызывать с ненулевым значением (например, используя аннотацию Spring framework @Transactional (timeout = xxx)).

Оказывается, всякий раз, когда исключение SQL возникает во время выполнения инструкции, таймер отмены не отменяется и остается в живых (как он реализован). Из-за объединения, соединение позади не закрыто, но возвращается в пул. Позже, когда таймер отмены срабатывает, он случайным образом отменяет запрос, связанный в настоящее время с соединением, с которым был создан этот таймер. В настоящий момент это совершенно другой запрос, который объясняет эффект случайности.

Предлагаемое обходное решение — отказаться от setQueryTimeout() и вместо этого использовать конфигурацию PostgreSQL (statement_timeout). Он не обеспечивает такой же уровень гибкости, но, по крайней мере, всегда работает.

Ответ 2

Если вы получаете эту ошибку без использования транзакций

Пользователь попросил отменить выражение. Заявление делает именно то, что ему говорят. Вопрос в том, кто просил это выражение быть отменено?

Посмотрите на каждую строку вашего кода, которая подготавливает SQL для выполнения. У вас может быть какой-то метод, применимый к утверждению, которое отменяет утверждение при некоторых обстоятельствах, например:

В моем случае, что случилось, я установил тайм-аут запроса на 25 секунд, и когда вставка заняла больше времени. Он передал исключение «отмена из-за исключения пользователя».

Если вы получаете эту ошибку при использовании транзакций:

Если вы получите это исключение, дважды проверьте весь свой код, который выполняет транзакции SQL.

Если у вас есть запрос, который находится в транзакции, и вы забываете совершить транзакцию, а затем используете это соединение, чтобы делать что-то еще, где вы работаете, как если бы вы не находились в транзакции, может быть поведение undefined, которое производит это исключение.

Убедитесь, что весь код, выполняющий транзакцию, очищается после себя. Убедитесь, что транзакция начинается, выполняются работы, выполняется больше работы, а транзакция откатывается или фиксируется, а затем убедитесь, что соединение осталось в состоянии autocommit=true .

Если это ваша проблема, тогда исключение не бросается туда, где вы забыли очистить себя, это происходит где-то долго после того, как вы не смогли очистить после транзакции, сделав это неуловимым исключением для отслеживания. Обновление соединения (закрытие и получение нового) очистит его.

Ответ 3

Это предполагает, что ошибка ошибки гонки в jarb jar файле для postgresql отвечает за указанную выше ошибку. (состояние гонки описано здесь: http://postgresql.1045698.n5.nabble.com/ERROR-canceling-query-due-to-user-request-td2077761.html)

Обходной путь 1, периодически обновлять соединение с базой данных

Один из способов — закрыть соединение с базой данных и периодически создавать новое подключение к базе данных. После того, как каждые несколько тысяч операторов sql просто закрывают соединение и воссоздают его. Затем по какой-то причине эта ошибка больше не выбрасывается.

Обходной путь 2, включите ведение журнала

Если вы включаете ведение журнала на уровне драйвера JDBC при настройке драйвера, то в некоторых ситуациях проблема состояния гонки нейтрализуется:

Обходной путь 3, поймать исключение и повторно инициализировать соединение

Вы также можете попробовать поймать конкретное исключение, повторно инициализировать соединение и повторить попытку запроса.

Обходной путь 4, дождитесь появления jQuery jgbc jgbc с исправлением ошибки

Я думаю, что проблема может быть связана со скоростью моего SSD-накопителя. Если вы получите эту ошибку, сообщите, как ее последовательно воспроизводить здесь, есть разработчики, очень заинтересованные в раздавливании этой ошибки.

Ответ 4

В дополнение к предложениям Эрика вы можете видеть, что оператор отменяет, когда:

• Администратор или другое соединение, зарегистрированное как один и тот же пользователь, использует pg_cancel_backend , чтобы попросить ваш сеанс отменить его текущий оператор
• Администратор отправляет сигнал на бэкэнд PostgreSQL, который запускает ваш оператор
• Администратор запрашивает fast выключение или перезапуск сервера PostgreSQL

Проверьте задания cron или инструменты управления загрузкой, которые могут отменить длительные запросы.

Источник

Hello.

The next query runs into error 57014 in about 50 % of the cases. We
certainly have not aborted the request. In the other 50 % it runs OK. The
statement is executed
in pgsql 7.4.8

Statement:
————
SELECT pst.typ_stay, SUM(pst.nr_rooms), SUM(pst.nr_trans), SUM(pst.pst_amnt)
from pst where pst.companyc = ‘H’ AND pst.pst_date >= (date ‘2006-01-01’)
AND pst.pst_date <= (date ‘2006-06-08’) AND pst.pst_type IN (1,7) AND
pst.typ_stay IS NOT NULL AND pst.dpt_code = ‘FO’ GROUP BY pst.typ_stay ;

Extract logfile
—————-
2006-06-09 03:55:06 LOG: 00000: database system is ready
LOCATION: StartupXLOG, xlog.c:2946
2006-06-09 03:55:37 ERROR: 57014: canceling query due to user request
LOCATION: ProcessInterrupts, postgres.c:1964
STATEMENT: SELECT pst.typ_stay, SUM(pst.nr_rooms), SUM(pst.nr_trans),
SUM(pst.pst_amnt) from pst where pst.companyc = ‘H’ AND pst.pst_date >=
(date ‘2006-01-01’) AND pst.pst_date <= (date ‘2006-06-08’) AND pst.pst_type
IN (1,7) AND pst.typ_stay IS NOT NULL AND pst.dpt_code = ‘FO’ GROUP BY
pst.typ_stay ;
2006-06-09 03:56:07 LOG: 08P01: unexpected EOF on client connection

Anyone any idea?

Many thanks

Henk Sanders

Environment : jdk1.8
architecture:Spring Boot 1.5.7+ Mybatis 3.4.3+ Druid + PostgreSQL JDBC Driver

I reported an error while using PostgreSQL to perform some queries. After spending half a day trying to understand the cause of the error, I decided to summarize it here.

ERROR: canceling statement due to user requestThe reason for the exception is thrown.

The main reason for reporting this error is that it is consistent with the literal meaning, «the status of the current query has been canceled due to the user’s request.»

Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)

It may be a bit sloppy to explain, but there is no way to explain it in more detail. The general meaning is that this code is thrown because you set the code to cancel the execution of the current query.
The reason for the exception is that the function was executed.java.sql.Statement:

// java.sql.Statement:248-259
    /**
     * Cancels this <code>Statement</code> object if both the DBMS and
     * driver support aborting an SQL statement.
     * This method can be used by one thread to cancel a statement that
     * is being executed by another thread.
     *
     * @exception SQLException if a database access error occurs or
     * this method is called on a closed <code>Statement</code>
     * @exception SQLFeatureNotSupportedException if the JDBC driver does not support
     * this method
     */
    void cancel() throws SQLException;

InPostgreSQL JDBC Driver The implementation code for this method in the driver is:

//org.postgresql.jdbc.PgStatement:595-606
  public void close() throws SQLException {
    // closing an already closed Statement is a no-op.
    if (isClosed) {
      return;
    }
    cleanupTimer();
    closeForNextExecution();
    isClosed = true;
  }

The two problems I am currently experiencing may cause this error to occur.

• When the user initiates an Http request, when the request triggers the Sql query, when the data has not been returned, the user cancels the request and causes the exception to be thrown;
• When in Mybatis’s configuration filemybatis-config.xmlSet indefaultStatementTimeoutThe attribute (unit: second) will be thrown when the query time of sql exceeds this set time;

The first case is not sure. But in the second case, the driver codePostgreSQL JDBC DriverFirst will be based on the query beforedefaultStatementTimeoutSet the time to create a timer.

//org.postgresql.jdbc:872-888
  private void startTimer() {
    /*
     * there shouldn't be any previous timer active, but better safe than sorry.
     */
    cleanupTimer();
    STATE_UPDATER.set(this, StatementCancelState.IN_QUERY);
    if (timeout == 0) {
      return;
    }
    TimerTask cancelTask = new TimerTask() {
      public void run() {
        try {
          if (!CANCEL_TIMER_UPDATER.compareAndSet(PgStatement.this, this, null)) {
            // Nothing to do here, statement has already finished and cleared
            // cancelTimerTask reference
            return;
          }
          PgStatement.this.cancel(); //**The code to cancel this query in the timer**
        } catch (SQLException e) {
        }
      }
    };
    CANCEL_TIMER_UPDATER.set(this, cancelTask);
    connection.addTimerTask(cancelTask, timeout);/ / Create a timer, and set the timeout
  }

After the execution time exceeds the timer setting time, the timer will start and close the sql execution. After the execution is completed, the loop that waits for the query is judged to cancel the query. When an exception is thrown, the exception is wrapped and thrown. Out.