MySQL Bugs: #119216: Feature request: Storage engine - Binlog XA transaction should have an option to roll back instead of forward on crash

Bug #119216	Feature request: Storage engine - Binlog XA transaction should have an option to roll back instead of forward on crash
Submitted:	23 Oct 2025 11:30
Reporter:	Eduardo Ortega (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As per the docs[1]:

"The effect of implicit InnoDB support for two-phase commit in XA transactions and sync_binlog=1 is that at restart after a crash, after doing a rollback of transactions, the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to complete any prepared transactions that were successfully written to the to the binary log, and truncates the binary log to the last valid position. This ensures that the binary log reflects the exact data of InnoDB tables, and therefore the replica remains in synchrony with the source because it does not receive a statement which has been rolled back"

As a user, I think it would be useful if I could configure the behavior to be either how it is currently described (default); or to do the opposite: on crash recovery, roll back  transactions that are prepared in the storage engine and committed to the binlog, from both the binlog and the storage engine.

The reason why I find the above to be useful is because, in replicated set-ups using semi-sync, it would make sure that a failed primary that had writes prepared on InnoDB, committed to the binlog and not acked by a semi-sync acker would be rolled back from both the binlog and InnoDB; allowing the failed primary to be safely re-attached as a replica later; as opposed to having to discard it and create a new one (via cloning or restoring from backup or whatever operations that might be a lot slower).

[1]: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

How to repeat:
N/A

Suggested fix:
A new config flag (innodb_binlog_xa_recover_rollback?) is created and the behavior is as follows:

If innodb_binlog_xa_recover_rollback=ON, the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to roll-back any prepared transactions that were successfully written to the to the binary log, and truncates the binary log so that its last transaction corresponds to the xid of the last fully transaction that was fully committed to the storage engine.