When a new row in database is added, an external command line program must be invoked

Question

Is it possible for MySQL database to invoke an external exe file when a new row is added to one of the tables in the database?

I need to monitor the changes in the database, so when a relevant change is made, I need to do some batch jobs outside the database.

Answer 1

I think it's going to require adding a User-Defined Function, which I believe requires recompilation:

MySQL FAQ - Triggers: Can triggers call an external application through a UDF?

Answer 2

1. you could do what replication does: hang on the 'binary log'. setup your server as a 'master server', and instead of adding a 'slave server', run mysqlbinlog. you'll get a stream of every command that modifies your database.

2. step in 'between' the client and server: check MySQLProxy. you point it to your server, and point your client(s) to the proxy. it lets you interpose Lua scripts to monitor, analyze or transform any SQL command.

Answer 3

Chad Birch has a good idea with using MySQL triggers and a user-defined function. You can find out more in the MySQL CREATE TRIGGER Syntax reference.

But are you sure that you need to call an executable right away when the row is inserted? It seems like that method will be prone to failure, because MySQL might spawn multiple instances of the executable at the same time. If your executable fails, then there will be no record of which rows have been processed yet and which have not. If MySQL is waiting for your executable to finish, then inserting rows might be very slow. Also, if Chad Birch is right, then will have to recompile MySQL, so it sounds difficult.

Instead of calling the executable directly from MySQL, I would use triggers to simply record the fact that a row got INSERTED or UPDATED: record that information in the database, either with new columns in your existing tables or with a brand new table called say database_changes. Then make an external program that regularly reads the information from the database, processes it, and marks it as done.

Your specific solution will depend on what parameters the external program actually needs.

If your external program needs to know which row was inserted, then your solution could be like this: Make a new table called database_changes with fields date, table_name, and row_id, and for all the other tables, make a trigger like this:

CREATE TRIGGER `my_trigger`
AFTER INSERT ON `table_name`
FOR EACH ROW BEGIN
  INSERT INTO `database_changes` (`date`, `table_name`, `row_id`)
  VALUES (NOW(), "table_name", NEW.id)
END;

Then your batch script can do something like this:

1. Select the first row in the database_changes table.
2. Process it.
3. Remove it.
4. Repeat 1-3 until database_changes is empty.

With this approach, you can have more control over when and how the data gets processed, and you can easily check to see whether the data actually got processed (just check to see if the database_changes table is empty).

Answer 4

I think it's really a MUCH better idea to have some external process poll changes to the table and execute the external program - you could also have a column which contains the status of this external program run (e.g. "pending", "failed", "success") - and just select rows where that column is "pending".

It depends how soon the batch job needs to be run. If it's something which needs to be run "sooner or later" and can fail and need to be retried, definitely have an app polling the table and running them as necessary.