A Little Noise

4Nov/170

GTID_INTERSECT

There's a GTID_SUBTRACT function, and the manual shows how to write your own cheap GTID_UNION, but not a GTID_INTERSECT. Fortunately it's easy to write your own, as it's just subtracting twice.

CREATE FUNCTION GTID_INTERSECT(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(g1, GTID_SUBTRACT(g1, g2));

What use is it?

SET @slave_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681577,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1267098:1267100-1267416:1267418-1589733';

SET @master_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-730294,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SET @master_purged = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681582,
421d139e-04b9-11e7-b702-0050569935dc:1-13077260,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SELECT @slave_needs := GTID_SUBTRACT(@master_executed, @slave_executed);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-730294, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

SELECT @slave_will_not_get := GTID_INTERSECT(@master_purged, @slave_needs);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-681582, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

While we're on the subject, the manual's GTID_UNION just concatenates the two sets, which means the output will list values twice. A small improvement will clean that up:

CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(CONCAT(g1,',',g2), '');

Filed under: MySQL Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.