Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2290

InnoDB ACTIVE (PREPARED) threads were marked/converted to XA transactions


    • Bug
    • Status: Done
    • Medium
    • Resolution: Incomplete
    • 5.7.23-31.31
    • None
    • None


      Hello Guys,

      It's been a good refresher to me to post again here. Hope everyone's doing fine there.

      Last week, I filed an upstream bug https://bugs.mysql.com/bug.php?id=93326. Basically, we got a weird case that we think is related to this code https://github.com/mysql/mysql-server/blob/e4924f36486f971f8a04252e01c803457a2c72f7/storage/innobase/trx/trx0trx.cc but since the version is of PXC, unfortunately, Oracle doesn't acknowledge the version.

      This is PXC version is: Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31  version 5.7.23-23-57-log 

      Now, allow me to paste the contents from the upstream here.

      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:

      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.


      === 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 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.




            lalit.choudhary Lalit Choudhary
            t0yt0y Paul Namuag
            0 Vote for this issue
            6 Start watching this issue



              Smart Checklist