Bug #93326 InnoDB ACTIVE (PREPARED) threads were marked/converted to XA transactions
Submitted: 24 Nov 2018 17:40 Modified: 25 Nov 2018 1:30
Reporter: Paul Namuag Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.23-23 OS:Ubuntu (16.04.5 LTS)
Assigned to: CPU Architecture:x86 (NPTL 2.23)

[24 Nov 2018 17:40] Paul Namuag
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.
[24 Nov 2018 17:43] Paul Namuag
MySQL error log

Attachment: mysql-error.log (application/octet-stream, text), 66.05 KiB.

[24 Nov 2018 17:44] Paul Namuag
InnoDB status before MySQL was killed

Attachment: show-engine-innodb.txt (text/plain), 71.33 KiB.

[24 Nov 2018 18:49] Miguel Solorzano
Thank you for the bug report. Percona server is unsupported, please check with MySQL server and print here the result found. Thanks.
[25 Nov 2018 1:30] Paul Namuag
Hi MIguel,

I understand that this is Percona Server, however, the InnoDB code we suspect is coming from MySQL Oracle. Maybe you can verify based on the code that we pointed out. Otherwise we'll file this one to Percona if this is XtraDB.