{"id":6468,"date":"2013-08-13T05:39:12","date_gmt":"2013-08-13T03:39:12","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6468"},"modified":"2013-08-13T05:39:12","modified_gmt":"2013-08-13T03:39:12","slug":"common_schema-2-2-better-queryscript-isolation-tokudb-table_rotate-split-params","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/common_schema-2-2-better-queryscript-isolation-tokudb-table_rotate-split-params","title":{"rendered":"common_schema 2.2: better QueryScript isolation &#038; cleanup; TokuDB; table_rotate, split params"},"content":{"rendered":"<p><a href=\"https:\/\/code.google.com\/p\/common-schema\/\"><strong>common_schema 2.2<\/strong><\/a> is released. This is shortly after the 2.1 release; it was only meant as bug fixes release but some interesting things came up, leading to new functionality.<\/p>\n<p>Highlights of the <strong>2.2<\/strong> release:<\/p>\n<ul>\n<li>Better QueryScript isolation &amp; cleanup: isolation improved across replication topology, cleanup done even on error<\/li>\n<li>Added <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/tokudb_views.html\">TokuDB related views<\/a><\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html\"><strong>split<\/strong><\/a> with &#8220;index&#8221; hint (Ike, this is for you)<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/table_rotate.html\"><strong>table_rotate()<\/strong><\/a>: a <em>logrotate<\/em>-like mechanism for tables<\/li>\n<li>better throw()<\/li>\n<\/ul>\n<p>Drill down:<\/p>\n<h4>Better QueryScript isolation &amp; cleanup<\/h4>\n<p><em>common_schema<\/em> <strong>2.1<\/strong> introduced persistent tables for QueryScript. This also introduced the problem of isolating concurrent scripts, all reading from and writing to shared tables. In <strong>2.1<\/strong> isolation was based on session id. However although unique per machine, collisions were possible across replication topology: a script could be issued on master, another on slave (I have such use cases) and both use same (local) session id.<\/p>\n<p>With 2.2 isolation is based on server_id &amp; session id combination; this is unique across a replication topology.<\/p>\n<p>Until <strong>2.1<\/strong>, QueryScript used temporary tables. This meant any error would just break the script, and the tables were left (isolated as they were, and auto-destroyed in time). With persistent tables a script throwing an error meant legacy code piling up. With <em>common_schema<\/em> <strong>2.2<\/strong> and on MySQL &gt;= <strong>5.5<\/strong> all exceptions are caught, cleanup is made, leaving exceptions to be <strong>RESIGNAL<\/strong>led.<\/p>\n<h4>TokuDB views<\/h4>\n<p>A couple TokuDB related views help out in converting to TokuDB and in figuring out tables status on disk:<!--more--><\/p>\n<ul>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_alter_table_tokudb.html\"><strong>sql_alter_table_tokudb<\/strong><\/a> will help you out to generate the complex ALTER statement to TokuDB engine if you happen to used COMPRESSED InnoDB tables with KEY_BLOCK_SIZE specified. The view generates a complex DROP KEYs &amp; ADD KEYs statementl this is due to bug &#8230;<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/tokudb_file_map.html\"><strong>tokudb_file_map<\/strong><\/a> simplifies the <strong>INFORMATION_SCHEMA.Tokudb_file_map<\/strong> table: the original view is not normalized and is difficult to interpret and follow when your table had many indexes or is partitioned (I will write more on this shortly). with <em>common_schema<\/em>&#8216;s <strong>tokudb_file_map<\/strong> you get, per table, the list of files representing that table, along with a couple Shell commands to tell you <em>the thing you want to know most<\/em>: &#8220;what is the size of my TokuDB table on disk?&#8221;<\/li>\n<\/ul>\n<h4>split<\/h4>\n<p>QueryScript&#8217;s <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html\"><strong>split<\/strong><\/a> device now supports the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html#parameters\">&#8220;<strong>index<\/strong>&#8221; parameter<\/a> (or <em>hint<\/em>), which instructs the split() operation to use an explicitly named index. If used, the index must exist and must be UNIQUE.<\/p>\n<h4>table_rotate()<\/h4>\n<p>Rotate your tables a-la logrotate with <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/table_rotate.html\"><strong>table_rotate()<\/strong><\/a>: generate a new, identical, empty table, version your table, pushing older versions along the line; optionally drop older versions. You get the picture. Got some nice use case behind this on cleaning up a test database.<\/p>\n<h4>throw()<\/h4>\n<p>On MySQL &gt;= <strong>5.5<\/strong> <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/throw.html\"><strong>throw()<\/strong><\/a> uses SIGNAL. No more weird <em>&#8220;table `Unknown column &#8216;$t&#8217; in &#8216;field list&#8217;` does not exist&#8221;<\/em> messages. Just plain old:<\/p>\n<blockquote>\n<pre>ERROR 1054 (42S22): Unknown column '$t' in 'field list'<\/pre>\n<\/blockquote>\n<h4>Get it<\/h4>\n<p><em>common_schema<\/em> is free and open source. It is licensed under GPL v2. This is where you can <a href=\"https:\/\/code.google.com\/p\/common-schema\/\">find and download latest common_schema release<\/a>.<\/p>\n<p>Your input is welcome! Please submit your bugs, or otherwise share your experience with <em>common_schema<\/em>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>common_schema 2.2 is released. This is shortly after the 2.1 release; it was only meant as bug fixes release but some interesting things came up, leading to new functionality. Highlights of the 2.2 release: Better QueryScript isolation &amp; cleanup: isolation improved across replication topology, cleanup done even on error Added TokuDB related views split with [&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":[67,120,57,76],"class_list":["post-6468","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-development","tag-open-source","tag-queryscript"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Gk","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6468","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=6468"}],"version-history":[{"count":7,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6468\/revisions"}],"predecessor-version":[{"id":6479,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6468\/revisions\/6479"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}