{"id":7974,"date":"2019-12-11T10:00:00","date_gmt":"2019-12-11T08:00:00","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7974"},"modified":"2019-12-11T10:00:00","modified_gmt":"2019-12-11T08:00:00","slug":"quick-hack-for-gtid_own-lack","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/quick-hack-for-gtid_own-lack","title":{"rendered":"Quick hack for GTID_OWN lack"},"content":{"rendered":"

One of the benefits of MySQL GTIDs is that each server remembers all<\/em> GTID entries ever executed. Normally these would be ranges, e.g. 0041e600-f1be-11e9-9759-a0369f9435dc:1-3772242<\/code> 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<\/code>.<\/p>\n

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

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

mysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:12345', @@global.gtid_executed) as transaction_found;\n+-------------------+\n| transaction_found |\n+-------------------+\n|                 1 |\n+-------------------+\n\nmysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:123450000', @@global.gtid_executed) as transaction_found;\n+-------------------+\n| transaction_found |\n+-------------------+\n|                 0 |\n+-------------------+\n<\/code><\/pre>\n
Getting OWN_GTID<\/h3>\n
This is all well, but, given some INSERT<\/code> or UPDATE<\/code> on the master<\/code>, how can I tell what’s the GTID associated with that transaction? There\\s good news and bad news.<\/p>\n
    \n
  • Good news is, you may SET SESSION session_track_gtids = OWN_GTID<\/code>. This makes the MySQL protocol return the GTID generated by your transaction.<\/li>\n
  • Bad news is, this isn’t a standard SQL response, and the common MySQL drivers offer you no way to get that information!<\/li>\n<\/ul>\n
    At GitHub we author our own Ruby driver, and have implemented the functionality to extract OWN_GTID<\/code>, much like you’d extract LAST_INSERT_ID<\/code>. 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<\/code>, delete<\/code>, create<\/code>, …), run:<\/p>\n
    select gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), @@global.gtid_executed)) as master_generated_gtid;\n<\/code><\/pre>\n
    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<\/code>). The number 1000000000000000<\/code> 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<\/code> years assuming 100,000<\/code> transactions per second.<\/p>\n
    <\/p>\n
    The value you get is the range on the master itself. e.g.:<\/p>\n
    mysql> select gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), @@global.gtid_executed)) as master_generated_gtid;\n+-------------------------------------------------+\n| master_generated_gtid                           |\n+-------------------------------------------------+\n| dc103953-1598-11ea-82a7-008cfa5440e4:1-35807176 |\n+-------------------------------------------------+\n<\/code><\/pre>\n
    You may further parse the above to extract dc103953-1598-11ea-82a7-008cfa5440e4:35807176<\/code> if you want to hold on to the latest GTID entry. Now, this entry isn’t necessarily your own<\/em>. Between the time of your write and the time of your GTID query, other writes will have taken place. But the entry you get is either your own or a later one. If you can find that entry on a replica, that means your write is included on the replica.<\/p>\n
    One may wonder, why do we need to extract the value at all? Why not just select @@global.gtid_executed<\/code>? Why filter only the master<\/code>‘s UUID? Logically, the answer is the same if you do that. But in practice, your query may be unfortunate enough to return some:<\/p>\n
    select @@global.gtid_executed \\G\n\ne71f0cdb-b8ef-11e9-9361-008cfa542442:1-83331,\ne742d87f-dea7-11e9-be6d-008cfa542c9e:1-18485,\ne7880c0e-ac54-11e9-865a-008cfa544064:1-7331973,\ne82043c6-c7d9-11e9-9413-008cfa5440e4:1-61692,\ne902678b-b046-11e9-a281-008cfa542c9e:1-83108,\ne90d7ff9-e35e-11e9-a9a0-008cfa544064:1-18468,\ne929a635-bb40-11e9-9c0d-008cfa5440e4:1-139348,\ne9351610-ef1b-11e9-9db4-008cfa5440e4:1-33460918,\ne938578d-dc41-11e9-9696-008cfa542442:1-18232,\ne947f165-cd53-11e9-b7a1-008cfa5440e4:1-18480,\ne9733f37-d537-11e9-8604-008cfa5440e4:1-18396,\ne97a0659-e423-11e9-8433-008cfa542442:1-18237,\ne98dc1f7-e0f8-11e9-9bbd-008cfa542c9e:1-18482,\nea16027a-d20e-11e9-9845-008cfa542442:1-18098,\nea1e1aa6-e74a-11e9-a7f2-008cfa544064:1-18450,\nea8bc1bd-dd06-11e9-a10c-008cfa542442:1-18203,\neae8c750-aaca-11e9-b17c-008cfa544064:1-85990,\neb1e41e9-af81-11e9-9ceb-008cfa544064:1-86220,\neb3c9b3b-b698-11e9-b67a-008cfa544064:1-18687,\nec6daf7e-b297-11e9-a8a0-008cfa542c9e:1-80652,\neca4af92-c965-11e9-a1f3-008cfa542c9e:1-18333,\necd110b9-9647-11e9-a48f-008cfa544064:1-24213,\ned26890e-b10b-11e9-a79d-008cfa542c9e:1-83450,\ned92b3bf-c8a0-11e9-8612-008cfa542442:1-18223,\neeb60c82-9a3d-11e9-9ea5-008cfa544064:1-1943152,\neee43e06-c25d-11e9-ba23-008cfa542442:1-105102,\neef4a7fb-b438-11e9-8d4b-008cfa5440e4:1-74717,\neefdbd3b-95b3-11e9-833d-008cfa544064:1-39415,\nef087062-ba7b-11e9-92de-008cfa5440e4:1-9726172,\nef507ff0-98b3-11e9-8b15-008cfa5440e4:1-928030,\nef662471-9a3b-11e9-bd2e-008cfa542c9e:1-954800,\nf002e9f7-97ee-11e9-bed0-008cfa542c9e:1-5180743,\nf0233228-e9a1-11e9-a142-008cfa542c9e:1-18583,\nf04780c4-a864-11e9-9f28-008cfa542c9e:1-83609,\nf048acd9-b1d2-11e9-a0b6-008cfa544064:1-70663,\nf0573d8c-9978-11e9-9f73-008cfa542c9e:1-85642135,\nf0b0a37c-c89c-11e9-804c-008cfa5440e4:1-18488,\nf0cfe1ac-e5af-11e9-bc09-008cfa542c9e:1-18552,\nf0e4997c-cbc9-11e9-9179-008cfa542442:1-1655552,\nf24e481c-b5c4-11e9-aff0-008cfa5440e4:1-83015,\nf4578c4b-be6d-11e9-982e-008cfa5440e4:1-132701,\nf48bce80-e99f-11e9-94f4-a0369f9432f4:1-18460,\nf491adf1-9b04-11e9-bc71-008cfa542c9e:1-962823,\nf5d3db74-a929-11e9-90e8-008cfa5440e4:1-75379,\nf6696ba7-b750-11e9-b458-008cfa542c9e:1-83096,\nf714cb4c-dab7-11e9-adb9-008cfa544064:1-18413,\nf7b781a9-cbbd-11e9-affb-008cfa542442:1-18169,\nf81f7729-b10d-11e9-b29b-008cfa542442:1-86820,\nf88a3298-e903-11e9-88d0-a0369f9432f4:1-18548,\nf9467b29-d78c-11e9-b1a2-008cfa5440e4:1-18492,\nf9c08f5c-e4ea-11e9-a76c-008cfa544064:1-1667611,\nfa633abf-cee3-11e9-9346-008cfa542442:1-18361,\nfa8b0e64-bb42-11e9-9913-008cfa542442:1-140089,\nfa92234c-cc90-11e9-b337-008cfa544064:1-18324,\nfa9755eb-e425-11e9-907d-008cfa542c9e:1-1668270,\nfb7843d5-eb38-11e9-a1ff-a0369f9432f4:1-1668957,\nfb8ceae5-dd08-11e9-9ed3-008cfa5440e4:1-18526,\nfbf9970e-bc07-11e9-9e4f-008cfa5440e4:1-136157,\nfc0ffaee-98b1-11e9-8574-008cfa542c9e:1-940999,\nfc9bf1e4-ee54-11e9-9ce9-008cfa542c9e:1-18189,\nfca4672f-ac56-11e9-8a83-008cfa542442:1-82014,\nfcebaa05-dab5-11e9-8356-008cfa542c9e:1-18490,\nfd0c88b1-ad1b-11e9-bf3a-008cfa5440e4:1-75167,\nfd394feb-e4e4-11e9-bd09-008cfa5440e4:1-18574,\nfd687577-b048-11e9-b429-008cfa542442:1-83479,\nfdb18995-a79f-11e9-a28d-008cfa542442:1-82351,\nfdc72b7f-b696-11e9-ade9-008cfa544064:1-57674,\nff1f3b6b-c967-11e9-ae04-008cfa544064:1-18503,\nff6fe7dc-c186-11e9-9bb4-008cfa5440e4:1-103192,\nfff9dd94-ed95-11e9-90b7-008cfa544064:1-911039\n<\/code><\/pre>\n
    This can happen when you fail over to a new master, multiple times; it happens when you don’t recycle UUIDs, when you provision new hosts and let MySQL pick their UUID. Returning this amount of data per query<\/em> is an excessive overhead, hence why we extract the master<\/code>‘s UUID only, which is guaranteed to be limited in size.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[113,8],"class_list":["post-7974","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-gtid","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-24C","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=7974"}],"version-history":[{"count":14,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7974\/revisions"}],"predecessor-version":[{"id":7988,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7974\/revisions\/7988"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}