{"id":7327,"date":"2015-08-07T14:39:59","date_gmt":"2015-08-07T12:39:59","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7327"},"modified":"2015-08-19T10:00:11","modified_gmt":"2015-08-19T08:00:11","slug":"baffling-5-7-globalstatus-variables-issues-unclean-migration-path","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/baffling-5-7-globalstatus-variables-issues-unclean-migration-path","title":{"rendered":"Baffling 5.7 global\/status variables issues, unclean migration path"},"content":{"rendered":"<p>MySQL <strong>5.7<\/strong> introduces a change in the way we query for global variables and status variables: the <strong>INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS)<\/strong> tables are now deprecated and empty. Instead, we are to use the respective <strong>performance_schema.(global|session)_(variables|status)<\/strong> tables.<\/p>\n<p>But the change goes farther than that; there is also a security change. Oracle\u00a0created a\u00a0pitfall of <strong>2<\/strong> changes at the same time:<\/p>\n<ol>\n<li>Variables\/status moved to a different table<\/li>\n<li>Privileges required on said table<\/li>\n<\/ol>\n<p>As an example, my non-root user gets:<\/p>\n<blockquote>\n<pre>mysql&gt; show session variables like 'tx_isolation';\r\nERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'<\/pre>\n<\/blockquote>\n<p>Who gets affected by this? Nearly <em>everyone and everything<\/em>.<\/p>\n<ul>\n<li>Your Nagios will not be able to read status variables<\/li>\n<li>Your ORM will not be able to determine session variables<\/li>\n<li>Your replication user will fail connecting (see <a href=\"http:\/\/datacharmer.blogspot.nl\/2015\/08\/mysql-578-features-bugs-and-rumors.html\">this post by Giuseppe<\/a>)<\/li>\n<li>And most everyone else.<\/li>\n<\/ul>\n<p>The problem with the above is that involves two unrelated\u00a0changes to your setup, which are not entirely\u00a0simple to coordinate:<\/p>\n<ol>\n<li>Change your app code to choose the correct\u00a0schema (information_schema vs. performance_schema)<\/li>\n<li><strong>GRANT<\/strong> the permissions on your database<\/li>\n<\/ol>\n<p>Perhaps at this point you still do not consider this to be a problem. You may be thinking: <em>well, let&#8217;s first prepare by creating the GRANTs, and once that is in place, we can, at our leisure, modify the code<\/em>.<\/p>\n<p>Not so fast. Can you really that simply create those GRANTs?<!--more--><\/p>\n<h3>Migration woes<\/h3>\n<p>How do you migrate to a new MySQL version? You do not reinstall all your servers. You want an easy migration path, and that path is: introduce one or two slaves\u00a0of a newer version, see that everything works to your satisfaction, slowly upgrade all your other slaves, eventually switchover\/upgrade your master.<\/p>\n<p>This should not be any different for <strong>5.7<\/strong>. We would like to provision a <strong>5.7<\/strong> slave in our topologies and just see that everything works. Well, we have, and things don&#8217;t just work. Our Nagios stops working for that <strong>5.7<\/strong> slave. <em>Orchestrator<\/em> started complaining (by this time I&#8217;ve <a href=\"https:\/\/github.com\/outbrain\/orchestrator\/releases\/tag\/v1.4.291\">already fixed it<\/a> to be more tolerant for the <strong>5.7<\/strong> problems so no crashes here).<\/p>\n<p>I hope you see the problem by now.<\/p>\n<blockquote><p>You cannot issue a <strong>GRANT SELECT ON performance_schema.global_variables TO &#8216;&#8230;&#8217;<\/strong> on your <strong>5.6<\/strong> master.<\/p><\/blockquote>\n<p>The table simply does not exist there, which means the statement will not go to binary logs, which means it will not replicate on your <strong>5.7<\/strong> slave, which means you will not\u00a0be able to <strong>SHOW GLOBAL VARIABLES<\/strong> on your slave, which means everything remains broken.<\/p>\n<p>Yes, you can issue this directly on your <strong>5.7<\/strong> slaves. It&#8217;s <em>doable<\/em>, but <em>undesired<\/em>. It&#8217;s ugly in terms of automation (and will quite possibly break some assumptions and sanity checks your automation uses); in terms of validity testing. It&#8217;s unfriendly to GTID (make sure to <strong>SET SQL_LOG_BIN=0<\/strong> before that).<\/p>\n<h3>WHY in the first place?<\/h3>\n<p>It seems like a security thing. I&#8217;m not sure whether this was intended. So you prevent a <strong>SHOW GLOBAL VARIABLES<\/strong> for a normal user. Makes sense. And yet:<\/p>\n<blockquote>\n<pre>mysql&gt; show global variables like 'hostname';\r\nERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'global_variables'\r\n\r\nmysql&gt; select @@global.hostname;\r\n+---------------------+\r\n| @@global.hostname   |\r\n+---------------------+\r\n| myhost.mydomain.com |\r\n+---------------------+\r\n\r\nmysql&gt; select @@version;\r\n+--------------+\r\n| @@version    |\r\n+--------------+\r\n| 5.7.8-rc-log |\r\n+--------------+\r\n\r\n<\/pre>\n<\/blockquote>\n<p>Seems like I&#8217;m allowed access to that info after all. So it&#8217;s not strictly a security design decision. For status variable, I admit, I don&#8217;t have a similar workaround.<\/p>\n<h3>Solutions?<\/h3>\n<p>The following are meant to be solutions, but do not really solve the problem:<\/p>\n<ul>\n<li><strong>SHOW<\/strong> commands. <strong>SHOW GLOBAL|SESSION VARIABLES|STATUS<\/strong> will work properly, and will implicitly know whether to provide the results\u00a0via <strong>information_schema<\/strong> or <strong>performance_schema<\/strong>\u00a0tables.\n<ul>\n<li>But, aren&#8217;t we meant to be happier with <strong>SELECT<\/strong> queries? So that I can really do stuff that is smarter than <strong>LIKE &#8216;variable_name%&#8217;<\/strong>?<\/li>\n<li>And of course you cannot use <strong>SHOW<\/strong> in server side cursors. Your stored routines are in a mess now.<\/li>\n<li>This does not solve the GRANTs problem.<\/li>\n<\/ul>\n<\/li>\n<li><strong><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_show_compatibility_56\">show_compatibility_56<\/a><\/strong>: an introduced variable in <strong>5.7<\/strong>, boolean. It truly is a time-travel-paradox novel in disguise, in multiple respects.\n<ul>\n<li>Documentation introduces it, and says it is deprecated.\n<ul>\n<li>time-travel-paradox :O<\/li>\n<\/ul>\n<\/li>\n<li>But it actually works in <strong>5.7.8<\/strong> (latest)\n<ul>\n<li>time-travel-paradox plot thickens<\/li>\n<\/ul>\n<\/li>\n<li>Your automation scripts do not know in advance whether your MySQL has this variable\n<ul>\n<li>Hence <strong>SELECT @@global.show_compatibility_56<\/strong> will produce an error on <strong>5.6<\/strong><\/li>\n<li>But the &#8220;safe&#8221; way of\u00a0<strong>SHOW GLOBAL VARIABLES LIKE &#8216;show_compatibility_56&#8217;<\/strong> will fail on a privilege error on <strong>5.7<\/strong><\/li>\n<li>time-travel-paradox :O<\/li>\n<\/ul>\n<\/li>\n<li>Actually advised by my colleague Simon J. Mudd, <strong>show_compatibility_56<\/strong> defaults to <strong>OFF<\/strong>. I <em>support<\/em> this line of thought. Or else it&#8217;s <strong>old_passwords=1<\/strong> all over again.<\/li>\n<li><strong>show_compatibility_56<\/strong>\u00a0doesn&#8217;t solve the GRANTs problem.<\/li>\n<li>This does not solve any migration path. It just postpones the moment when I will hit the same problem. When I flip the variable from <strong>&#8220;1&#8221;<\/strong> to <strong>&#8220;0&#8221;<\/strong>, I&#8217;m back at square one.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Suggestion<\/h3>\n<p>I claim security is not the issue, as presented above. I claim Oracle will yet again fall into the trap of no-easy-way-to-migrate-to-GTID in <strong>5.6<\/strong> if the current solution\u00a0is unchanged. I claim that there have been too many changes at once. Therefore, I suggest one of the alternative two flows:<\/p>\n<ol>\n<li><strong>Flow 1<\/strong>: keep <strong>information_schema<\/strong>, later\u00a0migration into <strong>performance_schema<\/strong>\n<ul>\n<li>In <strong>5.7<\/strong>,\u00a0<strong>information_schema<\/strong>\u00a0tables should still produce the data.<\/li>\n<li>No security constraints on\u00a0<strong>information_schema<\/strong><\/li>\n<li>Generate WARNINGs on reading from\u00a0<strong>information_schema<\/strong>\u00a0(&#8220;&#8230;this will be deprecated&#8230;&#8221;)<\/li>\n<li><strong>performance_schema <\/strong><em>also available<\/em>. With security constraints, whatever.<\/li>\n<li>In <strong>5.8<\/strong> remove <strong>information_schema<\/strong> tables; we are left with <strong>performance_schema<\/strong> only.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Flow 2<\/strong>: easy migration into\u00a0<strong>performance_schema<\/strong>:\n<ul>\n<li>In\u00a0<strong>5.7<\/strong>, <strong>performance_schema<\/strong> tables should\u00a0not require any special\u00a0privileges.\u00a0Any user can read from them.<\/li>\n<li>Keep\u00a0<strong>show_compatibility_56\u00a0<\/strong>as it is.<\/li>\n<li><strong>SHOW<\/strong> commands choose between <strong>information_schema<\/strong> or <strong>performance_schema<\/strong> on their own &#8212; just as things are done now.<\/li>\n<li>In <strong>5.8<\/strong>,\u00a0<strong>performance_schema<\/strong> tables\u00a0will require <strong>SELECT<\/strong>\u00a0privileges.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p>As always, I love the work done by the engineers; and I love how they listen to the community.<\/p>\n<p>Comments are most welcome. Have I missed the simple solution here? Are there even more complications to these features? Thoughts on my suggested two flows?<\/p>\n<h3>[UPDATE 2015-08-19]<\/h3>\n<p>Please <a href=\"http:\/\/www.tocker.ca\/2015\/08\/18\/a-followup-on-show_compatibility_56.html\">see this followup<\/a> by Morgan Tocker of Oracle.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables. But the change goes farther than that; there is also a security change. Oracle\u00a0created a\u00a0pitfall of 2 changes at the same time: [&hellip;]<\/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":[11,24,46,89,51,108,117,16,59],"class_list":["post-7327","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-information_schema","tag-monitoring","tag-new-features","tag-opinions","tag-orchestrator","tag-performance_schema","tag-security","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Ub","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7327","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=7327"}],"version-history":[{"count":11,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7327\/revisions"}],"predecessor-version":[{"id":7346,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7327\/revisions\/7346"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}