{"id":5033,"date":"2012-09-11T08:48:13","date_gmt":"2012-09-11T06:48:13","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5033"},"modified":"2012-09-11T08:49:01","modified_gmt":"2012-09-11T06:49:01","slug":"how-common_schema-installs-itself","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/how-common_schema-installs-itself","title":{"rendered":"How common_schema installs itself"},"content":{"rendered":"

Up till common_schema<\/a> version 1.1<\/strong>, the user would need to choose from distinct distribution files: an install compatible with MySQL 5.1<\/strong>, one compatible with InnoDB Plugin enabled servers, and one compatible with Percona Server. The difference between the three is the availability of certain INFORMATION_SCHEMA<\/strong> tables.<\/p>\n

With 1.1<\/strong>, this is no longer the case: common_schema<\/em> auto-detects the server and available feature set, and installs accordingly.<\/p>\n

Wait, isn’t common_schema just a SQL file?<\/h4>\n

Yes. It’s not like there’s an installer like InstallShield<\/em> or anything. Nevertheless, common<\/em>_schema<\/em> offers a smart way of conditional handling, which is uses in itself. It’s called QueryScript<\/a>.<\/p>\n

common_schema<\/em> is installed by importing the SQL file (via SOURCE<\/strong> command; the mysql<\/em> client; your favorite GUI). This creates your usual tables, views and routines. But some of these routines make for an interpreter for QueryScript<\/em>. Somewhere along the installation process (remember – it’s just a SQL import), common_schema<\/em> switches over to executing scripts to manage the installation. In particular, there are a few views which depend on optional tables, such as InnoDB Plugin’s tables for INFORMATION_SCHEMA<\/strong>.<\/p>\n

Consider the following CREATE VIEW<\/strong> statement:<\/p>\n

\n
CREATE OR REPLACE\r\nALGORITHM = UNDEFINED\r\nSQL SECURITY INVOKER\r\nVIEW innodb_transactions_summary AS\r\n\u00a0 SELECT \r\n\u00a0\u00a0\u00a0 COUNT(*) AS count_transactions,\r\n\u00a0\u00a0\u00a0 IFNULL(SUM(trx_state = 'RUNNING'), 0) AS running_transactions,\r\n\u00a0\u00a0\u00a0 IFNULL(SUM(trx_requested_lock_id IS NOT NULL), 0) AS locked_transactions,\r\n\u00a0\u00a0\u00a0 COUNT(DISTINCT trx_requested_lock_id) AS distinct_locks\r\n\u00a0 FROM \r\n\u00a0\u00a0\u00a0 INFORMATION_SCHEMA.INNODB_TRX<\/strong>\r\n\u00a0 WHERE \r\n\u00a0\u00a0\u00a0 trx_mysql_thread_id != CONNECTION_ID()\r\n;<\/pre>\n<\/blockquote>\n
Such a statement would fail the import if the underlying tables (INFORMATION_SCHEMA.INNODB_TRX<\/strong> in our example) do not actually exist. Which is why this CREATE<\/strong> statement is not invoked just like that. It is wrapped within a script:<\/p>\n
\n
set @script := \"\r\n  set @common_schema_innodb_plugin_expected := 0<\/strong>;\r\n  try<\/strong> {\r\n  \u00a0 set @common_schema_innodb_plugin_expected<\/strong> := @common_schema_innodb_plugin_expected + 1; \r\n\r\n    CREATE VIEW<\/strong> ... (as above)\r\n\r\n \u00a0\u00a0 set @common_schema_innodb_plugin_installed<\/strong> := @common_schema_innodb_plugin_installed + 1;\r\n  }\r\n  catch<\/strong> {\r\n  }\r\n\";\r\n\r\ncall run(@script);<\/pre>\n<\/blockquote>\n
I really want to stress this: this is a try-catch<\/strong><\/a> statement running from within your MySQL server. QueryScript<\/em> accepts this statement and behaves very similarly to the way you are used to with your favorite programming language.<\/p>\n
If the CREATE VIEW<\/strong> cannot be fulfilled, an error is generated. But instead of failing the entire script, it is caught by the catch<\/strong>clause; the “set @common_schema_innodb_plugin_installed :=<\/strong> …” line is never executed upon such error.<\/p>\n
Upon CREATE VIEW<\/strong> failure, @common_schema_innodb_plugin_installed<\/strong> falls short of @common_schema_innodb_plugin_expected<\/strong> and <\/strong>. By the end of installation process, the two are compared, so as to determine the install success:<\/p>\n
\n
+-----------------------------------------------------------+\r\n| complete\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| - Base components: installed<\/strong>\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| - InnoDB Plugin components: installed<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| - Percona Server components: not installed<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\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| Installation complete. Thank you for using common_schema! |\r\n+-----------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Can’t you just use “mysql –force”?<\/h4>\n
So, one can import a SQL file using mysql –force<\/strong>, which does nto break down on first sight of error, but continues to next statements.<\/p>\n
True, but:<\/p>\n
    \n
  • I don’t want to ask that of the user.<\/li>\n
  • It won’t give me a status on what went wrong<\/em>.<\/li>\n<\/ul>\n
    By using QueryScript<\/em>‘s try-catch<\/strong>:<\/p>\n
      \n
    • I can take alternate action on failure.<\/li>\n
    • common_schema<\/em> can (and does) inform the user at the end of installation process which components are installed and which are not.<\/li>\n
    • It can (and does) write this info down to a metadata<\/em> table such that I can ask the user for that information when submitting a bug report.<\/li>\n<\/ul>\n
      Eat your own dog food!<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
      Up till common_schema version 1.1, the user would need to choose from distinct distribution files: an install compatible with MySQL 5.1, one compatible with InnoDB Plugin enabled servers, and one compatible with Percona Server. The difference between the three is the availability of certain INFORMATION_SCHEMA tables. With 1.1, this is no longer the case: common_schema […]<\/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":[67,10,76],"class_list":["post-5033","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-installation","tag-queryscript"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1jb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5033","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=5033"}],"version-history":[{"count":33,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5033\/revisions"}],"predecessor-version":[{"id":5394,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5033\/revisions\/5394"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}