Database migrations using triggers in MySQL with MyISAM and replication

by Taavi on October 3, 2011

Last year we purchased the book Refactoring Databases. While the first chapter was preaching to the choir (database migrations are normal around here), there was one extremely valuable gem: the use of database triggers to effect seamless data migrations.

The book targets enterprise environments where there are multiple applications accessing the database concurrently, each with release cycles measured in months and years, and regularly scheduled downtime windows. In contrast, FreshBooks has fewer moving parts (and each part is much smaller), release cycles on the order of weeks (sometimes hours!), and no time is good for downtime. We’re used around the world, and it shows in the server activity logs 24/7.

However, we can still learn a lot from Refactoring Databases. Methods of performing migrations with staggered application releases over the course of months are equally applicable to a normal web app undergoing a 0-downtime deployment with rolling backend restarts to a new version of the code.

Database triggers are a way of telling the database server to react automatically to some other action, like If This Then That. The Big Three operations that change data are INSERT, UPDATE, and DELETE. One example use of triggers is to create an audit log by instructing the database server to record the effect of all UPDATE statements against a table. From this description it’s tempting to think that a lot of things should be done using triggers! Unfortunately they run on the database server itself (so can easily cause a performance bottleneck), are written in SQL (which can make it hard to express yourself if you need to do any kind of data transformation), are impossible to unit test (any test you can write for them is essentially an integration test), and don’t allow chaining (in MySQL at least; if you need two logical things done you have to write them as a single trigger; Observer and Visitor patterns are not an option here).

Believe it or not, MySQL (even using the MyISAM table engine) has full support for triggers! In the context of supporting data migrations, there are some more specific uses whose benefits outweigh the issues above. Data migration triggers don’t have to exist for very long, and it’s a strong hint to reorganize your release schedule if you find yourself wanting to do more than one thing in a single trigger. Useful things a trigger can do to support a data migration include:

• keeping two columns in a table in sync
• keeping two columns in two different tables in sync
• creating new column(s) with data synthesized from old one(s) and vice versa
• anything else you can do with a trigger (no dynamic SQL!)

However, there are some gotchas to keep in mind when you’re replicating with triggers. With MySQL replication the master records all the data-changing SQL statements executed, and the slave(s) execute those recorded SQL statements¹. The slave machine doesn’t check any privileges; it assumes that if the master performed the operation that it was okay. Normally this is perfectly fine, but triggers add an extra layer of indirection.

Let’s try an example. Say that we want to replicate data from one table (FOO) into another (BAR) using an AFTER INSERT trigger. The CREATE TRIGGER statement itself will be written to the replication logs to be executed blindly by the slave(s); each MySQL instance will then have a copy of the trigger. Trigger creation on the master requires the SUPER privilege. Note that ALL PRIVILEGES does not include SUPER!

CREATE TRIGGER `foo_to_bar`
AFTER INSERT ON foo FOR EACH ROW
INSERT INTO bar (some_column)
VALUES (NEW.some_old_column);

NEW is an alias for use in triggers. When a user issues an INSERT INTO FOO statement, the master checks that this is okay, inserts the row in FOO, runs the trigger (with the privileges of the trigger creator), and records only the original INSERT statement for replication.

A slave server sees the INSERT INTO FOO statement, does the insert blindly, and runs the trigger. And that works IFF the user who created the trigger on the master also exists on the slave! If the trigger-creator user doesn’t exist on the slave, replication will fail on the INSERT statement, because the slave is no longer in “blind-execution” replication, but acting on a trigger just as if it had been caused only on the local instance.

“So,” you think to yourself, “I’ll just add the trigger-creator user and re-start replication.” Except that this is MyISAM, and there are no transactions…and that original INSERT statement already completed! So restarting replication will cause a duplicate PK error (if you’re lucky). Being lucky, removing the new row in FOO on the slave will let you restart replication and everything goes on its merry way. If you weren’t lucky, your slave is now strangely out of sync with the master. Have fun cleaning up!

But assuming the trigger-creator user is in place, things will work quite happily. At one point, we had over a million triggers running simultaneously for a few weeks. Of course, before trusting the results we ran some data verifications to ensure we had exactly the right data.

So be careful the first time you deploy triggers, and enjoy the power they give to change your schema without inconveniencing your users.

¹ This is equally a problem for saving the results of local-state-specific functions. For example, it’s a bad idea to do something like INSERT INTO foo VALUES (GET_LOCK('a lock')) because the value returned by GET_LOCK() could be different on the slave versus on the master. Timestamps manage to propagate properly because of extra annotations (read: computer-readable SQL comments) included in the replication logs.

