Flow of the program:
I’ve got a table and I’m inserting a record there.
Using AFTER INSERT trigger, I retrieve the ID of the row and I pass it to a php script.
PHP script then pulls the data from MySQL with the corresponding ID, encodes the data to JSON and sends it to the browser.
Problem: AFTER INSERT trigger seems to be invoking my external program (in this case PHP script) before the actual data gets inserted into MySQL db.
Naturally, that troubles me since I cannot retrieve the row data and display it.
I can confirm that it’s exactly that behavior because after adding sleep(2) to the php script itself - it managed to pull the record properly.
Table layout in question:
CREATE TABLE test (
id int not null auto_increment PRIMARY KEY,
random_data varchar(255) not null
);
Trigger:
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 ;
MySQL UDF in question: http://www.mysqludf.org/lib_mysqludf_sys/index.php
MySQL versions used: 5.0.75 and 5.1.41 @ Ubuntu.
What troubles me is the workings of AFTER INSERT trigger.
Disclaimer: I’m aware of security risk by using sys_ calls from the forementioned UDF. I don’t want to use SELECT INTO OUTFILE + FAM in order to achieve the same result, I’m mostly wondering if is this intended behavior of AFTER INSERT trigger or not. If anyone has any solution or alternative approach on how I can establish “natural” execution of all the elements in question - I’d be very grateful.