Miscounting XID for INSERT IGNORE - potentially breaking XA recovery

This bug affects all versions of MySQL and MariaDB from 5.0 and up.

SET binlog_format=row;

CREATE TABLE t1(a INT PRIMARY KEY) engine=innodb;

CREATE TABLE t2(a INT) engine=myisam;

INSERT INTO t1 VALUES (1);

START TRANSACTION;

INSERT INTO t2 VALUES (1);

INSERT IGNORE INTO t1 VALUES (1);

COMMIT;

During COMMIT, there is nothing to write into the binlog - the changes to t2
were written directly during the INSERT statement (non-transactional updates),
and due to the primary key conflict, no row events exist for t1.

The bug is that the mysql_bin_log.prepared_xids counter is not increased in
tc_log() (log_and_order() in mariadb 5.3+). But it is still decreased in
unlog(). This means that if another transaction is doing COMMIT in parallel,
we may rotate binlog too early (before the other transaction is durably
committed in InnoDB). And if we then crash at the wrong time - the other
transaction will not be seen when we scan the newest binlog (as it is in the
old one that was rotated), and we will roll it back - so it will be in binlog
but missing from the storage engine.

The bug was actually found by MySQL@Oracle devs, but they did not fix it -
instead they just removed the assert that was supposed to catch this problem:

revision-id: jon.hauglid@oracle.com-20110512125600-czil87cpcrrbvs48

Bug#12346411 SQL/LOG.CC:6509: ASSERTION `PREPARED_XIDS > 0' FAILED

=== modified file 'sql/log.cc'

--- sql/log.cc	2011-03-11 09:12:58 +0000

+++ sql/log.cc	2011-05-12 12:56:00 +0000

@@ -6506,8 +6506,11 @@

 {

   DBUG_ENTER("TC_LOG_BINLOG::unlog");

   mysql_mutex_lock(&LOCK_prep_xids);

-  DBUG_ASSERT(prepared_xids > 0);

-  if (--prepared_xids == 0) {

+  // prepared_xids can be 0 if the transaction had ignorable errors.

+  DBUG_ASSERT(prepared_xids >= 0);

+  if (prepared_xids > 0)

+    prepared_xids--;

+  if (prepared_xids == 0) {

     DBUG_PRINT("info", ("prepared_xids=%lu", prepared_xids));

     mysql_cond_signal(&COND_prep_xids);

   }

As part of the fix for this bug, that change must be reverted to restore the
assert.