MYSQL trigger that externally launches a PHP script

Question

I am currently trying to use a mysql trigger to launch a php script. When launched the script retrieves data from a particular website, and puts it into another table. I have tested the script, and it currently works when I run it using php5. I also have properly installed lib_mysqludf_sys, and moved the proper folders into the plugin folder for mysql. When I created the trigger I received no errors. It just wont run my php script. Where did I go wrong, and how could I fix it?

DELIMITER $$
CREATE TRIGGER getFriends
    -> AFTER INSERT ON tbluser
    -> FOR EACH ROW    
    -> BEGIN
    ->   DECLARE RESULT INT;
    ->   SET RESULT = sys_exec('php5 /var/www/html/getFriends.php');
    ->END$$;
DELIMITER ;

score 1 · Accepted Answer · answered Dec 15 '15 at 20:32

Confirm you're using the right plugin folder:

SHOW VARIABLES LIKE 'plugin_dir';

Recreate the function:

DROP FUNCTION IF EXISTS sys_exec;
CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';

Ensure you're using the full path to your executable:

SET RESULT = sys_exec('/usr/bin/php5 /var/www/html/getFriends.php');

And make sure that whatever user MySQL is running as has the permissions to access this script, and do whatever it's doing.

MYSQL trigger that externally launches a PHP script

1 Answers1