MySQL触发器-插入后触发器+ UDF sys_exec()问题 [英] MySQL Triggers - AFTER INSERT trigger + UDF sys_exec() issue

问题描述

问题:我有一个表，其中包含某些记录.插入完成后，我想通过MySQL的sys_ * UDF调用外部程序(php脚本). 现在，问题-我已将触发器的记录ID传递给脚本. 当我尝试通过脚本提取数据时，得到0行. 在我自己的测试中，我得出的结论是，触发器在实际插入之前会调用php脚本并传递参数，因此对于给定的ID我没有任何记录. 我已经在MySQL 5.0.75和5.1.41(Ubuntu OS)上进行了测试. 我可以确认在实际插入之前将参数传递给了脚本，因为我已经添加了sleep(2);到我的PHP脚本，我已经正确获得了数据. 没有sleep();语句，我收到0条给定ID的记录.

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.

我的问题是-如何解决此问题而不必在php脚本中进行某种形式的延迟编码? 我没有自由地假设2秒(或10秒)将是足够的延迟，所以我希望当一个命令完成时-另一个命令被执行时，一切都自然地"流动.

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.

我假设如果触发器的类型为AFTER INSERT，则在MySQL实际插入数据后，触发器主体内的所有内容都将被执行.

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.

表布局:

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

触发器布局:

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 ;

免责声明:我知道使用sys_exec函数时的安全性问题，我的问题是MySQL不会插入FIRST，然后使用必要的参数调用脚本. 如果有人可以解决此问题，或者有其他方法不涉及SELECT INTO OUTFILE和FAM的使用，我将不胜感激.预先感谢.

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.

推荐答案

即使您使用AFTER触发器，该行也尚未提交.但是sys_exec()直到php脚本退出后才会返回，因此AFTER触发器无法完成，因此也无法提交INSERT.

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.

您不应使用触发器来执行此任务.充其量，您应该使用触发器来设置标志"列，然后编写一个外部过程来查找设置了标志的行，然后调用该PHP脚本.这样，将仅处理已成功插入并提交的行.

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.

这篇关于MySQL触发器-插入后触发器+ UDF sys_exec()问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！