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.

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

Solution 2

If I understand it clearly, you insert a row in your DB. That invoke a trigger that launch an external command written in PHP. That command queries in its turn the same DB by using the id of the inserted row?

I don't think this is a problem of "delay".

The real "problem" is your initial insert and you external command connect to the same DB on two different sessions -- probably in two different transactions (depending your database engine and your transaction isolation level).

I assume, when the trigger in invoked the row insert is not yet committed to the DB. So the external command still see the DB as it was before.

---

BTW, if the above explanation is quite speculative -- what is more evident to me is that you should probably think about a different design than trying to made that work as it is.

Comments

[O. Jones]

So, dispatcher.php has its own client connection to mySQL? This isn't going to work reliably, because the insert / update transaction in which your trigger runs is not complete until after your trigger is done. So, dispatcher.php sees either inconsistent or pre-transaction data. At any rate it's like eating a slice of pie while the pie is still in the oven. triggers should stay inside the data base server.

[Mihael]

You either didn't read what I wrote or don't understand the question, seeing you asked a question after I've explained in rather simple manner what's going on. I don't intend to be rude, but your answer isn't helping me in any sort of way.

[rantsh]

@Mihael did you ever figure this out?

[Bill Karwin]

@JDPeckham, sure you could pass the whole row to the script. But what if the INSERT is part of a transaction that is ultimately rolled back? Then the script has just acted on data that will never be visible in the database.

[JDPeckham]

Thanks Bill! That makes sense. I guess one would have to call an extra procedure or something.