{"id":388,"date":"2009-01-15T10:01:39","date_gmt":"2009-01-15T08:01:39","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=388"},"modified":"2009-01-15T14:30:42","modified_gmt":"2009-01-15T12:30:42","slug":"triggers-use-case-compilation-part-ii","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-ii","title":{"rendered":"Triggers Use Case Compilation, Part II"},"content":{"rendered":"<p>In <a title=\"Triggers Use Case Compilation, Part I\" href=\"http:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-i\">Triggers Use Case Compilation, Part I<\/a>, I&#8217;ve demonstrated some triggers use scenarios.<\/p>\n<p>We continue our examples of triggers usage.<\/p>\n<h4>Counters and aggregations bookkeeping<\/h4>\n<p>Consider the City table: each city belongs to a certain country. Some questions we may be interested in are:<\/p>\n<ul>\n<li>How many cities are there per country?<\/li>\n<li>What&#8217;s the sum of cities population per country?<\/li>\n<li>What&#8217;s the population of the largest city per country?<\/li>\n<\/ul>\n<p>Answering any of these questions is an easy SQL excercise. But aggregation is required, and full table scan (or full index scan, if we&#8217;re lucky) is essentially part of any execution plan. What if we can&#8217;t pay the price for these queries? What if we need immediate, or near immediate response?<\/p>\n<p><!--more-->One solution is to use counter tables, or summary tables. For example, to answer the first questions, we create the following table:<\/p>\n<blockquote>\n<pre>CREATE TABLE  CityCount (\r\n  `CountryCode` char(3) NOT NULL,\r\n  `NumCities` int(11) NOT NULL,\r\n  PRIMARY KEY  (`CountryCode`)\r\n);<\/pre>\n<\/blockquote>\n<p>By following all INSERTs and DELETEs on the <strong>City<\/strong> table, we can manage the <strong>CityCount<\/strong> table&#8217;s data.<\/p>\n<blockquote>\n<pre>DELIMITER $$\r\n\r\nDROP TRIGGER IF EXISTS City_ai $$\r\nCREATE TRIGGER City_ai AFTER INSERT ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  INSERT INTO CityCount (CountryCode, NumCities)\r\n    VALUES (NEW.CountryCode, 1)\r\n    ON DUPLICATE KEY\r\n    UPDATE NumCities = NumCities+1;\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS City_au $$\r\nCREATE TRIGGER City_au AFTER UPDATE ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  IF (OLD.CountryCode != NEW.CountryCode) THEN\r\n    UPDATE CityCount SET NumCities = NumCities-1\r\n      WHERE CountryCode = OLD.CountryCode;\r\n    INSERT INTO CityCount (CountryCode, NumCities)\r\n      VALUES (NEW.CountryCode, 1)\r\n      ON DUPLICATE KEY\r\n      UPDATE NumCities = NumCities+1;\r\n  END IF;\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS City_ad $$\r\nCREATE TRIGGER City_ad AFTER DELETE ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  UPDATE CityCount SET NumCities = NumCities-1\r\n    WHERE CountryCode = OLD.CountryCode;\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>To illustrate the impact of triggers, let&#8217;s do a &#8216;massive&#8217; data load here:<\/p>\n<blockquote>\n<pre>mysql&gt; CREATE TABLE City_2 LIKE City;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql&gt; INSERT INTO City_2 SELECT * FROM City;\r\n<span style=\"color: #008000;\">Query OK, 3998 rows affected (0.23 sec)<\/span>\r\nRecords: 3998  Duplicates: 0  Warnings: 0\r\n\r\nmysql&gt; TRUNCATE TABLE City;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql&gt; INSERT INTO City SELECT * FROM City_2;\r\n<span style=\"color: #993300;\">Query OK, 3998 rows affected (3.58 sec)<\/span>\r\nRecords: 3998  Duplicates: 0  Warnings: 0\r\n\r\nmysql&gt; SELECT * FROM CityCount LIMIT 10;\r\n+-------------+-----------+\r\n| CountryCode | NumCities |\r\n+-------------+-----------+\r\n| AFG         |         4 |\r\n| NLD         |        28 |\r\n| ANT         |         1 |\r\n| ALB         |         1 |\r\n| DZA         |        18 |\r\n| ASM         |         2 |\r\n| AND         |         1 |\r\n| AGO         |         5 |\r\n| AIA         |         2 |\r\n| ATG         |         1 |\r\n+-------------+-----------+\r\n10 rows in set (0.01 sec)<\/pre>\n<\/blockquote>\n<p>The results seem satisfactory. We can now query <strong>CityCount<\/strong> directly, no need for complex queries on <strong>City<\/strong>. But look at the times: INSERTing data into <strong>City_2<\/strong> took 0.23 seconds. INSERTing the same data into <strong>City<\/strong> took 3.58 seconds. That&#8217;s the triggers overhead. There is an advantage to using triggers here (and in general) if you&#8217;re doing many SELECTs, but few INSERT\/UPDATE\/DELETE.<\/p>\n<h4>Enhance security<\/h4>\n<p>In <a title=\"Using triggers to block malicious code: an example\" href=\"http:\/\/code.openark.org\/blog\/mysql\/using-triggers-to-block-malicious-code-an-example\">Using triggers to block malicious code: an example<\/a>, I have shown how a trigger may block changes to sensitive data. A trigger is aware of the invoker, and can implement a row-based privileges system.<\/p>\n<p>As another example, let&#8217;s see how we can do a &#8220;privileges table partitioning&#8221;. We look at the <a title=\"MySQL's world database setup\" href=\"http:\/\/dev.mysql.com\/doc\/world-setup\/en\/world-setup.html\">world<\/a>&#8216;s City table. What if we&#8217;re working on some world-nations-wiki, and we want to assign users to countries, in such way that a user can only modify data for a country she is assigned to?<\/p>\n<p>We create a privileges table which maps users to countries:<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `CountryUser`;\r\nCREATE TABLE `CountryUser` (\r\n  `CountryCode` char(3) NOT NULL,\r\n  `mysql_User` char(16) collate utf8_bin NOT NULL default '',\r\n  PRIMARY KEY  (`CountryCode`, `mysql_User`)\r\n);<\/pre>\n<\/blockquote>\n<p>And then add the security triggers on <strong>City<\/strong>:<\/p>\n<blockquote>\n<pre>DELIMITER $$\r\n\r\nDROP TRIGGER IF EXISTS City_bi $$\r\nCREATE TRIGGER City_bi BEFORE INSERT ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;\r\n  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = NEW.CountryCode)) THEN\r\n    SELECT 0 FROM `Unauthorized access` INTO @error;\r\n  END IF;\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS City_bu $$\r\nCREATE TRIGGER City_bu BEFORE UPDATE ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;\r\n  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = OLD.CountryCode)) THEN\r\n    SELECT 0 FROM `Unauthorized access` INTO @error;\r\n  END IF;\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS City_bd $$\r\nCREATE TRIGGER City_bd BEFORE DELETE ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;\r\n  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = OLD.CountryCode)) THEN\r\n    SELECT 0 FROM `Unauthorized access` INTO @error;\r\n  END IF;\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>Testing (as user root):<\/p>\n<blockquote>\n<pre>mysql&gt; INSERT INTO CountryUser (CountryCode, mysql_User) VALUES ('NLD', 'root');\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql&gt; DELETE FROM City WHERE Name = 'Milano';\r\n<span style=\"color: #993300;\">ERROR 1146 (42S02): Table 'world.Unauthorized access' doesn't exist<\/span>\r\n\r\nmysql&gt; DELETE FROM City WHERE Name = 'Amsterdam';\r\n<span style=\"color: #008000;\">Query OK, 1 row affected (0.05 sec)<\/span><\/pre>\n<\/blockquote>\n<p><strong>Managing cache (e.g. invalidating memcached)<\/strong><\/p>\n<p>In <a title=\"Using memcached functions for MySQL; an automated alternative to Query Cache\" href=\"http:\/\/code.openark.org\/blog\/mysql\/using-memcached-functions-for-mysql-an-automated-alternative-to-query-cache\">Using memcached functions for MySQL; an automated alternative to Query Cache<\/a>, I&#8217;ve shown how triggers can be used to invalidate memcached values. But cache management can apply to local tables as well.<\/p>\n<p>It is common practice to have summary tables (we used such one in our <em>counters<\/em> example). Summary tables are just normal tables which are filled with aggregated data, and save the need to re-aggregate that data. Much like the memcached example, triggers can be used to invalidate or reload the summary table data when relevant changes occur in underlying tables.<\/p>\n<p><strong>Limiting table size<\/strong><\/p>\n<p>Out last use case shows how it is possible to limit table size using triggers.<\/p>\n<p>By &#8220;limiting table size&#8221; we can think of row-count limitation, or storage limitation. In the following example, the <strong>logs<\/strong> table is limited by a certain byte size.<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `world`.`logs`;\r\nCREATE TABLE  `world`.`logs` (\r\n  `logs_id` int(11) NOT NULL auto_increment,\r\n  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\r\n  `message` varchar(255) character set utf8 NOT NULL,\r\n  PRIMARY KEY  (`logs_id`)\r\n) ENGINE=MyISAM;\r\n\r\nDELIMITER $$\r\n\r\nDROP TRIGGER IF EXISTS logs_bi $$\r\nCREATE TRIGGER logs_bi BEFORE INSERT ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  SELECT DATA_LENGTH+INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='world' AND TABLE_NAME='LOGS' INTO @estimated_table_size;\r\n  IF (@estimated_table_size &gt; 25*1024) THEN\r\n    SELECT 0 FROM `logs table is full` INTO @error;\r\n  END IF;\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>No more than 25KB of storage is allowed for this table. Let&#8217;s put it to the test:<\/p>\n<blockquote>\n<pre>mysql&gt; INSERT INTO logs (message) VALUES ('this line is 31 characters long');\r\n<span style=\"color: #339966;\">Query OK, 1 row affected (0.00 sec)<\/span>\r\n\r\nmysql&gt; INSERT INTO logs SELECT * FROM logs;\r\n<span style=\"color: #339966;\">Query OK, 1 row affected (0.00 sec)<\/span>\r\nRecords: 1  Duplicates: 0  Warnings: 0\r\n\r\nmysql&gt; INSERT INTO logs SELECT * FROM logs;\r\n<span style=\"color: #339966;\">Query OK, 2 rows affected (0.01 sec)<\/span>\r\nRecords: 2  Duplicates: 0  Warnings: 0\r\n\r\n...\r\n\r\nmysql&gt; INSERT INTO logs SELECT * FROM logs;\r\n<span style=\"color: #008000;\">Query OK, 256 rows affected (1.84 sec)<\/span>\r\nRecords: 256  Duplicates: 0  Warnings: 0\r\n\r\nmysql&gt; INSERT INTO logs SELECT * FROM logs;\r\n<span style=\"color: #993300;\">ERROR 1146 (42S02): Table 'world.logs table is full' doesn't exist\r\n<\/span>\r\nmysql&gt; INSERT INTO logs (message) VALUES ('this line is 31 characters long');\r\n<span style=\"color: #993300;\">ERROR 1146 (42S02): Table 'world.logs table is full' doesn't exist<\/span><\/pre>\n<\/blockquote>\n<p>A few important notes:<\/p>\n<ul>\n<li>INFORMATION_SCHEMA only presents estimated size.<\/li>\n<li>For InnoDB, small tables may claim to possess much more storage than they really do (see this <a title=\"How much space does empty Innodb table take ?\" href=\"http:\/\/www.mysqlperformanceblog.com\/2008\/12\/16\/how-much-space-does-empty-innodb-table-take\/\">post<\/a> by <a title=\"MySQL Performance Blog\" href=\"http:\/\/www.mysqlperformanceblog.com\/\">MySQL Performance Blog<\/a>).<\/li>\n<li>InnoDB does not release storage (unless you use TRUNCATE or ALTER TABLE), which means even if you delete rows from the table, it still occupies the same storage.<\/li>\n<li>Memory tables do not release memory unless you use TRUNCATE or ALTER TABLE.<\/li>\n<li>You may wish to limit table size by row count. There, again, INFORMATION_SCHEMA only provides an estimated value, and querying InnoDB for count(*) is a lengthy operation.<\/li>\n<\/ul>\n<p>A more interesting implementation of table size limitation is the notion of <em>rotating tables<\/em>. More on that in the next post.<\/p>\n<h4>More to come<\/h4>\n<p>In the next and final part we will look at some problems which cannot be solved with triggers due to current trigger limitations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Triggers Use Case Compilation, Part I, I&#8217;ve demonstrated some triggers use scenarios. We continue our examples of triggers usage. Counters and aggregations bookkeeping Consider the City table: each city belongs to a certain country. Some questions we may be interested in are: How many cities are there per country? What&#8217;s the sum of cities [&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":[30],"class_list":["post-388","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-triggers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-6g","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/388","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=388"}],"version-history":[{"count":20,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/388\/revisions"}],"predecessor-version":[{"id":519,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/388\/revisions\/519"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}