Re: perl script & mysql after insert trigger

something silly spotted here...

If you are using transactions, then this is most likely your problem. You are in a catch-22. The trigger fires as part of the transaction, but the data is not there until the commit. The general concept behind triggers is to perform additional table-specific operations (regardless of individual sql operation) that can also be rolled-back if necessary. You could create another table, ie email_retrieve, with a single column containing the row-id. Your trigger would insert into that table instead of invoking 'deadweight'. Then, all you have to do is periodically scan this table for entries, call your script, and remove the entries.

fnord

Comment on Re: perl script & mysql after insert trigger

Replies are listed 'Best First'.
Re^2: perl script & mysql after insert trigger by vxp (Pilgrim) on Nov 27, 2010 at 17:35 UTC
I see. I went around the problem in my usual proctology-like way. instead of: `SET @exec_var = sys_exec(CONCAT('/usr/bin/perl /home/vxp/veng/bin/emai +l_retrieve.pl ', NEW.id));` [download] I now have: `SET @exec_var = sys_exec(CONCAT('/usr/bin/perl /home/vxp/veng/bin/emai +l_retrieve.pl ', NEW.id, ' &'));` [download] So the trigger places my script in the background now ("&"). That lets it "return" immedeately, mysql finishes the commit, and the script gets its values. It's a through-the-ass solution but it seems to work for my purposes :)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: perl script & mysql after insert trigger
by vxp (Pilgrim) on Nov 27, 2010 at 17:35 UTC

I went around the problem in my usual proctology-like way.

instead of:

SET @exec_var = sys_exec(CONCAT('/usr/bin/perl /home/vxp/veng/bin/emai
+l_retrieve.pl ', NEW.id));
[download]

SET @exec_var = sys_exec(CONCAT('/usr/bin/perl /home/vxp/veng/bin/emai
+l_retrieve.pl ', NEW.id, ' &'));
[download]

So the trigger places my script in the background now ("&"). That lets it "return" immedeately, mysql finishes the commit, and the script gets its values.

It's a through-the-ass solution but it seems to work for my purposes :)

[reply]
[d/l]
[select]