Twig in Production

by Mark on September 28, 2011

For the past year we’ve been busy behind the scenes updating much of the existing FreshBooks application to use Twig. If you haven’t heard of Twig before, it’s a great templating library for PHP. Its syntax and features are mostly inspired by Jinja2. Twig provides a number of features that are useful in larger applications:

• Template inheritance – Useful for creating application layouts and re-using common page layouts.
• Horizontal reuse of blocks – You can reuse blocks from one template in another giving you an alternative way to re-use template code beyond extension.
• Macros – Create re-usable ‘functions’ entirely of template code.
• Automatic HTML escaping – The ‘killer feature’ you should look for in any template language.
• Sandboxing – Great for limiting what can be accessed in a template. This is really useful when exposing templates to end users.

Automatic escaping is something I think every PHP template language should have. While PHP is ok at being a template language, it is not automatically safe against cross-site scripting attacks. Having a template language offer both syntactic sugar and invisibily securing template code with no additional work is fantastic feature. If you’re using a template language without automatic escaping you should really switch to one that does. Having to explicitly turn off HTML escaping forces you to think about whether or not content is actually safe to output. In practice, we’ve found autoescaping makes developers more confident about not having accidentally created a new XSS problems. Unlike an omitted |escape, which could easily get lost in the crowd, every |raw sticks out like a sore thumb in code review.

We first started using Twig around the 0.9.6 release. It was chosen over the existing solution (Smarty) primarily because Twig offers a better feature set. Inheritance and automatic escaping were two concrete features that would improve our workflow. At the time Smarty 3 was still in beta, and the team wasn’t really happy with how Smarty had treated them in the past. We also considered other options:

• Dwoo – This was not chosen because the project activity was not overly high, and it seemed like development had stalled.
• PHPTal – XML only meant there were going to be significant integration hurdles with our non-compliant legacy code. It
  also meant it would be impossible to use with plain text email templates.

Getting twig into production

While updating all of our templates to use Twig we learned a few things along the way that are worth sharing.

Flushing the compiled templates is important. Forgetting to do it will cause you a great deal of pain. Thankfully, Twig has this functionality built in which makes it super easy to integrate as part of your deployment:

<?php
/**
 * Clean out compiled templates.
 *
 */

require_once './inc/bootstrap.php';

$environments = array();
$return = 0;

echo "Clearing template cache files...\n";

// Cache files used for the actual application
$environments['app'] = Fresh_Twig::getEnvironment();

// Other configurations like tests are loaded as well,
// but have been omitted.

foreach ($environments as $name => $environment) {
    $cache = $environment->getCache();

    if (!$cache) {
        echo "[Info '$name']: skipping because caching is disabled.\n";
        continue;
    }

    if (!is_dir($cache)) {
        echo "[Info '$name']: skipping because '$cache' does not exist.\n";
        continue;
    }

    if (!is_writable($cache)) {
        echo "[Error '$name']: '$cache' is not writable. Try levelling up.\n";
        $return++;
        continue;
    }

    $environment->clearCacheFiles();

    echo "[Success '$name']: cleared directory '$cache'.\n";
}
exit($return);

Automate building the cache files. Deploying on a cold cache will work for a while. If you have multiple webservers and many concurrent users you should think about pre-compiling your template cache. Deploying with a hot cache will remove the possiblity of two users generating the same template cache file. It also removes the possibility for contention to occur around the creation of cache files, and finally it just reduces the amount of work done overall. Since each webserver isn’t spending time generating cache files individually they can get back to serving users faster. We use make and a simple php script to do this:

<?php
/**
 * Simple script that gets a Twig_Enviornment and compiles all the
 * templates that match the glob pattern.
 *
 */
if (empty($argv[1])) {
    echo "No file path provided, please give a directory to compile templates in.\n";
    exit(1);
}
if (empty($argv[2])) {
    $argv[2] = 'twig';
}
$verbose = false;
if (in_array('--verbose', $argv)) {
    $verbose = true;
}

require_once './inc/bootstrap.php';

// A wrapper that provides a Twig_Environment with
// all the extensions we use attached.
$environment = Fresh_Twig::getEnvironment();
$directory = new RecursiveDirectoryIterator($argv[1]);
$recurser = new RecursiveIteratorIterator($directory);
$matcher = new RegexIterator($recurser, '/.*?\.' . $argv[2] . '$/', RegexIterator::GET_MATCH);

echo "Compiling Templates\n";

