Call PHP script from MySQL trigger

Question

Is there a way to call a PHP page / function when a record is inserted into a MySQL database table? We have no control over the entry procedure. Is there a trigger mechanism that can call a PHP script back?

Answer 1

The trigger is executed on the MySQL server, and not on PHP (even if they are on the same computer).

So, I would say that this is not entirely possible - at least not easy.

However, given this entry from the MySQL FAQ on triggers :

23.5.11: Can a trigger invoke an external application via UDF?

Yes. For example, a trigger may be called by sys_exec() UDF, available here: https://github.com/mysqludf/lib_mysqludf_sys#readme

Thus, there may be a way through the UDF function that runs the php / script executable. Not so simple, but it seems possible .; -)

Answer 2

A friend and I figured out what to call Bernardo Damele sys_eval UDF, but the solution is not as elegant as we would like. Here's what we did:

• Since we use Windows, we had to compile the UDF library for Windows using Roland Bouman instructions and install them on our MySQL server.
• We created a stored procedure that calls sys_eval.
• We created a trigger that calls a stored procedure.

Saved procedure code:

 DELIMITER $$ CREATE PROCEDURE udfwrapper_sp (p1 DOUBLE, p2 DOUBLE, p3 BIGINT) BEGIN DECLARE cmd CHAR(255); DECLARE result CHAR(255); SET cmd = CONCAT('C:/xampp/php/php.exe -f "C:/xampp/htdocs/phpFile.php" ', p1, ' ', p2, ' ', p3); SET result = sys_eval(cmd); END$$; 

Launch Code:

 CREATE TRIGGER udfwrapper_trigger AFTER INSERT ON sometable FOR EACH ROW CALL udfwrapper_sp(NEW.Column1, NEW.Column2, NEW.Column3); 

I am not happy with the availability of the stored procedure, and I do not know if it creates additional overhead, but it really works. Each time a line is added to sometable, a trigger fires.

Answer 3

This should be considered a very bad programming practice in order to call PHP code from a database trigger. If you explain the problem that you are trying to solve using such "crazy" tricks, we can provide a satisfactory solution.

ADDED 03/19/2014:

I should have added some reasoning before, but only took the time to do it now. Thanks @cmc for the important point. Thus, PHP triggers add the following difficulties to your application:

• Adds certain security issues to the application (external calls to the PHP script, setting permissions, possibly installing SELinux, etc.), as @Johan says.

• Adds an extra level of complexity to your application (to understand how the database works, you now need to know both SQL and PHP, not just SQL), and you will also have to debug PHP, not just SQL.

• Adds an additional point of failure for your application (for example, an incorrect PHP configuration), which should also be diagnosed (I think the trigger should contain some debugging code that will record somewhere all the unsuccessful calls of the PHP interpreter and their reasons).

• Adds an additional performance analysis point. Each PHP call is expensive, since you need to run the interpreter, compile the script into bytecode, execute it, etc. This way, every request associated with this trigger will run slower. And sometimes it can be difficult to isolate query performance problems, because EXPLAIN says nothing about slow query query because of the trigger trigger performance. And I'm not sure how startup time is reset to a slow query log.

• Adds some problems to application testing. SQL can be tested quite easily. But to test the SQL + PHP triggers, you will need to apply some skills.

Answer 4

I found this:

http://forums.mysql.com/read.php?99,170973,257815#msg-257815

 DELIMITER $$ CREATE TRIGGER tg1 AFTER INSERT ON `test` FOR EACH ROW BEGIN \! echo "php /foo.php" >> /tmp/yourlog.txt END $$ DELIMITER ; 

Answer 5

I was thinking about this exact problem for the long poll case where I did not want the php script to have to constantly test db. The survey would have to be done somewhere, the memory would probably be better. Therefore, if some kind of trigger can put information in something like memcache, then php could poll, which would be much less intense in general. You just need the mysql method to use memcache. Perhaps in a predefined variable with a specific user id. Once the data is received, php can reset var until db installs it again. However, not sure about the timing. Perhaps the second variable to save the previous selected key.

Answer 6

If you have transaction logs in your MySQL, you can create a trigger to create an instance of the log. Cronjob can track this log and, based on events generated by your trigger, can call a php script. That is, if you absolutely do not have control over the insert.

Answer 7

To get notification from the database, I wrote a command line script using websocket to check for the latest updated timestamp every second. This ran like an endless loop on the server. If there is a change, all connected clients can send a notification.

Answer 8

I don't know if this is possible, but I always imagined being able to do this using the CSV storage engine in MySQL. I don’t know the details of this engine: http://dev.mysql.com/doc/refman/5.7/en/csv-storage-engine.html , but you can examine it and have a file watcher in your operating system that launches the call PHP if the file is modified.

Answer 9

Cronjob can track this log and, based on events generated by your trigger, can call a php script. That is, if you absolutely have no control over the insert. If you have transaction logs in your MySQL, you can create a trigger to create an instance of the log.

Answer 10

Get away from storage procedures as much as possible. They are quite difficult to maintain and VERY OLD STUFFLE;)