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.