Bug #42816 Row Based Replication broken in transactions that include non-transaction tables

• Submitted: 13 Feb 2009 7:05
• Modified: 13 Feb 2009 7:58
• Reporter: Gary Pendergast
• Status: Not a Bug
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.1.31
• OS: Any
• CPU Architecture: Any
• Tags: RBR, regression

[13 Feb 2009 7:05] Gary Pendergast

Description:
When using RBR in 5.1.31, there is a regression in handling non-transactional tables in within transactions. There is no workaround for this, as SBR has always be broken for this case.

How to repeat:
Setup:

USE test;
DROP TABLE IF EXISTS myisam_tbl;
DROP TABLE IF EXISTS innodb_tbl;

CREATE TABLE myisam_tbl (
  item INT,
  val INT
) ENGINE=MyISAM;

CREATE TABLE innodb_tbl (
  item INT,
  val INT,
  PRIMARY KEY (item)
) ENGINE=InnoDB;

INSERT INTO myisam_tbl (item, val) VALUES(1, 0);
INSERT INTO innodb_tbl (item, val) VALUES(1, 0), (2, 0);

SET SESSION binlog_format='ROW';

Connection 1:

START TRANSACTION;
UPDATE myisam_tbl SET val=val+1 WHERE item=1;
UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;

Connection 2:

START TRANSACTION;
UPDATE myisam_tbl SET val=val+1 WHERE item=1;
UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
COMMIT;

Connection 1:

COMMIT;

In 5.1.30, the Master and Slave tables both look the same:

master [localhost] {msandbox} (test) > select * from myisam_tbl;
+------+------+
| item | val  |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > select * from innodb_tbl;
+------+------+
| item | val  |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

In 5.1.31, the Master is correct, but the Slave is incorrect:

slave1 [localhost] {msandbox} (test) > select * from myisam_tbl;
+------+------+
| item | val  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from innodb_tbl;
+------+------+
| item | val  |
+------+------+
|    1 |    0 |
|    2 |    1 |
+------+------+
2 rows in set (0.00 sec)

[13 Feb 2009 7:58] Mats Kindahl

The old behavior has been changed as part of the resolution for BUG#40116. See that bug for more information.

[13 Feb 2009 9:15] Mats Kindahl

The old behavior was to write non-transactional statements inside a transaction "ahead" of the transaction since the changes take effect and is visible immediately. However, this caused subtle problems when mixing transactional and non-transactional changes (see BUG#40116 for more information).

The workaround for the example you give is to not write the non-transactional statements inside the transaction, but move them to before the transaction (they take affect before the transaction has completed anyway).

Here is an example of an execution:

  [connection 1]
  UPDATE myisam_tbl SET val=val+1 WHERE item=1;
  SELECT * FROM myisam_tbl WHERE item=1;
  item	val
  1	1
  START TRANSACTION;
  UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;

  [connection 2]
  UPDATE myisam_tbl SET val=val+1 WHERE item=1;
  SELECT * FROM myisam_tbl WHERE item=1;
  item	val
  1	2
  START TRANSACTION;
  UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
  COMMIT;

  [connection 1]
  COMMIT;

  [on master]
  SELECT * FROM innodb_tbl;
  item	val
  1	1
  2	2
  SELECT * FROM myisam_tbl;
  item	val
  1	2

  [on slave]
  SELECT * FROM innodb_tbl;
  item	val
  1	1
  2	2
  SELECT * FROM myisam_tbl;
  item	val
  1	2