MySQL Triggers - AFTER INSERT trigger + UDF sys_exec() issue

Question

Problem: I've got a table which holds certain records. After the insert has been done, I want to call an external program (php script) via MySQL's sys_* UDFs. Now, the issue - the trigger I have passes the ID of the record to the script. When I try to pull the data out via the script, I get 0 rows. During my own testing, I came to a conclusion that the trigger invokes the php script and passes the parameters BEFORE the actual insert occured, thus I get no records for given ID. I've tested this on MySQL 5.0.75 and 5.1.41 (Ubuntu OS). I can confirm that parameters get passed to the script before actual insert happens because I've added sleep(2); to my php script and I've gotten the data correctly. Without sleep(); statement, I'm receiving 0 records for given ID.

My question is - how to fix this problem without having to hardcode some sort of delay within the php script? I don't have the liberty of assuming that 2 seconds (or 10 seconds) will be sufficient delay, so I want everything to flow "naturally", when one command finishes - the other gets executed.

I assumed that if the trigger is of type AFTER INSERT, everything within the body of the trigger will get executed after MySQL actually inserts the data.

Table layout:

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

Trigger layout:

DELIMITER $$

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

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

DELIMITER ;

Disclaimer: I know the security issues when using sys_exec function, my problem is that the MySQL doesn't insert FIRST and THEN call the script with necessary parameters. If anyone can shed some light on how to fix this or has a different approach that doesn't involve SELECT INTO OUTFILE and using FAM - I'd be very grateful. Thanks in advance.

Answer 1

Even if you use an AFTER trigger, the row isn't committed yet. But sys_exec() doesn't return until the php script exits, so the AFTER trigger can't complete, therefore you can't commit the INSERT either.

This is by design. After all, you may do more operations within the same transaction, or you may roll back the transaction. That's the problem with invoking external processes from a trigger: external processes can't see data within the scope of the transaction in the database.

You shouldn't do this task with a trigger. At best, you should use the trigger to set a "flag" column and then write an external process to look for rows with the flag set and then invoke that PHP script. That way only rows that have successfully been inserted AND committed will be processed.