MySQL Error 1614 (XAER_NOTA) occurs when you try to commit, rollback, or prepare an XA distributed transaction that doesn't exist or has already been completed. This commonly happens in replication scenarios or when another connection already committed the transaction.
MySQL Error 1614 is the server's way of saying it cannot find an XA transaction identifier (XID) you're trying to operate on. XA transactions are "extended architecture" distributed transactions used in replication and multi-database scenarios. When you run XA COMMIT, XA ROLLBACK, or XA PREPARE, MySQL looks up the transaction by its XID in its internal transaction list. If the XID is not found—either because it was never prepared, already committed, or from a different session—you get error 1614. This error maps to the XA standard code XAER_NOTA, which means "Node transaction association does not exist." It's distinct from other transaction errors: it specifically means the XID lookup failed, not that the transaction is in the wrong state.
First, determine whether the XID is known to MySQL at all. For MySQL 8.0+, query the performance schema:
SELECT * FROM performance_schema.events_transactions_current
WHERE THREAD_ID = <your_thread_id>;Or, check all prepared XA transactions globally:
XA RECOVER;The XA RECOVER command returns a list of all transactions in PREPARED state. If your XID is not listed, it either never reached PREPARED state, was already committed/rolled back, or the session is different.
What this tells you: If the XID doesn't appear, error 1614 is inevitable because MySQL has no record of that transaction. You'll need to skip the error (in replication) or inject an empty transaction (see below).
In application code, ensure the same database connection is used for the entire XA workflow:
// WRONG - connection pooling recycled the connection
connection = getConnection();
connection.execute("XA START 'xid1'");
connection.close(); // Bad!
connection = getConnection(); // May be a different physical connection
connection.execute("XA COMMIT 'xid1'"); // Error 1614!
// CORRECT - pin the connection for the entire transaction
connection = getConnection();
try {
connection.execute("XA START 'xid1'");
connection.execute("INSERT INTO users VALUES (...)");
connection.execute("XA END 'xid1'");
connection.execute("XA PREPARE 'xid1'");
connection.execute("XA COMMIT 'xid1'");
} finally {
connection.close(); // Only after all XA operations complete
}Connection pooling libraries like HikariCP or Druid must be configured to "pin" connections during distributed transactions—not recycle them between XA phases.
Why this matters: Each XA transaction is tied to the session that started it. If the connection is recycled, MySQL's XA list for that session is cleared, and the XID becomes invisible.
If replication is stuck because the replica received an XA COMMIT for an unknown XID, you can inject a placeholder transaction on the replica so it can catch up:
-- Example: replica received "XA COMMIT X'1a',X'a1',1" but XID was never prepared
-- 1. Start an empty XA transaction with the same XID
XA START X'1a',X'a1',1;
-- 2. End it (no actual work needed)
XA END X'1a',X'a1',1;
-- 3. Prepare it
XA PREPARE X'1a',X'a1',1;
-- 4. Commit it
XA COMMIT X'1a',X'a1',1;
-- Now replication can resume
START SLAVE;This creates a "dummy" prepared transaction that replication can then commit naturally, unblocking the replica.
When to use: Only when replication is halted and you've verified the XID doesn't exist. This requires super privilege and careful coordination.
Starting in MySQL 8.0.17, the system variable xa_detach_on_prepare allows XA transactions to be completed by any session after PREPARE. This is enabled by default in MySQL 8.0.29+:
-- Check the current setting
SHOW VARIABLES LIKE 'xa_detach_on_prepare';
-- If you're on 8.0.17-8.0.28, enable it explicitly
SET GLOBAL xa_detach_on_prepare = ON;With xa_detach_on_prepare = ON, after XA PREPARE, the transaction is detached from the originating session. Any session can then XA COMMIT or XA ROLLBACK it, which is safer for connection pooling scenarios.
Benefit: Reduces the risk of error 1614 caused by connection recycling, because the prepared XID is not tied to a specific session anymore.
If replication is blocked after a source crash, you may need to skip the problematic XA COMMIT event:
-- Stop the replica
STOP SLAVE;
-- Check the current position
SHOW SLAVE STATUS\G
-- Skip the next N events (usually 1 for the failing XA COMMIT)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
-- Resume replication
START SLAVE;
-- Verify it's working
SHOW SLAVE STATUS\GThis tells the replica to skip the XA COMMIT event in the binlog. Use this only if:
- You've confirmed the XID doesn't exist and can't be recovered
- You're willing to accept potential data inconsistency
- You have a backup plan to resync if needed
Note: This is a last resort. Only use after exhausting other options.
Detached vs. Attached XA Transactions
In MySQL 8.0.29+, by default, XA transactions are detached after XA PREPARE (xa_detach_on_prepare = ON). This means a prepared transaction is no longer tied to a specific session. Any session can commit or rollback it using XA COMMIT/ROLLBACK with the XID.
Before MySQL 8.0.29, or if xa_detach_on_prepare = OFF, a prepared XA transaction remains attached to its originating session. Only that session can finish it. If the session closes, the transaction remains in PREPARED state until explicitly recovered using XA RECOVER and XA COMMIT/XA ROLLBACK from a new session.
Replication and XA Transactions
When the MySQL replication source uses XA transactions, the events are written to the binlog in order: XA START, DML operations, XA END, XA PREPARE, and XA COMMIT (or ROLLBACK). The replica replays these events sequentially.
If the source crashes between XA PREPARE and XA COMMIT, upon recovery, the source's binary logs may contain an orphaned XA COMMIT event. When replication resumes, the replica tries to execute this XA COMMIT but the XID was never prepared on the replica, causing error 1614.
This is particularly problematic in backup/restore scenarios: if you restore a replica from a backup that was taken mid-XA transaction, the PREPARE event may be missing from the replica's relay logs.
Recovery and XA RECOVER
The XA RECOVER statement is essential for crash recovery. It returns all transactions currently in PREPARED state:
XA RECOVER;
-- Output:
-- | formatID | gtrid_length | bqual_length | data |
-- | 1 | 4 | 4 | xid1xid1 |
-- | 1 | 4 | 4 | xid2xid2 |After a crash, use XA RECOVER to find orphaned prepared transactions and decide whether to XA COMMIT or XA ROLLBACK them. This is critical for data consistency.
Performance Considerations
Avoid long-running XA transactions. Each XID holds locks on resources, and the longer PREPARE to COMMIT takes, the higher the risk of deadlocks, timeouts, and connection pool exhaustion. If your distributed transaction is complex, break it into smaller units or use a framework like Seata that handles retries and recovery automatically.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL