Skip to content

# Triggers Use Case Compilation, Part II

MySQL Triggers January 15, 2009January 15, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
0 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
code.openark.org » Blog Archive » Triggers Use Case Compilation, Part I

[…] Triggers Use Case Compilation, Part II […]

Triggers Use Case Compilation, Part III | code.openark.org

[…] Triggers Use Case Compilation, Part III […]

Thoughts and ideas for Online Schema Change | code.openark.org

[…] Triggers Use Case Compilation, Part II […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.