Search code examples
phpmysqltriggersmysql-udf

MYSQL trigger that externally launches a PHP script

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 ;
Solution

  • 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.