Skip to content

# Month: December 2019

## Quick hack for GTID_OWN lack

MySQL GTID Replication December 11, 2019

One of the benefits of MySQL GTIDs is that each server remembers all GTID entries ever executed. Normally these would be ranges, e.g. `0041e600-f1be-11e9-9759-a0369f9435dc:1-3772242` or multi-ranges, e.g. `24a83cd3-e30c-11e9-b43d-121b89fcdde6:1-103775793, 2efbcca6-7ee1-11e8-b2d2-0270c2ed2e5a:1-356487160, 46346470-6561-11e9-9ab7-12aaa4484802:1-26301153, 757fdf0d-740e-11e8-b3f2-0a474bcf1734:1-192371670, d2f5e585-62f5-11e9-82a5-a0369f0ed504:1-10047`.

One of the common problems in asynchronous replication is the issue of consistent reads. I’ve just written to the `master`. Is the data available on a replica yet? We have iterated on this, from reading on `master`, to heuristically finding up-to-date replicas based on heartbeats (see presentation and slides) via freno, and now settled, on some parts of our apps, to using GTID.

GTIDs are reliable as any replica can give you a definitive answer to the question: have you applied a given transaction or not?. Given a GTID entry, say `f7b781a9-cbbd-11e9-affb-008cfa542442:12345`, one may query for the following on a replica:

    mysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:12345', @@global.gtid_executed) as transaction_found;
    +-------------------+
    | transaction_found |
    +-------------------+
    |                 1 |
    +-------------------+

    mysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:123450000', @@global.gtid_executed) as transaction_found;
    +-------------------+
    | transaction_found |
    +-------------------+
    |                 0 |
    +-------------------+

### Getting OWN_GTID

This is all well, but, given some `INSERT` or `UPDATE` on the `master`, how can I tell what’s the GTID associated with that transaction? There\s good news and bad news.

- Good news is, you may `SET SESSION session_track_gtids = OWN_GTID`. This makes the MySQL protocol return the GTID generated by your transaction.
- Bad news is, this isn’t a standard SQL response, and the common MySQL drivers offer you no way to get that information!

At GitHub we author our own Ruby driver, and have implemented the functionality to extract `OWN_GTID`, much like you’d extract `LAST_INSERT_ID`. But, how does one solve that without modifying the drivers? Here’s a poor person’s solution which gives you an inexact, but good enough, info. Following a write (`insert`, `delete`, `create`, …), run:

    select gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), @@global.gtid_executed)) as master_generated_gtid;

The idea is to “clean” the executed GTID set from irrelevant entries, by filtering out all ranges that do not belong to the server you’ve just written to (the `master`). The number `1000000000000000` stands for “high enough value that will never be reached in practice” – set to your own preferred value, but this value should take you beyond `300` years assuming `100,000` transactions per second.

Continue reading » “Quick hack for GTID_OWN lack”