Over a million developers have joined DZone.

Differences in PREPARE Statement Error Handling With Binary and Text Protocol

DZone's Guide to

Differences in PREPARE Statement Error Handling With Binary and Text Protocol

Error handling is different with text and binary protocol — a more prominent difference since sysbench-1.0 started using the PREPARE statement by default.

· Database Zone ·
Free Resource

Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.

In this blog, we’ll look at the differences in how a PREPARE statement handles errors in binary and text protocols.

Since Percona XtraDB Cluster is a multi-master solution, when an application executes conflicting workloads, one of the workloads gets rolled back with a DEADLOCK error. While the same holds true even if you fire the workload through a PREPARE statement, there are differences between using the MySQL connector API (with binary protocol) and the MySQL client (with text protocol). Let’s look at these differences with the help of an example.

Base Workload

Say we have a two-node cluster (n1 and n2) with the following base schema and tables:

use test;
create table t (i int, k int, primary key pk(i)) engine=innodb;
insert into t values (1, 10), (2, 20), (3, 30);
select * from t;

Workload (w1) on node-1 (n1):

prepare st1 from 'update t set k = k + 100 where i > 1';
execute st1;

Workload (w2) on node-2 (n2):

prepare st1 from 'update t set k = k + 100 where i > 1';
execute st1;

The workloads are conflicting, and the first node to commit wins. Let’s assume n1 commits first, and the write-set is replicated to n2 while n2 is busy executing the local workload.

Different Scenarios Based on Configuration

We'll look at a couple different examples.

wsrep_retry_autocommit > 0

n2 tries to apply the replicated workload, thereby forcefully aborting (brute-force abort) the local workload (w2).

The forcefully aborted workload is retried (based on wsrep_retry_autocommit value (default is 1)).

w2 succeeds on retry, and the new state of table t would be:

(1, 10), (2, 220), (3, 230);

The user can increase wsrep_retry_autocommit to a higher value, though we don’t recommend this as a higher value increases pressure on the system to retry failed workloads. A retry doesn’t ensure things always pass if additional conflicting replicated workload are lined up in the certification queue. Also, it is important to note that a retry works only if AUTO_COMMIT=ON. For a begin-commit transaction, retry is not applicable. If we abort this transaction, then the complete transaction is rolled back. The workload executor application should have the logic to expect workload failure due to conflict and handle it accordingly.

wsrep_retry_autocommit = 0

Let’s say the user sets wsrep_retry_autocommit=0 on node-2 and executes the same workload. This time, it doesn’t retry the local workload (w2). Instead, it sends an error to the end-user:

mysql> execute st1;
<strong>ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction
</strong>mysql> select * from t;
| i | k |
| 1 | 10 |
| 2 | 120 |
| 3 | 130 |
3 rows in set (0.00 sec)

Only the replicated workload (w1) is applied, and local workload w2 fails with a DEADLOCK error.

Use of MySQL Connector API

Now let’s try to execute these workloads through Connector API. Connector API uses a binary protocol, which means it directly invokes the PREPARE statement and executes the statement using dedicated command codes (COM_STMT_PREPARE andCOM_STMT_EXECUTE).

While the conflicting transaction internal abort remains same, the COM_QUERY command code (text protocol) handling corrects the “query interrupted error” and resets it to “deadlock error” based on the wsrep_conflict_state value. It also has logic to retry the auto-commit enable statement.

This error handling and retry logic is not present when the COM_STMT_PREPARE and COM_STMT_EXECUTE command codes are used. This means the user sees a different error (non-masked error) when we try the same workload through an application using the MySQL Connector API.

The output of application from node-2:

Statement init OK!
Statement prepare OK!
Statement execution failed: Query execution was interrupted

Exact error code to be specific:

ER_QUERY_INTERRUPT<wbr />ED - 1317 - "Query execution was interrupted"

As you can see above, the statement/workload execution fails with “Query execution was interrupted” error vs. “deadlock” error (as seen with the normal MySQL client).


While the core execution remains same, error handling is different with text and binary protocol. This issue/limitation was always present, but it’s more prominent since sysbench-1.0 started using the PREPARE statement as a default.

What does this means to end-user/application?

Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.

database ,prepare statements ,error handling ,binary ,text protocol ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}