{"id":5941,"date":"2013-01-14T08:25:07","date_gmt":"2013-01-14T06:25:07","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5941"},"modified":"2013-01-14T08:51:05","modified_gmt":"2013-01-14T06:51:05","slug":"common_schema-1-3-security-goodies-parameterized-split-json-to-xml-query-checksum","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/common_schema-1-3-security-goodies-parameterized-split-json-to-xml-query-checksum","title":{"rendered":"common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum"},"content":{"rendered":"<p>common_schema <strong>1.3<\/strong> is released and is <a href=\"http:\/\/code.google.com\/p\/common-schema\">available for download<\/a>. New and noteworthy in this version:<\/p>\n<ul>\n<li>Parameterized <strong><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html\">split()<\/a><\/strong>: take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/duplicate_grantee.html\"><strong>duplicate_grantee()<\/strong><\/a>: copy+paste existing accounts along with their full set of privileges<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/similar_grants.html\"><strong>similar_grants<\/strong><\/a>: find which accounts share the exact same set of privileges (i.e. have the same <em>role<\/em>)<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/json_to_xml.html\"><strong>json_to_xml()<\/strong><\/a>: translate any valid JSON object into its equivalent XML form<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/extract_json_value.html\"><strong>extract_json_value()<\/strong><\/a>: use XPath notation to extract info from JSON data, just as you would from XML<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_checksum.html\"><strong>query_checksum()<\/strong><\/a>: given a query, calculate a checksum on the result set<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/random_hash.html\"><strong>random_hash()<\/strong><\/a>: get a 40 hexadecimal digits random hash, using a reasonably large changing input<\/li>\n<\/ul>\n<p>Let&#8217;s take a closer look at the above:<\/p>\n<h4>Parameterized split()<\/h4>\n<p><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html\"><strong>split<\/strong><\/a> takes your bulk query and automagically breaks it down into smaller pieces. So instead of one huge <strong>UPDATE<\/strong> or <strong>DELETE<\/strong> or <strong>INSERT..SELECT<\/strong> transaction, you get many smaller transactions, each with smaller impact on I\/O, locks, CPU.<\/p>\n<p>As of <strong>1.3<\/strong>, <em>split()<\/em> gets more exposed: you can have some control on its execution, and you also get a lot of very interesting info during operation.<\/p>\n<p>Here&#8217;s an example of <em>split()<\/em> control:<\/p>\n<blockquote>\n<pre>set @script := \"\r\n\u00a0 <strong>split<\/strong>({<em>start<\/em>:7015, <em>step<\/em>:2000} : <span style=\"color: #3366ff;\">UPDATE sakila.rental SET return_date = return_date + INTERVAL 1 DAY<\/span>) \r\n\u00a0\u00a0\u00a0 <strong>throttle<\/strong> 1;\r\n\";\r\ncall common_schema.run(@script);<\/pre>\n<\/blockquote>\n<p>In the above we choose a split size of 2,000 rows at a time; but we also choose to only start with <strong>7015<\/strong>, skipping all rows prior to that value. Just what is that value? It depends on the splitting key (and see next example for just that); but in this table we can safely assume this is the <strong>rental_id<\/strong> <strong>PRIMARY KEY<\/strong> of the table.<\/p>\n<p>You don&#8217;t <em>have to<\/em> use these control <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html#parameters\">parameters<\/a>. But they can save you some time and effort.<!--more--><\/p>\n<p>And, look at some interesting info about the <em>splitting<\/em> process:<\/p>\n<blockquote>\n<pre>set @script := \"\r\n\u00a0 <strong>split<\/strong>(<span style=\"color: #339966;\">sakila.film_actor<\/span>) \r\n\u00a0\u00a0\u00a0 <span style=\"color: #3366ff;\"><strong>select<\/strong><\/span> $split_columns <span style=\"color: #3366ff;\">as columns<\/span>, $split_range_start <span style=\"color: #3366ff;\">as range_start<\/span>, $split_range_end <span style=\"color: #3366ff;\">as range_end<\/span>\r\n\";\r\ncall common_schema.run(@script);\r\n+----------------------+-------------+------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0 |\r\n+----------------------+-------------+------------+\r\n| `actor_id`,`film_id` | '1','1'\u00a0\u00a0\u00a0\u00a0 | '39','293' |\r\n+----------------------+-------------+------------+\r\n\r\n+----------------------+-------------+------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0 |\r\n+----------------------+-------------+------------+\r\n| `actor_id`,`film_id` | '39','293'\u00a0 | '76','234' |\r\n+----------------------+-------------+------------+\r\n\r\n+----------------------+-------------+-------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0\u00a0 |\r\n+----------------------+-------------+-------------+\r\n| `actor_id`,`film_id` | '76','234'\u00a0 | '110','513' |\r\n+----------------------+-------------+-------------+\r\n\r\n+----------------------+-------------+-------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0\u00a0 |\r\n+----------------------+-------------+-------------+\r\n| `actor_id`,`film_id` | '110','513' | '146','278' |\r\n+----------------------+-------------+-------------+\r\n\r\n+----------------------+-------------+-------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0\u00a0 |\r\n+----------------------+-------------+-------------+\r\n| `actor_id`,`film_id` | '146','278' | '183','862' |\r\n+----------------------+-------------+-------------+\r\n\r\n+----------------------+-------------+-------------+\r\n| columns\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | range_start | range_end\u00a0\u00a0 |\r\n+----------------------+-------------+-------------+\r\n| `actor_id`,`film_id` | '183','862' | '200','993' |\r\n+----------------------+-------------+-------------+<\/pre>\n<\/blockquote>\n<p>In the above you get to be told exactly how table splitting occurs: you are being told what columns are used to split the table, and what range of values is used in each step. There&#8217;s more to it: read the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_split.html\">split() documentation<\/a>.<\/p>\n<h4>similar_grants<\/h4>\n<p>Out of your <strong>100<\/strong> different grants, which ones share the exact same set of privileges? MySQL has non notion of <em>roles<\/em>, but that doesn&#8217;t mean the notion does not exist. Multiple accounts share the same restrictions and privileges. Use <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/similar_grants.html\"><strong>similar_grants<\/strong><\/a> to find out which. You might just realize there&#8217;s a few redundant accounts in your system.<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM similar_grants;\r\n+-------------------------------+----------------+-------------------------------------------------------+\r\n| sample_grantee                | count_grantees | similar_grantees                                      |\r\n+-------------------------------+----------------+-------------------------------------------------------+\r\n| 'root'@'127.0.0.1'            |              3 | <span style=\"color: #3366ff;\">'root'@'127.0.0.1'<\/span>,<span style=\"color: #0000ff;\">'root'@'myhost'<\/span>,<span style=\"color: #333399;\">'root'@'localhost'<\/span> |\r\n| 'repl'@'10.%'                 |              2 | <span style=\"color: #008000;\">'repl'@'10.%'<\/span>,<span style=\"color: #808000;\">'replication'@'10.0.0.%'<\/span>                |\r\n| 'apps'@'%'                    |              1 | 'apps'@'%'                                            |\r\n| 'gromit'@'localhost'          |              1 | 'gromit'@'localhost'                                  |\r\n| 'monitoring_user'@'localhost' |              1 | 'monitoring_user'@'localhost'                         |\r\n+-------------------------------+----------------+-------------------------------------------------------+<\/pre>\n<\/blockquote>\n<h4>duplicate_grantee()<\/h4>\n<p>Provide an existing account, and name your new, exact duplicate account. The complete set of privileges is copied, and so is the password. <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/duplicate_grantee.html\"><strong>duplicate_grantee()<\/strong><\/a> is your Copy+Paste of MySQL accounts.<\/p>\n<p>Let&#8217;s begin with some pre-existing account and see how it duplicates:<\/p>\n<blockquote>\n<pre>mysql&gt; show grants for <span style=\"color: #000080;\">'world_user'@'localhost'<\/span>;\r\n+------------------------------------------------------------------------------------------------------------------------+\r\n| Grants for world_user@localhost\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\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------------------------------------------------------------------------------------------------------------+\r\n| GRANT USAGE ON *.* TO 'world_user'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| GRANT ALL PRIVILEGES ON `world`.* TO 'world_user'@'localhost'\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sakila`.`get_customer_balance` TO 'world_user'@'localhost' WITH GRANT OPTION |\r\n+------------------------------------------------------------------------------------------------------------------------+\r\n\r\nmysql&gt; call <strong>duplicate_grantee<\/strong>(<span style=\"color: #000080;\">'world_user@localhost'<\/span>, <span style=\"color: #000080;\">'copied_user@10.0.0.%'<\/span>);\r\nQuery OK, 0 rows affected (0.06 sec)\r\n\r\nmysql&gt; show grants for <span style=\"color: #000080;\">'copied_user'@'10.0.0.%'<\/span>;\r\n+------------------------------------------------------------------------------------------------------------------------+\r\n| Grants for copied_user@10.0.0.%\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\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------------------------------------------------------------------------------------------------------------+\r\n| GRANT USAGE ON *.* TO 'copied_user'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| GRANT ALL PRIVILEGES ON `world`.* TO 'copied_user'@'10.0.0.%'\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sakila`.`get_customer_balance` TO 'copied_user'@'10.0.0.%' WITH GRANT OPTION |\r\n+------------------------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>The routine is quite relaxed in grantee format. <strong>copied_user@10.0.0.%<\/strong>, <strong>copied_user@&#8217;10.0.0.%&#8217;<\/strong> and <strong>&#8216;copied_user&#8217;@&#8217;10.0.0.%&#8217;<\/strong> are all just fine, and represent the same account. Saves trouble with all that quoting.<\/p>\n<h4>json_to_xml()<\/h4>\n<p>JSON is becoming increasingly popular in storing dynamically-structured data. XML&#8217;s tags overhead and its human unfriendliness make it less popular today. However, the two share similar concepts, and conversion between the two is possible. <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/json_to_xml.html\"><strong>json_to_xml()<\/strong><\/a> will translate your valid JSON data into its equivalent XML format. The rules are simple (all-nodes-and-data, no attributes, arrays as repeating nodes, objects as subnodes) and the results are valid XML objects.<\/p>\n<p>Sample data taken from <a href=\"http:\/\/json.org\/example.html\">json.org<\/a>:<\/p>\n<blockquote>\n<pre>mysql&gt; SET @json := '\r\n<span style=\"color: #000080;\">{\r\n  \"menu\": {\r\n    \"id\": \"file\",\r\n    \"value\": \"File\",\r\n    \"popup\": {\r\n      \"menuitem\": [\r\n        {\"value\": \"New\", \"onclick\": \"CreateNewDoc()\"},\r\n        {\"value\": \"Open\", \"onclick\": \"OpenDoc()\"},\r\n        {\"value\": \"Close\", \"onclick\": \"CloseDoc()\"}\r\n      ]\r\n    }\r\n  }\r\n}<\/span>\r\n';\r\n\r\nmysql&gt; SELECT <strong>json_to_xml(@json)<\/strong> AS <strong>xml<\/strong> \\G\r\n*************************** 1. row ***************************\r\n<strong>xml:<\/strong> &lt;menu&gt;&lt;id&gt;file&lt;\/id&gt;&lt;value&gt;File&lt;\/value&gt;&lt;popup&gt;&lt;menuitem&gt;&lt;value&gt;New&lt;\/value&gt;&lt;onclick&gt;CreateNewDoc()&lt;\/onclick&gt;&lt;\/menuitem&gt;&lt;menuitem&gt;&lt;value&gt;Open&lt;\/value&gt;&lt;onclick&gt;OpenDoc()&lt;\/onclick&gt;&lt;\/menuitem&gt;&lt;menuitem&gt;&lt;value&gt;Close&lt;\/value&gt;&lt;onclick&gt;CloseDoc()&lt;\/onclick&gt;&lt;\/menuitem&gt;&lt;\/popup&gt;&lt;\/menu&gt;<\/pre>\n<\/blockquote>\n<p>Beautified form of the above result:<\/p>\n<blockquote>\n<pre>&lt;menu&gt;\r\n  &lt;id&gt;file&lt;\/id&gt;\r\n  &lt;value&gt;File&lt;\/value&gt;\r\n  &lt;popup&gt;\r\n    &lt;menuitem&gt;\r\n      &lt;value&gt;New&lt;\/value&gt;\r\n      &lt;onclick&gt;CreateNewDoc()&lt;\/onclick&gt;\r\n    &lt;\/menuitem&gt;\r\n    &lt;menuitem&gt;\r\n      &lt;value&gt;Open&lt;\/value&gt;\r\n      &lt;onclick&gt;OpenDoc()&lt;\/onclick&gt;\r\n    &lt;\/menuitem&gt;\r\n    &lt;menuitem&gt;\r\n      &lt;value&gt;Close&lt;\/value&gt;\r\n      &lt;onclick&gt;CloseDoc()&lt;\/onclick&gt;\r\n    &lt;\/menuitem&gt;\r\n  &lt;\/popup&gt;\r\n&lt;\/menu&gt;<\/pre>\n<\/blockquote>\n<p>Note that linked examples page uses sporadically invented attributes;\u00a0<em>common_schema<\/em> prefers using well-defined nodes.<\/p>\n<h4>extract_json_value()<\/h4>\n<p>Which means things you can do with XML can also be done with JSON. XPath is a popular extraction DSL, working not only for XML but also for Object Oriented structures (see Groovy&#8217;s nice integration of XPath into the language, or just commons-beans for conservative approach). JSON is a perfect data store for XPath expressions; by utilizing the translation between JSON and XML, one is now easily able to extract value from JSON (using same example as above):<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT <strong>extract_json_value<\/strong>(@json, <span style=\"color: #000080;\">'\/\/id'<\/span>) AS result;\r\n+--------+\r\n| result |\r\n+--------+\r\n| file   |\r\n+--------+\r\n\r\nmysql&gt; SELECT <strong>extract_json_value<\/strong>(@json, <span style=\"color: #000080;\">'count(\/menu\/popup\/menuitem)'<\/span>) AS count_items;\r\n+-------------+\r\n| count_items |\r\n+-------------+\r\n| 3           |\r\n+-------------+<\/pre>\n<\/blockquote>\n<p>Implementations of <strong>json_to_xml()<\/strong> and <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/extract_json_value.html\"><strong>extract_json_value()<\/strong><\/a> are CPU intensive. There is really just one justification for having these written in Stored Procedures: their lack in the standard MySQL function library. This is reason enough. Just be aware; test with <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/information-functions.html#function_benchmark\">BENCHMARK()<\/a>.<\/p>\n<h4>query_checksum()<\/h4>\n<p>It looks like this:<\/p>\n<blockquote>\n<pre>mysql&gt; call <strong>query_checksum<\/strong>(<span style=\"color: #000080;\">'select id from world.City where id in (select capital from world.Country) order by id'<\/span>);\r\n+----------------------------------+\r\n| checksum\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| 5f35070b90b0c079ba692048c51a89fe |\r\n+----------------------------------+\r\n\r\nmysql&gt; call <strong>query_checksum<\/strong>(<span style=\"color: #000080;\">'select capital from world.Country where capital is not null order by capital'<\/span>);\r\n+----------------------------------+\r\n| checksum\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| 5f35070b90b0c079ba692048c51a89fe |\r\n+----------------------------------+<\/pre>\n<\/blockquote>\n<p>The two queries above yield with the same result set. As consequence, <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_checksum.html\"><strong>query_checksum()<\/strong><\/a> produces the same checksum value for both. The next query produces a different result set, hence a different checksum:<\/p>\n<blockquote>\n<pre>mysql&gt; call <strong>query_checksum<\/strong>(<span style=\"color: #000080;\">'select id from world.City where id in (select capital from world.Country) order by id limit 10'<\/span>);\r\n+----------------------------------+\r\n| checksum\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| 997079c2dfca34ba87ae44ed8965276e |\r\n+----------------------------------+<\/pre>\n<\/blockquote>\n<p>The routine actually invokes the given queries (modifying them a bit along the way) and uses a deterministic incremental checksum to get the final result.<\/p>\n<p>Its use? As a handy built-in mechanism for comparing your table data. This is meant for relatively small result sets &#8211; not for your <strong>20GB<\/strong> table. Inspired by Baron&#8217;s <a href=\"http:\/\/www.xaprb.com\/blog\/2009\/03\/25\/mysql-command-line-tip-compare-result-sets\/\">old trick<\/a>, and works on server side (Windows\/GUI\/automated clients to benefit).<\/p>\n<h4>random_hash()<\/h4>\n<p>Random hashes come handy. The naive way to produce them is by executing something like <strong>SELECT SHA1(RAND())<\/strong>. However the <strong>RAND()<\/strong> function just doesn&#8217;t provide enough plaintext for the hash function. The <strong>SHA<\/strong>\/<strong>MD5<\/strong> functions expect a textual input, and produce a <strong>160<\/strong>\/<strong>128<\/strong> bit long hash. The maximum char length of a <strong>RAND()<\/strong> result is <strong>20<\/strong> characters or so, and these are limited to the <strong>0-9<\/strong> digits. So at about <strong>10^20<\/strong> options for input, which is about <strong>64<\/strong> bit. Hmmmm. a 64 bit input to generate a <strong>160<\/strong> bit output? I don&#8217;t think so! <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/random_hash.html\"><strong>random_hash()<\/strong><\/a> provides additional input in the form of your current status (at about 830 characters) as well as <strong>RAND()<\/strong>, <strong>SYSDATE()<\/strong> and server ID.<\/p>\n<h4>Bugfixes<\/h4>\n<p>Any bugfix adds at least one test; typically more. Currently with over <strong>470<\/strong> tests, <em>common_schema<\/em> is built to work.<\/p>\n<h4>Get common_schema<\/h4>\n<p><em>common_schema<\/em> <strong>1.3<\/strong> is available under the permissive New BSD License. <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">Find the latest download here<\/a>.<\/p>\n<p>If you like to support <em>common_schema<\/em>, I&#8217;m always open for ideas and contributions. Or you can just spread the word!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>common_schema 1.3 is released and is available for download. New and noteworthy in this version: Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed duplicate_grantee(): copy+paste existing accounts along with their full set of privileges similar_grants: find which accounts share the exact same set [&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,89,57,76,50,16],"class_list":["post-5941","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-development","tag-new-features","tag-open-source","tag-queryscript","tag-scripts","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1xP","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5941","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=5941"}],"version-history":[{"count":46,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5941\/revisions"}],"predecessor-version":[{"id":5999,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5941\/revisions\/5999"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}