Skip to content

Instantly share code, notes, and snippets.

@lefred

lefred/addtion_to_sys_8.0.2.sql

Last active September 12, 2025 19:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lefred/153448f7ea0341d6d0daa2738db6fcd8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lefred/153448f7ea0341d6d0daa2738db6fcd8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. lefred revised this gist Dec 14, 2018. 1 changed file with 21 additions and 8 deletions.
    29 changes: 21 additions & 8 deletions addtion_to_sys_8.0.2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,15 +4,28 @@ DELIMITER $$

    CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

    -- previous obsolete function
    -- CREATE FUNCTION gr_member_in_primary_partition()
    -- RETURNS VARCHAR(3)
    -- DETERMINISTIC
    -- BEGIN
    -- RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    -- performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
    -- ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    -- 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    -- performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
    -- END$$

    -- new function, contribution from Bruce DeFrang
    CREATE FUNCTION gr_member_in_primary_partition()
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
    RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
    RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
    END$$

    CREATE VIEW gr_member_routing_candidate_status AS SELECT
  2. lefred revised this gist Jul 18, 2017. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion addtion_to_sys_8.0.2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,6 +20,7 @@ sys.gr_member_in_primary_partition() as viable_candidate,
    IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
    performance_schema.global_variables WHERE variable_name IN ('read_only',
    'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
    Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id();$$
    Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
    from performance_schema.replication_group_member_stats where member_id=my_id();$$

    DELIMITER ;
  3. lefred revised this gist Jul 18, 2017. 1 changed file with 1 addition and 54 deletions.
    55 changes: 1 addition & 54 deletions addtion_to_sys_8.0.2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,59 +2,6 @@ USE sys;

    DELIMITER $$

    CREATE FUNCTION IFZERO(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IF(a = 0, b, a)$$

    CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

    CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
    RETURNS TEXT(10000)
    DETERMINISTIC
    RETURN GTID_SUBTRACT(g, '')$$

    CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
    RETURNS INT
    DETERMINISTIC
    BEGIN
    DECLARE result BIGINT DEFAULT 0;
    DECLARE colon_pos INT;
    DECLARE next_dash_pos INT;
    DECLARE next_colon_pos INT;
    DECLARE next_comma_pos INT;
    SET gtid_set = GTID_NORMALIZE(gtid_set);
    SET colon_pos = LOCATE2(':', gtid_set, 1);
    WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
    SET result = result +
    SUBSTR(gtid_set, next_dash_pos + 1,
    LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
    SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
    ELSE
    SET result = result + 1;
    END IF;
    SET colon_pos = next_colon_pos;
    END WHILE;
    RETURN result;
    END$$

    CREATE FUNCTION gr_applier_queue_length()
    RETURNS INT
    DETERMINISTIC
    BEGIN
    RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
    Received_transaction_set FROM performance_schema.replication_connection_status
    WHERE Channel_name = 'group_replication_applier' ), (SELECT
    @@global.GTID_EXECUTED) )));
    END$$

    CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

    CREATE FUNCTION gr_member_in_primary_partition()
    @@ -73,6 +20,6 @@ sys.gr_member_in_primary_partition() as viable_candidate,
    IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
    performance_schema.global_variables WHERE variable_name IN ('read_only',
    'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
    sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id();$$
    Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id();$$

    DELIMITER ;
  4. lefred created this gist Jul 18, 2017.
    78 changes: 78 additions & 0 deletions addtion_to_sys_8.0.2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    USE sys;

    DELIMITER $$

    CREATE FUNCTION IFZERO(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IF(a = 0, b, a)$$

    CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
    RETURNS INT
    DETERMINISTIC
    RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

    CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
    RETURNS TEXT(10000)
    DETERMINISTIC
    RETURN GTID_SUBTRACT(g, '')$$

    CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
    RETURNS INT
    DETERMINISTIC
    BEGIN
    DECLARE result BIGINT DEFAULT 0;
    DECLARE colon_pos INT;
    DECLARE next_dash_pos INT;
    DECLARE next_colon_pos INT;
    DECLARE next_comma_pos INT;
    SET gtid_set = GTID_NORMALIZE(gtid_set);
    SET colon_pos = LOCATE2(':', gtid_set, 1);
    WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
    SET result = result +
    SUBSTR(gtid_set, next_dash_pos + 1,
    LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
    SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
    ELSE
    SET result = result + 1;
    END IF;
    SET colon_pos = next_colon_pos;
    END WHILE;
    RETURN result;
    END$$

    CREATE FUNCTION gr_applier_queue_length()
    RETURNS INT
    DETERMINISTIC
    BEGIN
    RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
    Received_transaction_set FROM performance_schema.replication_connection_status
    WHERE Channel_name = 'group_replication_applier' ), (SELECT
    @@global.GTID_EXECUTED) )));
    END$$

    CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

    CREATE FUNCTION gr_member_in_primary_partition()
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
    RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
    END$$

    CREATE VIEW gr_member_routing_candidate_status AS SELECT
    sys.gr_member_in_primary_partition() as viable_candidate,
    IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
    performance_schema.global_variables WHERE variable_name IN ('read_only',
    'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
    sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id();$$

    DELIMITER ;