Triggering a shell script from MySQL

Sometimes you need to trigger a script when data changes in mysql.  Sometimes there just aren’t other hooks into the system and the database is your last resort.

It seems like this would be a common use case with a simple solution, but no.

MySQL does support triggers.

However what you can do inside a trigger is limited to what you can do in mysql.  This is good for security.

Using the following DOES NOT work!

\! /bin/ls >> /log/yourlog.txt

The !\ (bang or exclamation point + backslash) is a mysql console feature for running commands in the console.  These are ignored on the actual server.  Do not be fooled.  Since you are testing in the console, it will appear to work once, but the trigger will not cause your code to fire.

So you have two options:

1.) Polling

2.) MySQL UDF (plugin)


Yes, polling sucks.  But what you can do is still create a trigger that uses a separate (maybe in memory) table for capturing the changes.  Then your polling script only needs to look at that table to do its work.

So you could use something like this:

Create the temp_changes table (this example creates an in memory table):

CREATE TABLE temp_changes (changed_id INT, from_value VARCHAR(40), to_value VARCHAR(40)) ENGINE=MEMORY MAX_ROWS=500;

Create the mysql trigger:

CREATE TRIGGER tg1 AFTER UPDATE ON `table_i_want_to_watch`
INSERT INTO temp_changes SET from_value = OLD.column_i_want_to_track, to_value = NEW.column_i_want_to_track, changed_id =;
END $$

The above will create a row in the temp_changes table with the old and new values and id of the column I want to track in the table I want to watch.  Substitute column_i_want_to_track and table_i_want_to_watch with your own table and column names.

Read the MySQL trigger documentation if you need to do something different.  This is just an example.

Now your external polling script can query only the temp_changes table without polling your whole database for changes.

MySQL UDF (plugin)

MySQL UDFs offer a powerful way to extend the functionality of your MySQL database.  But with great power comes great responsibility right?  (A. Yes.)

These plugins are written in C.  They need to be compiled and installed in your mysql plugins folder on the mysql server.

Plugins add custom functions you your mysql server.

Security is a major concern with this option because these functions run with the same privileges as the mysqlserver user.

There are a number of plugins published here including lib_mysqludf_sys, that will allow you to run shell commands.

Here is the justifiably scarey warning from that site:

Be very careful in deciding whether you need this function. UDFs are available to all database users – you cannot grant EXECUTE privileges for them. As the commandstring passed to sys_exec can do pretty much everything, exposing the function poses a very real security hazard.

Even for a benign user, it is possible to accidentally do a lot of damage with it. The call will be executed with the privileges of the os user that runs MySQL, so it is entirely feasible to delete MySQL’s data directory, or worse.

SQL injection takes on new meaning when the attacker can also run any command on your server.

So my recommendation is to download the source and modify it to hard code a specific path to the executable you want to run if you go this route. I was going to do this, but I’ve had to move on to other endeavors now.

Using the UDF plugin still involves using the mysql trigger functionality.  See Mike E’s solution on this stackoverflow post.

Whether to store data in SQL as key value pairs

It can be tempting to store unstructured data as key value pairs to avoid lots of schema changes and capture dynamic data.  But it can lead to problems.

  • Overly complex queries/reporting
  • No type support

This article explains some of the issues.

My conclusion is that it is fine if you will only be accessing the data rather than querying it, or as a temporary capture means until you’ve identified where the data really belongs.

If you have another way to query, like a Lucene index it may also be perfectly fine.


A key value store NoSQL solution may be appropriate, but that brings with it other baggage.  Depending on the NoSQL solution you choose, you may have to do some extra work to get the type of search you need or use a Lucene style indexing solution anyway.

If you are using PostgreSQL, you can get much of the best of both worlds using hstore.

And you can easily use it with rails!

This will have the drawback of not being portable to other SQL backends, but that may be a tradeoff worth making.

With the PostgreSQL solution, you can retain one datastore and still allow some ability to store unstructured key values.  Then as needed, you can integrate these back into the schema to permanent normalized columns.