code for MySql / MariaDB to trigger / signal external script from database

One feature I often wish MySQL / MariaDB (I’ll refer from now to just MariaDB referring to both) had was the ability to trigger or launch an  external script or open a socket connection to some external site; on a database event. Perhaps using a stored procedure in other words generate a signal /event to an outside application directly.

Most applications that want near real time notifications of database changes resort to some sort of polling, but having a process sit idling and simply querying the database waiting for a specific change doesn’t seem very efficient. So I wanted to know ; is it possible to MariaDB to notify a remote script or process that a particular piece of data has changed?

After a bit of investigation , I discovered there is no native (built-in procedure / function ) to do this, so one has to resort to some customization to make this happen. Here I’ll highlight  a couple of approaches…

• Approach #1 : Write a Custom UDF to send a signal on an event

MariaDB (And MYSQL) offer the ability to write User Defined Functions (essentially database plug-in functions).  A user-defined function (UDF) is a way to extend MariaDB with a new function that works like a native (built-in) MariaDB function CONCAT() or  ABS(). These functions created in C/C++ are essentially plugins to the MariaDB SQL Server.  Because it’s just standard C/C++ code you pretty much can craft any command you wish.

Be careful what you wish for, as this of course introduces pretty severe security  issues as well as performance problems and ACID compatibility challenges, plus you need to recompile parts of the server to make sure it properly detects and uses your UDF. So its something too keep in mind.  If you’re not up to creating your own UDF, there are libraries of many custom UDF already existing that may provide the functionality you need.

So rather than write a UDF from scratch we’ll make use of one of the many popular ones, such as this UDF that essentially makes an HTTP REST CAL call, it could of course be any other UDF,but for this posit I just want to illustrate how I can  simply signal an outside service that something changed in my DB..

In this example we’re going to make use of mysql-udf-http to  basically issue a REST call out to a remote web service which will simply update a value or post a result. We’ll use a MariaDB Stored procedure to call the UDF when a particular condition is met.

Starting with a simper  example can be we’ll initiate a trigger on a  row insert and have it automatically post to a web page we’ll trigger the signal.

CREATE TABLE trig_test (
id int not null auto_increment PRIMARY KEY,
random_data varchar(255) not null
);

Here’s the a External PHP trigger…

DELIMITER $$

CREATE TRIGGER `test_after_insert` AFTER INSERT ON `trig_test` 
FOR EACH ROW BEGIN

SET @exec_var = sys_exec(CONCAT('php /var/www/xyz/servers/server_side.php ', NEW.id));
END;
$$

DELIMITER ;

Here’s a similar example triggering a Python Event

DELIMITER $
CREATE TRIGGER ExtEvent_Trigger
AFTER INSERT ON QuotesTable
FOR EACH ROW
BEGIN
DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd = CONCAT('python /home/python_code.py');
SET result = sys_exec(cmd);
END;
$
DELIMITER ;

• Approach #2: Create an in-memory MaraiDB database and poll

Another simple,but not as efficient approach is a modified polling approach, the easiest of the 3 approaches is to simply create an in-memory table. MariaDB has the ability to store temporary values in-memory (not saved to disk) and this means that you can poll an in-memory table without not too much network or server load. So it’s not a pure non-polling solution but it should works well from a resource constraint, and it has the advantages of being very easy to implement without any re-compilation or re-configuring of the server.

Then I’ll use Node.js + Socket.io to poll for changes and then create a simple Server Side event and stream the results to a webpage. You could also as easily used PHP as I did in the prior approach.

• Approach #3: Monitor and Parse the MariaDB Binary log and react.

Another more esoteric approach is to simply monitor the DB Binary Log, which “the log contains “events” that describe database changes such as table creation operations or changes to table data.”. As noted in the documentation : Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrements. Additionally monitoring and reacting to the log is generally unrelated to database operations, but still real-time enough to be of use.

I will not go into the code details of this approach, but for those interested, you read this article on Identifying useful information from Binary log. Basically it innvolves making use of mysqlbinlog  command line utility to monitor the log for updates.

• Approach #4: Hybrid model, Use MariaDB to feed a database that’s already designed to do this.

You’re probably asking yourself, hasn’t someone done this, and actually there’s  new popular open source NoSQL  database called RethinkDB . RethinkDB dubbed “The open-source database for the realtime web”,  basically is a NoSQL, clustered capable  database, that can store data and he generate JSON output directly. So why not have MariaDB simply load data that you want to alert on into ReThinkDB , and let ReThink DB handle the signal outputs..

This of course requires you to have two database servers running, and having some mechanism to get data form MAraiDB to RethinkDB. I will not fully detail this process, but its simplest level it requires some import script to run periodically to load data into REThink DB (which I’ll grant you is not terribly realtime).

Conclusion

After reading the info , it becomes obvious that this could have been a built in function. But because of the performance and security nature of modern databases this sort of real time alerts are not usually implemented in the core database functionality.

And this is something to be cognizant of, adding this sort of convenience feature, may expose an additional security vulnerabilities, but more likely  a poorly implemented version will impact performance greatly.

Additionally, this type of  UDF Function breaks ACID issues like Commit and Rollback situations become problematic as leaving the confines of the database system, you can no longer undo (in the case of rollbacks) certain settings, so obviously this would not be useful for situations where you need true ACID and true transactional integrity.With those items in mind, you can use this approach in a carefully constrained environment.

I hope you found this post useful, feel free to leave a comment and enlighten me on some other approaches that can be used.