$i = 0;
foreach ($matcher as $file) {
    try {
        $filename = str_replace($argv[1], '', $file[0]);
        $environment->loadTemplate($filename);
        $environment->loadTemplate('/' . $filename);
        if ($verbose) {
            echo " - " . $filename . "\n";
        } else {
            if ($i % 72 == 0) {
                echo "\n";
            }
            echo '.';
        }
    } catch (Exception $e) {
        echo $e->getMessage() . "\n";
    }
    $i++;
}

echo "\nTemplates compiled\n";
exit(0);

We compile both the slash-less and leading slash versions of the template. This produces more template files, but saves us from having to be super picky about how we use twig files in our application. Before each deploy we run the make script to generate all the template cache files. The compiled templates are shipped with the code changes.

In closing

Since the switch to Twig last year we haven’t really hit any real problems with it. Its been a fantastic and easily extensible library. Thanks to Fabien and all the other Twig contributors for making such a solid product.

UTF-8 is here!

by Taavi on September 21, 2011

Today I’m happy to announce that FreshBooks is fully UTF-8!

What this means for you: You can use unicode snowman and all the other characters in the Unicode Basic Multilingual Plane to your heart’s content, knowing that each symbol takes the space of only one code point (essentially a “letter”), for example being able to fit a full 50 non-Latin characters into an invoice item name. It also means that you’ll be able to use snowmen and other extended characters in the time tracking section of FreshBooks!

What this means for you as an API consumer: You can now send FreshBooks the full gamut of BMP characters directly as UTF-8 with no encoding shenanigans! Previously anything not in the ISO-8859-1 character set would be smashed into a question mark, including XML entities representing those extended charaters. As a workaround, one could “double-encode” entities, e.g. your XML would contain the ASCII string ☃ to get a snowman. This is no longer required!

Here’s how things work, starting today:

For the next month or so we’ll continue to accept HTML entities through the web application (and double-encoded entities through the API), and display them just like we have for years. But after that, we’ll migrate all the exiting entity-based characters into real UTF-8 codepoints, and flip the switch such that plain text is plain text.

When that happens, the behaviour will change like this:

Enjoy!

Song In Code Contest Winners

by Anton Nguyen on September 8, 2011

We recently held a contest to see all the creative ways you could turn your favourite songs into code. Everyone submitted so many amazing entries, that it made it almost impossible to pick our favourite submission!

We raged long and hard, debating who should come out on top. After the dust had settled and votes were counted, there emerged a victor: Hong Hua with his entry of Aladdin’s “A Whole New World”!

Congratulations Hong! You’ll be receiving a $100 Amazon Gift Certificate!

John Girvin came in a close 2nd place with his entry of “Blame it on the Boogie”. We thought he deserved a prize as well for all his hard work, so he’ll also receive a $50 Amazon Gift Certificate consolation prize!

Thanks to everyone who participated! We hope you had fun!

The Winning Entry

A Whole New World – Aladdin
Codified by Hong Hua

Song Lyrics:

A whole new world
A hundred thousand things to see

Song In Code:

World *newWorld = [[world alloc] init];
int thingsToSee = 0
for (thingsToSee; thingsToSee < 100000; thingsToSee++)
   [newWorld seeThings: thingsToSee];
[newWorld release] //clean up after magic carpet ride

The way Hong codified these lyrics was simple and clever. There's the initialization of the world, then looping of all hundred thousand things to see, and finishes it off on a nice touch with excellent memory management!

The Runner-Up

Blame It On The Boogie - The Jacksons
Codified by John Girvin

Song Lyrics:

Don't you blame it on the sunshine
Don't blame it on the moonlight
Don't blame it on the good times
Blame it on the boogie

I just can't
I just can't
I just can't control my feet

Song In Code:

blame.remove(SUNSHINE);
blame.remove(MOONLIGHT);
blame.remove(GOOD_TIMES);
blame.add(BOOGIE);
feet.setControl(null);

With his code, all the blame is removed and only "Boogie" is added back. What really won us over, is how at the end, feet control gets set to null.

A few other great entries

if (touchers.contains(you)) {
   throw new InvalidOperationException(CANT);
}
hammerTime.stop();

$ touch /this
touch: /this: Permission denied

MC Hammer - Can't Touch This

for (SlimShady ss : slimShadys) {
   if (ss.isReal()) {
      ss.requestPoseChange(STANDING);
   }
}

Eminem - The Real Slim Shady

for (i = countdowns.getFinal(); i >= 0; i--);
setDestination(VENUS);
setPose(Standing.TALL);
addObserver(VENUS.inhabitants());

