Description:
Complete Version: InnoDB: Percona XtraDB (http://www.percona.com) 5.7.23-23 started;
We think that this is none of Percona XtraDB issue but we're basing this lines of code from github https://github.com/mysql/mysql-server/blob/e4924f36486f971f8a04252e01c803457a2c72f7/storag...
Our customer expressed that they do not have any XA transactions in their back end . However, he found this in the MySQL source code:
https://github.com/mysql/mysql-server/blob/e4924f36486f971f8a04252e01c803457a2c72f7/storag...
if (undo->state == TRX_UNDO_PREPARED) {
ib::info(ER_IB_MSG_1204) << "Transaction " << trx_get_id_for_print(trx)
<< " was in the XA prepared state.";
These can be located at lines 768 and 831 of code trx0trx.cc.
It looks like if the transaction was in PREPARED state when the server was killed this error is logged even if it is an ordinary Innodb transaction.
This encounter is such a weird behaviour were the active threads marked as ACTIVE (PREPARED) in the InnoDB status and were being recognized as XA transactions. This was noticed when it was force to be killed and MySQL error log shows that it was in the prepared state.
Example:
=== InnoDB Engine Status ==============
---TRANSACTION 1303691515, ACTIVE (PREPARED) 2 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 28078, OS thread handle 140128559306496, query id 6298771 prod-db-eu-proxysql-01.node.dc1.consul 10.0.101.53 db_g4s wsrep: pre-commit/certification passed (777602149)
INSERT INTO tbl_eventsongoing (starttime, endtime, accesstime, evtype, server, cameraid, videosource, nasid, userid, format, rate, evduration, frames, bytes, videocodec, audiocodec, evcomment, resolution, evpath, device_mapping) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
=========== MySQL Error Log ===========
2018-11-21T07:05:47.508407Z 0 [Note] InnoDB: Transaction 1303691515 was in the XA prepared state.
However, the Global Status reveals that there were no XA being used. See below:
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0
Please see details attached for the complete InnoDB status and MySQL Error Log.
How to repeat:
Please checkout the description.
Suggested fix:
I'm not sure if these normal transactions where converted into XA which causing the server to stuck up.
