Why does this CASE based update cause replication skew?

Vote count:

0

Using MySQL 5.6 with statement based replication between a single master and slave, the following scenario creates replication skew:

Create this table:

CREATE TABLE `ReplicationTest` (
  `TestId` int(11) NOT NULL,
  `Tokens` int(11) NOT NULL,
  PRIMARY KEY (`TestId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

Insert this data:

Insert into ReplicationTest (TestId, Tokens) VALUES
(1, 0),
(2, 0),
(3, 0),
(4, 0);

Create this proc:

CREATE PROCEDURE `MyDatabase`.`ReplicationTestProc` (vTestId int, pSuccessful BIT, pAmount DECIMAL(19, 4))
BEGIN

START TRANSACTION;

Update MyDatabase.ReplicationTest Set Tokens = CASE WHEN pSuccessful THEN Tokens - (pAmount * 100) ELSE Tokens END
where TestId = vTestId;

COMMIT;

END

Run these 4 statements in a single execution

call `MyDatabase`.`ReplicationTestProc`(1, 1, 1);
call `MyDatabase`.`ReplicationTestProc`(2, 1, 1);
call `MyDatabase`.`ReplicationTestProc`(3, 1, 1);
call `MyDatabase`.`ReplicationTestProc`(4, 0, 1);

You will now have different values in the ReplicationTest table between master and replication. It looks like the pSuccessful variable is being treated as a global, with the last value set the one that is being used on the replication server.

asked 21 secs ago

jmacinnes

1,236