{"id":7034,"date":"2014-10-20T08:40:04","date_gmt":"2014-10-20T06:40:04","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7034"},"modified":"2014-10-20T08:40:04","modified_gmt":"2014-10-20T06:40:04","slug":"making-uuid-and-rand-replication-safe","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/making-uuid-and-rand-replication-safe","title":{"rendered":"Making UUID() and RAND() replication safe"},"content":{"rendered":"

MySQL’s UUID()<\/strong><\/a> and RAND()<\/strong><\/a> functions both provide with (pseudo) indeterministic result. UUID()<\/strong>‘s result is moreover bound to the host on which it executes. For this reason, both are unsafe to replicate with STATEMENT<\/strong> binlog format. As an example, consider:<\/p>\n

\n
master><\/strong> create table test.uuid_test (id int, u varchar(64));\r\n\r\nmaster><\/strong> insert into test.uuid_test values (1, UUID());\r\nQuery OK, 1 row affected, 1 warning (0.03 sec)\r\n\r\nmaster><\/strong> select * from test.uuid_test;\r\n+------+--------------------------------------+\r\n| id\u00a0\u00a0 | u\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+--------------------------------------+\r\n|\u00a0\u00a0\u00a0 1 | 7e3596d8-56ac-11e4-b284-3c970ea31ea8 |\r\n+------+--------------------------------------+<\/pre>\n<\/blockquote>\n
The warning we got on the insert directly relates to the following inconsistency on a slave:<\/p>\n
\n
slave1><\/strong> select * from test.uuid_test;\r\n+------+--------------------------------------+\r\n| id\u00a0\u00a0 | u\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+--------------------------------------+\r\n|\u00a0\u00a0\u00a0 1 | 7e379d63-56ac-11e4-8477-3c970ea31ea8 |\r\n+------+--------------------------------------+<\/pre>\n<\/blockquote>\n
The data on the slave is clearly inconsistent with the master’s. The slave, replicating via STATEMENT<\/strong> binlog format, re-executes the INSERT<\/strong> command and gets a different UUID value.<\/p>\n
External<\/h4>\n
One solution to the above is to generate the UUID value from your application. By the time MySQL gets the INSERT<\/strong> statement, the UUID value is a constant string, as far as MySQL is concerned.<\/p>\n
Internal<\/h4>\n
However there’s a way to do it from within MySQL, by decoupling the UUID()<\/strong> function from the INSERT<\/strong> statement. It takes a session variable. Consider:<\/p>\n
\n
master><\/strong> set @safe_uuid := UUID();\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmaster><\/strong> insert into test.uuid_test values (2, @safe_uuid);\r\nQuery OK, 1 row affected (0.02 sec)\r\n\r\nmaster><\/strong> select * from test.uuid_test;\r\n+------+--------------------------------------+\r\n| id\u00a0\u00a0 | u\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+--------------------------------------+\r\n|\u00a0\u00a0\u00a0 1 | 7e3596d8-56ac-11e4-b284-3c970ea31ea8 |\r\n|\u00a0\u00a0\u00a0 2 | 29c51fb9-56ad-11e4-b284-3c970ea31ea8<\/strong> |\r\n+------+--------------------------------------+<\/pre>\n<\/blockquote>\n
And on a slave:<\/p>\n
\n
slave1><\/strong> select * from test.uuid_test;\r\n+------+--------------------------------------+\r\n| id\u00a0\u00a0 | u\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+--------------------------------------+\r\n|\u00a0\u00a0\u00a0 1 | 7e379d63-56ac-11e4-8477-3c970ea31ea8 |\r\n|\u00a0\u00a0\u00a0 2 | 29c51fb9-56ad-11e4-b284-3c970ea31ea8<\/strong> |\r\n+------+--------------------------------------+<\/pre>\n<\/blockquote>\n
The reason why this succeeds is that MySQL stores session variable values that are being used by DML queries in the binary log. It just so happened that @safe_uuid<\/strong> was assigned the UUID()<\/strong> value, but it could just as well have been assigned a constant or other computation. MySQL stored the resulting value into the binary log, where it is forces upon the slave to use. Check out this binary log snippet:<\/p>\n
\n
# at 14251\r\n#141018 12:57:35 server id 1\u00a0 end_log_pos 14319\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 thread_id=2\u00a0\u00a0\u00a0\u00a0 exec_time=0\u00a0\u00a0\u00a0\u00a0 error_code=0\r\nSET TIMESTAMP=1413626255\/*!*\/;\r\nSET @@session.sql_auto_is_null=0\/*!*\/;\r\nBEGIN\r\n\/*!*\/;\r\n# at 14319\r\n#141018 12:57:35 server id 1\u00a0 end_log_pos 14397\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 User_var\r\nSET @`safe_uuid`:=_utf8 0x32396335316662392D353661642D313165342D623238342D336339373065613331656138 COLLATE `utf8_general_ci`\/*!*\/;<\/strong>\r\n# at 14397\r\n#141018 12:57:35 server id 1\u00a0 end_log_pos 14509\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 thread_id=2\u00a0\u00a0\u00a0\u00a0 exec_time=0\u00a0\u00a0\u00a0\u00a0 error_code=0\r\nSET TIMESTAMP=1413626255\/*!*\/;\r\ninsert into test.uuid_test values (2, @safe_uuid)<\/strong>\r\n\/*!*\/;\r\n# at 14509\r\n#141018 12:57:35 server id 1\u00a0 end_log_pos 14536\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Xid = 145\r\nCOMMIT\/*!*\/;<\/pre>\n<\/blockquote>\n
The same can be applied for RAND()<\/strong>. Funny thing about RAND()<\/strong> is that it is already taken care of by the binary log via SET @@RAND_SEED1, SET @@RAND_SEED2<\/strong> statements (i.e. it works<\/em>), though the documentation clearly states it is unsafe.<\/p>\n
With Row Based Replication (RBR) the problem never arises in the first place since the binlog contains the values<\/em> of the new\/updated rows.<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL’s UUID() and RAND() functions both provide with (pseudo) indeterministic result. UUID()‘s result is moreover bound to the host on which it executes. For this reason, both are unsafe to replicate with STATEMENT binlog format. As an example, consider: master> create table test.uuid_test (id int, u varchar(64)); master> insert into test.uuid_test values (1, UUID()); Query […]<\/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":[74,8],"class_list":["post-7034","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-hack","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Ps","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7034","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=7034"}],"version-history":[{"count":6,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7034\/revisions"}],"predecessor-version":[{"id":7048,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7034\/revisions\/7048"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7034"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7034"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7034"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}