Triggers Use Case Compilation, Part II

In Triggers Use Case Compilation, Part I, I’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’s the sum of cities population per country?
• What’s the population of the largest city per country?

Answering any of these questions is an easy SQL excercise. But aggregation is required, and full table scan (or full index scan, if we’re lucky) is essentially part of any execution plan. What if we can’t pay the price for these queries? What if we need immediate, or near immediate response?

One solution is to use counter tables, or summary tables. For example, to answer the first questions, we create the following table:

CREATE TABLE  CityCount (
  `CountryCode` char(3) NOT NULL,
  `NumCities` int(11) NOT NULL,
  PRIMARY KEY  (`CountryCode`)
);

By following all INSERTs and DELETEs on the City table, we can manage the CityCount table’s data.

DELIMITER $$

DROP TRIGGER IF EXISTS City_ai $$
CREATE TRIGGER City_ai AFTER INSERT ON City
FOR EACH ROW
BEGIN
  INSERT INTO CityCount (CountryCode, NumCities)
    VALUES (NEW.CountryCode, 1)
    ON DUPLICATE KEY
    UPDATE NumCities = NumCities+1;
END $$

DROP TRIGGER IF EXISTS City_au $$
CREATE TRIGGER City_au AFTER UPDATE ON City
FOR EACH ROW
BEGIN
  IF (OLD.CountryCode != NEW.CountryCode) THEN
    UPDATE CityCount SET NumCities = NumCities-1
      WHERE CountryCode = OLD.CountryCode;
    INSERT INTO CityCount (CountryCode, NumCities)
      VALUES (NEW.CountryCode, 1)
      ON DUPLICATE KEY
      UPDATE NumCities = NumCities+1;
  END IF;
END $$

DROP TRIGGER IF EXISTS City_ad $$
CREATE TRIGGER City_ad AFTER DELETE ON City
FOR EACH ROW
BEGIN
  UPDATE CityCount SET NumCities = NumCities-1
    WHERE CountryCode = OLD.CountryCode;
END $$

DELIMITER ;

To illustrate the impact of triggers, let’s do a ‘massive’ data load here:

mysql> CREATE TABLE City_2 LIKE City;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO City_2 SELECT * FROM City;
Query OK, 3998 rows affected (0.23 sec)
Records: 3998  Duplicates: 0  Warnings: 0

mysql> TRUNCATE TABLE City;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO City SELECT * FROM City_2;
Query OK, 3998 rows affected (3.58 sec)
Records: 3998  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM CityCount LIMIT 10;
+-------------+-----------+
| CountryCode | NumCities |
+-------------+-----------+
| AFG         |         4 |
| NLD         |        28 |
| ANT         |         1 |
| ALB         |         1 |
| DZA         |        18 |
| ASM         |         2 |
| AND         |         1 |
| AGO         |         5 |
| AIA         |         2 |
| ATG         |         1 |
+-------------+-----------+
10 rows in set (0.01 sec)

The results seem satisfactory. We can now query CityCount directly, no need for complex queries on City. But look at the times: INSERTing data into City_2 took 0.23 seconds. INSERTing the same data into City took 3.58 seconds. That’s the triggers overhead. There is an advantage to using triggers here (and in general) if you’re doing many SELECTs, but few INSERT/UPDATE/DELETE.

Enhance security

In Using triggers to block malicious code: an example, 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.

As another example, let’s see how we can do a “privileges table partitioning”. We look at the world‘s City table. What if we’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?

We create a privileges table which maps users to countries:

DROP TABLE IF EXISTS `CountryUser`;
CREATE TABLE `CountryUser` (
  `CountryCode` char(3) NOT NULL,
  `mysql_User` char(16) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`CountryCode`, `mysql_User`)
);

And then add the security triggers on City:

DELIMITER $$

DROP TRIGGER IF EXISTS City_bi $$
CREATE TRIGGER City_bi BEFORE INSERT ON City
FOR EACH ROW
BEGIN
  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;
  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = NEW.CountryCode)) THEN
    SELECT 0 FROM `Unauthorized access` INTO @error;
  END IF;
END $$

DROP TRIGGER IF EXISTS City_bu $$
CREATE TRIGGER City_bu BEFORE UPDATE ON City
FOR EACH ROW
BEGIN
  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;
  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = OLD.CountryCode)) THEN
    SELECT 0 FROM `Unauthorized access` INTO @error;
  END IF;
END $$

DROP TRIGGER IF EXISTS City_bd $$
CREATE TRIGGER City_bd BEFORE DELETE ON City
FOR EACH ROW
BEGIN
  SELECT SUBSTRING_INDEX(USER(),'@',1) INTO @current_mysql_user;
  IF (@current_mysql_user NOT IN (SELECT mysql_User FROM CountryUser WHERE CountryCode = OLD.CountryCode)) THEN
    SELECT 0 FROM `Unauthorized access` INTO @error;
  END IF;
END $$

DELIMITER ;

Testing (as user root):

mysql> INSERT INTO CountryUser (CountryCode, mysql_User) VALUES ('NLD', 'root');
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM City WHERE Name = 'Milano';
ERROR 1146 (42S02): Table 'world.Unauthorized access' doesn't exist

mysql> DELETE FROM City WHERE Name = 'Amsterdam';
Query OK, 1 row affected (0.05 sec)

Managing cache (e.g. invalidating memcached)

In Using memcached functions for MySQL; an automated alternative to Query Cache, I’ve shown how triggers can be used to invalidate memcached values. But cache management can apply to local tables as well.

It is common practice to have summary tables (we used such one in our counters 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.

Limiting table size

Out last use case shows how it is possible to limit table size using triggers.

By “limiting table size” we can think of row-count limitation, or storage limitation. In the following example, the logs table is limited by a certain byte size.

DROP TABLE IF EXISTS `world`.`logs`;
CREATE TABLE  `world`.`logs` (
  `logs_id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `message` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`logs_id`)
) ENGINE=MyISAM;

DELIMITER $$

DROP TRIGGER IF EXISTS logs_bi $$
CREATE TRIGGER logs_bi BEFORE INSERT ON City
FOR EACH ROW
BEGIN
  SELECT DATA_LENGTH+INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='world' AND TABLE_NAME='LOGS' INTO @estimated_table_size;
  IF (@estimated_table_size > 25*1024) THEN
    SELECT 0 FROM `logs table is full` INTO @error;
  END IF;
END $$

DELIMITER ;

No more than 25KB of storage is allowed for this table. Let’s put it to the test:

mysql> INSERT INTO logs (message) VALUES ('this line is 31 characters long');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO logs SELECT * FROM logs;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO logs SELECT * FROM logs;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> INSERT INTO logs SELECT * FROM logs;
Query OK, 256 rows affected (1.84 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> INSERT INTO logs SELECT * FROM logs;
ERROR 1146 (42S02): Table 'world.logs table is full' doesn't exist

mysql> INSERT INTO logs (message) VALUES ('this line is 31 characters long');
ERROR 1146 (42S02): Table 'world.logs table is full' doesn't exist

A few important notes:

• INFORMATION_SCHEMA only presents estimated size.
• For InnoDB, small tables may claim to possess much more storage than they really do (see this post by MySQL Performance Blog).
• 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.
• Memory tables do not release memory unless you use TRUNCATE or ALTER TABLE.
• 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.

A more interesting implementation of table size limitation is the notion of rotating tables. More on that in the next post.

More to come

In the next and final part we will look at some problems which cannot be solved with triggers due to current trigger limitations.