MySQL

Emulating Cascading Operations From InnoDB to MyISAM Tables

Mike Hillyer has released the newest version of the Sakila sample database. One of the things that I wanted to highlight is a technique that Mike demonstrates through his use of triggers to cascade update and delete operations from an InnoDB table to a MyISAM table.

As many of you are aware, the InnoDB storage engine does not support the very useful FULLTEXT indexes which are unique to the MyISAM storage engine. Unfortunately, the MyISAM storage engine does not currently support foreign key contraints or transactional safety. So, what happens when you want to enforce data integrity through the InnoDB storage engine, but still want the capacity to use MyISAM’s FULLTEXT ability? Until MySQL 5.0, you were reduced to writing application-level code to handle the writing of textual fields to a duplicated MyISAM table whenever the primary entity in an InnoDB table was inserted or updated. Likewise, when the primary records was removed, similar application code was required to ensure the related records in the MyISAM table were removed.

Demonstration of the Problem

In the Sakila database, there is a table, film, containing records for the primary “film” entity (the database is designed around a fictional film store chain…). Here’s the CREATE TABLE statement for the table:

CREATE TABLE film (
  film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  description TEXT DEFAULT NULL,
  release_year YEAR DEFAULT NULL,
  language_id TINYINT UNSIGNED NOT NULL,
  original_language_id TINYINT UNSIGNED DEFAULT NULL,
  rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
  rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
  length SMALLINT UNSIGNED DEFAULT NULL,
  replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
  rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (film_id),
  KEY idx_title (title),
  KEY idx_fk_language_id (language_id),
  KEY idx_fk_original_language_id (original_language_id),
  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Notice that the table is using the InnoDB storage engine, and is referencing another table (language) on two of its columns. Also note that there are two text-based fields in the table: “title” and “description”.

OK, so what if we want to provide the ability for our customers to search through film titles and descriptions for certain keywords, and want to make use of the FULLTEXT indexing of the MyISAM engine, but still maintain the data integrity provided by the InnoDB storage engine? What we want is just the couple textual fields in a MyISAM table so that we can take advantage of FULLTEXT indexing:

CREATE TABLE film_text (
  film_id SMALLINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY  (film_id),
  FULLTEXT KEY idx_title_description (title,description)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The solution involves some duplication of data, however if you can deal with a little extra storage requirement on the server, you can essentially get the best of both worlds, with NO extra application-level code. In fact, as you’ll see, using triggers, all the logic of maintaining this duplicate store of data is handled at the database level, using MySQL 5 Triggers.

Triggers Are the Key to the Solution

MySQL 5 Triggers allow SQL code to be executed during an event happening on a particular table in the database. Currently, the supported events for MySQL triggers are:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

A trigger occurs, quite appropriately, when a trigger has been placed on the table correpsonding to one of the above events. Triggers can contain logic, similar to a MySQL 5 stored procedure (with a few exceptions) and can reference other tables in the current or other databases. They take the general form of:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

In the case of the Sakila database, Mike wanted to use triggers to enforce the cascading relationship of the primary InnoDB film table and the FULLTEXT film_text MyISAM table. The following code shows how triggers are used to accomplish the cascades:

DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END;;


CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
    IF (old.title != new.title) or (old.description != new.description)
    THEN
        UPDATE film_text
            SET title=new.title,
                description=new.description,
                film_id=new.film_id
        WHERE film_id=old.film_id;
    END IF;
  END;;


CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
    DELETE FROM film_text WHERE film_id = old.film_id;
  END;;

DELIMITER ;

You can follow through the code on your own. it’s not particularly complicated, as the syntax for the trigger logic is quite human-readable and straight-forward. the OLD and NEW keywords are used to refer to the field values before and after an update. Obviously, there can be no references to the OLD keyword during an INSERT trigger (either BEFORE or AFTER) and there can be no references to the NEW keyword during a DELETE trigger…

You can see how Mike checks the OLD.title and NEW.title values during the INSERT and UPDATE triggers, and correspondingly INSERTs or UPDATEs the appropriate records in the MyISAM film_text table when needed. Smiilarly, records are removed fromthe film_text table when no longer found in the primary film table.

All in all, an excellent example of how to effectively use triggers in your database to enforce cascading relationships and provide your application with the best of both InnoDB and MyISAM. Great job, Mike!