MySQL trigger to check after insert if no duplicate then run sys_exec udf

Question

MySQL version: 5.1.73 Database client version: libmysql - 5.1.73

I'm trying to check if NEW.src exists in the last one hour and if it doesn't then execute sys_exec udf.

I have the following trigger in mysql. As this is an after insert trigger my IF condition will check if there is only one value in the last one hour and then proceed with the sys_exec statement.

BEGIN
SET @numofcalls = (SELECT count(src) FROM `cdr` WHERE calldate >= DATE_SUB(NOW(),INTERVAL 1 HOUR) AND src = NEW.src);
IF (numofcalls = 1) then
SET @missed_call = sys_exec(CONCAT('/usr/bin/php /var/lib/asterisk/agi-bin/api_pbx/call_api.php ', NEW.src));
END IF;
END

I get no syntax error when saving the trigger. When a new record is inserted the sys_exec statement doesn't seem to run and exits from the IF condition.

Can someone please suggest what I'm doing wrong here.

If in last hour no duplicates for NEW.src then you are running sys_exec udf. so your if condition should be numofcalls = 0 instead of numofcalls = 1. Also you need to check your udf is present or not by using below query SELECT name FROM mysql.proc WHERE name = 'my_name'. — Bhushan
– Bhushan, Commented Sep 25, 2017 at 5:32
First thing, as this is a after insert trigger the data should already be there. Hence the if condition value is 1. Secondly, the udf is working without the if condition or the select query by running directly with the after insert trigger. — Vinod Chandrasekar
– Vinod Chandrasekar, Commented Sep 28, 2017 at 4:49
In Your if condition is IF (numofcalls = 1) then. I think it should be IF (@numofcalls = 1). Othere then this I think your code is OK. — Bhushan
– Bhushan, Commented Sep 28, 2017 at 5:07

Bill Karwin · Accepted Answer · 2017-09-25 14:27:05Z

1

I recommend you should not execute php programs from a MySQL trigger.

Reasons:

When a trigger runs (even an after trigger), the insert has not been committed in the database. If your PHP script tries to query the database, it will not be able to see the uncommitted data. Besides, the insert may be rolled back, but the phone call cannot be rolled back.
You can't check errors or exit status from the PHP script. It's hard to debug whether the PHP script was successful or not.

Instead, I recommend you should not use a trigger, but insert the data and commit the transaction, and THEN use application code to check if you should run the call, and then execute it from your application. In other words, do not execute code that has effects outside the database.

edited Sep 25, 2017 at 14:27

answered Sep 25, 2017 at 5:18

Bill Karwin

567k87 gold badges710 silver badges871 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vinod Chandrasekar Over a year ago

I'm not doing any transaction with the mysql database from my php script in the entire flow.

Bill Karwin Over a year ago

You're always doing a transaction, even if it's autocommit. Nevertheless, if you run an external program from a trigger, and that program tries to query the row whose insert spawned the trigger, the row won't be visible. Try it.

Collectives™ on Stack Overflow

MySQL trigger to check after insert if no duplicate then run sys_exec udf

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related