{"id":3794,"date":"2011-07-13T06:25:24","date_gmt":"2011-07-13T04:25:24","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3794"},"modified":"2011-07-13T06:25:24","modified_gmt":"2011-07-13T04:25:24","slug":"announcing-common_schema-common-views-routines-for-mysql","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/announcing-common_schema-common-views-routines-for-mysql","title":{"rendered":"Announcing common_schema: common views &#038; routines for MySQL"},"content":{"rendered":"<p>Today I have released <a title=\"common_schema\" href=\"http:\/\/code.openark.org\/forge\/common_schema\">common_schema<\/a>, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.<\/p>\n<h4>What does it do?<\/h4>\n<p>There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes &amp; transactions, security, internals&#8230; There are basic functions answering for common needs.<\/p>\n<p>Some of the views\/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.<\/p>\n<p>Here are a few highlights:<\/p>\n<ul>\n<li>Did you know you can work out <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/global_status_diff_nonzero.html\">simple monitoring<\/a> of your server with a <em>query<\/em>?\u00a0 There&#8217;s a view to do that for you.<\/li>\n<li>How about showing just <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/processlist_top.html\">the good parts of the processlist<\/a>?<\/li>\n<li>Does your schema have <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/redundant_keys.html\">redundant keys<\/a>?<\/li>\n<li>Or InnoDB tables with <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/no_pk_innodb_tables.html\">no PRIMARY KEY<\/a>?<\/li>\n<li>Is AUTO_INCREMENT <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/auto_increment_columns.html\">running out of space<\/a>?<\/li>\n<li>Can I get the SQL statements to <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_foreign_keys.html\">generate my FOREIGN KEYs<\/a>? To drop them?<\/li>\n<li>And can we finally get <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_show_grants.html\">SHOW GRANTS for all accounts<\/a>, and as an <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_grants.html\">SQL query<\/a>?<\/li>\n<li>Ever needed a <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/general_functions.html#crc64\">64 bit CRC function<\/a>?<\/li>\n<li>And aren&#8217;t you tired of writing the cumbersome SUBSTRING_INDEX(SUBSTRING_INDEX(str, &#8216;,&#8217;, 3), &#8216;,&#8217;, -1)? <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/string_functions.html#split_token\">There&#8217;s an alternative<\/a>.<\/li>\n<\/ul>\n<p>There&#8217;s more. Take a look at the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/introduction.html\">common_schema documentation<\/a> for full listing. And it&#8217;s evolving: I&#8217;ve got quite a few ideas already for future components.<\/p>\n<p>Some of these views rely on heavyweight INFORMATION_SCHEMA tables. You should be aware of the impact and <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/risks.html\">risks<\/a>.<\/p>\n<h4>What do I need to install?<\/h4>\n<p>There&#8217;s no script or executable file. It&#8217;s just a schema. The distribution in an SQL file which generates <em>common_schema<\/em>. Much like a dump file.<\/p>\n<h4><!--more-->What are the system requirements?<\/h4>\n<p>It&#8217;s just between you and your MySQL. There are currently three distribution files, dedicated for different versions of MySQL (and allowing for increased functionality):<\/p>\n<ul>\n<li><strong>common_schema_mysql_51<\/strong>: fits all MySQL &gt;= 5.1 distributions<\/li>\n<li><strong>common_schema_innodb_plugin<\/strong>: fits MySQL &gt;= 5.1, with InnoDB plugin + INFORMATION_SCHEMA tables enabled<\/li>\n<li><strong>common_schema_percona_server<\/strong>: fits Percona Server &gt;= 5.1<\/li>\n<\/ul>\n<p>Refer to the <a rel=\"nofollow\" href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/download.html\">documentation<\/a> for more details.<\/p>\n<h4>What are the terms of use?<\/h4>\n<p><em>common_schema<\/em> is released under the <a href=\"http:\/\/www.opensource.org\/licenses\/bsd-license.php\">BSD license<\/a>.<\/p>\n<h4>Where can I download it?<\/h4>\n<p>On the <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema project page<\/a>. Enjoy it!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server. What does it do? There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes &amp; transactions, security, internals&#8230; There are [&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":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[25,67,18,120,26,24,14,46,57,17,16,21,59],"class_list":["post-3794","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-analysis","tag-common_schema","tag-data-types","tag-development","tag-indexing","tag-information_schema","tag-innodb","tag-monitoring","tag-open-source","tag-schema","tag-security","tag-sql","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Zc","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3794","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=3794"}],"version-history":[{"count":37,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3794\/revisions"}],"predecessor-version":[{"id":3835,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3794\/revisions\/3835"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3794"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3794"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3794"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}