Europe - The Final Countdown

while (TRUE)
   [self celebrate:oneMoreTime];
[dancing stop];

Daft Punk - One More Time

LYRICS = (
   ('give you up'),
   ('let you down'),
   ('run around and desert you'),
   ('make you cry'),
   ('say goodbye'),
   ('tell a lie and hurt you'),
)

for lyric in LYRICS:
   print 'Never gonna ', lyric

Rick Astley - Never Gonna Give You Up

getItOn: function(baby) {
      if (baby.curPos != spirit.move.call(baby).curPos) {
         this.groove(baby);
      }
   },

   groove: function(baby) {
      // this is a family blog
   }
};

Marvin Gaye - Let's Get It On

>> problems.count
=> 99
>> problems.include?(:female-dog) # this is a family blog
=> false

Jay-Z - 99 Problems

Songs In Code Contest

by Anton Nguyen on August 18, 2011

A Michael Jackson song came up on my iPod one day while I was writing unit tests. As I listened to him croning on and on about how “Billie Jean” was not his lover, I thought to myself, “why doesn’t he just write a unit test and assert this fact once and for all?”

This idea reminded me of a twitter meme a few years back (#songsincode), that was very entertaining. I wanted to give it try with other songs, so I gathered my fellow brogrammers and we started codifying our favourite songs.

Contest

We want to see what creative ways you can turn your favourite songs into code! Email your entries to songcontest@freshbooks.com and we’ll award our favourite “Codified Song” with a $100 Gift Certificate for Amazon to buy all the cool swag you’ve been eyeing!

Send in your entries by 6:00 PM ET on August 23nd and the winner will be announced soon after!

Our Take on Songs in Code

assertFalse(self.lover == "Billie Jean");

Michael Jackson – Billie Jean

best_things_in_life = ['your kiss']

Hall & Oates – Kiss on My List

$this->assertTrue($this->much);

Splendau Ballet – True

function is_valid($action) {
   switch($action) {
      case "she kept looking at her watch":
      return true;

      case "but I cried the whole time":
      return true;

      case "I think she might of been a racist":
      return true;

      case "she put a bag on my head":
      return true;

   }
   return false;
}

The Lonely Island feat. Akon – I Just Had Sex

void say(string word) {
   if (word != "never") {
      printf(word);
   }
}

Justin Bieber – Never Say Never

try {
   get_all_love()
} catch {
   die_for_you();
}

function get_all_love() {
   throw new Exception("grenade");
}

Bruno Mars – Grenade

while (song.play) {
   whip_hair("back");
   whip_hair("forth");
   song.line.next()
}

Willow Smith – Whip My Hair

SELECT s.name FROM song s JOIN artist a ON s.artistid = a.artistid WHERE a.lname = "ever" AND a.fname = "greatest";

Drake – Forever

$detroit = new City("Rock");

Kiss – Detroit Rock City

class City extends RockNRoll {}

Starship – Built This City

~/$ make dreams-come-true

Hall and Oates – Make My Dreams Come True

git blame foot_control.c
0fcfd160 (The Boogie 1978-10-23 13:04:43 -0700 1) #include "funk.h"

Jackson 5 – Blame it on the boogie

if (self.bank_account.totalBalance() == 1000000)):
   return buyFurCoat()

def buyFurCoat():
   // That's cruel!
   return notRealFurCoat()

Barenaked Ladies – Million Dollars

~/$ cat anthem
45 18 * * * shuffle

LMFAO – Party Rock Anthem

$(document).see(bad_moon.arise());
   .see(trouble);
   .see([earthquake, earthquake, lightning])
   .see(new Date('25:00:00'))

Creedence Clearwater Revival: Bad Moon Rising

return concat(("with" || "without"), "you");

U2 – With or Without you

while(night() && day()){
   run();
}
exit();
getAway();

A Flock of Seagulls – I Ran

assertLike(record, baby.spin(me.right_round()))

Dead or Alive – You Spin Me Round

~/$ ls -a
. ..
~/$ touch virgin

Madonna – Like a Virgin

if(!self.fight){
   self.party.right = False;
}

Beastie Boys – Right to Party

self.private = {
   palms: sweaty,
   knees: weak,
   arms: heavy,
   sweater: { vomit: spaghetti},
   nervous: True
};

self.public = {
   calm: True,
   ready: True
};

self.forget(lyrics)

Eminem – Lose yourself

$days = array('yesterday' => 'Thursday', 'today' => 'Friday', 'tomorrow' => 'Saturday', 'afterwards' => 'Sunday');

Rebecca Black – Friday