MySQL Bugs: #84850: Add a function to filter a GTID

Bug #84850	Add a function to filter a GTID
Submitted:	6 Feb 2017 21:14	Modified:	8 Mar 2018 12:11
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	8.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GTID

Description:
If I only want to see the gtids for the local server then I would like to do something like this:

mysql> SELECT GTID_FILTER(@@global.gtid_executed, @@server_uuid);

The result should be <@@server_uuid>:<tx> even if @@global.gtid_executed contains multiple ranges.

How to repeat:
See description

Hello Daniël,

Thank you for the feature request.

Thanks,
Umesh

Posted by developer:
 
Thanks for the feature request, Daniel.

The server provides two building blocks, GTID_SUBTRACT and GTID_SUBSET. Together with string manipulation, they can be used to do most GTID set operations.

There is a small library of stored functions for GTID set manipulation in the test suite, mysql-test/include/gtid_utils.inc. This file contains the function GTID_INTERSECTION_WITH_UUID, which returns the subset of a GTID set having a given UUID.

This library currently does not have any function that selects only the highest-numbered GTID when there are multiple ranges. However, it can be computed using GTID_INTERSECTION_WITH_UUID + string manipulation, e.g. like:

CREATE FUNCTION GTID_LAST_FOR_UUID(gtid_set TEXT(10000), uuid TEXT(36))
RETURNS TEXT(10000) BEGIN
  DECLARE uuid_set TEXT(10000) DEFAULT GTID_INTERSECTION_WITH_UUID(gtid_set, uuid);
  DECLARE from_last_colon TEXT(10000) DEFAULT SUBSTRING_INDEX(uuid_set, ':', -1);
  DECLARE from_last_dash TEXT(10000) DEFAULT SUBSTRING_INDEX(from_last_colon, '-', -1);
  RETURN IF(from_last_dash != '', CONCAT(uuid, ':', from_last_dash), '');
END;

(Note: this was written mainly for tests and is limited to 10000 characters. If you expect bigger GTID sets you need to adjust that limit in gtid_utils.inc)

I don't know if this suffices or whether you need something that exists out of the box?

Could you give an example of when getting the highest-numbered GTID in a set is needed?

I think your answer doesn't really match my request.

Example:
gtid_executed = "aaaa:1-100, bbbb:1-999"
server_uuid = "bbb"

SELECT GTID_FILTER(@@global.gtid_executed, @@server_uuid);
Result: bbbb:1-999

An example would be to check for local transactions on a slave (which is not supposed to have any in most situations)

Posted by developer:
 
Thank you for your clarification, Daniel. Good that you want all the ranges for the given UUID, and not just the highest number (sorry if I misunderstood this at first). Then I think we are on the same page. Then the use case of finding errant transactions on a slave makes sense.

Then, the function GTID_INTERSECTION_WITH_UUID defined in mysql-test/include/gtid_utils.inc is exactly the same as the GTID_FILTER function you suggest. Is GTID_INTERSECTION_WITH_UUID usable for you?

Yes that works.

mysql> SELECT GTID_INTERSECTION_WITH_UUID('
    '> d09a0723-07ff-11e8-aa13-34363bcba386:1,
    '> c09a0723-07ff-11e8-aa13-34363bcba386:1,
    '> a09a0723-07ff-11e8-aa13-34363bcba386:1', 
    -> 'c09a0723-07ff-11e8-aa13-34363bcba386') as result\G
*************************** 1. row ***************************
result: c09a0723-07ff-11e8-aa13-34363bcba386:1
1 row in set (0.00 sec)

However:
1. These are not installed by default
2. Creating these functions requires log_bin_trust_function_creators=1
3. These functions are installed in a specific schema, not globally.
4. TEXT(10000) might not work in production (many different UUID's because of switchovers to new masters)