3

I have a table T1 and firing a trigger after insert on T1 and calling external PHP programm using UDF where the app is looking for a last inserted data and do a action on condition base but it is not working as expected.. Please help as I guess that we cannot select the data from the same table where we are firing a trigger?? Is it so?

TRIGGER

DELIMITER @@
CREATE TRIGGER CALL
AFTER INSERT ON call_test
FOR EACH ROW
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result int(10);
 SET cmd=CONCAT('php /var/www/html/test/call.php');
 SET result = sys_exec(cmd);
END;
@@
DELIMITER ;

call.php

    function connect_db() {
$db_connection = mysql_connect("localhost","root","test") or die (mysql_error());
$db_select = mysql_select_db('testdb') or die (mysql_error());
}
connect_db();

$sql2=mysql_query("SELECT * FROM call_test ORDER BY createtime desc limit 1") or die(mysql_error()); 
    $res = mysql_fetch_array($sql2);
    if(strstr($res['name'],'go')!=false)
    {
    echo "inserted";
    //sleep(10);

$sql4=mysql_query("insert into call_test_auto (name,createtime) values ('from UDF automatic2','".$today."')") or die(mysql_error());          
}
else
{
    echo "not inserted";
}
Sam
  • 41
  • 6
  • 1
    Can you add some code? It is hard to tell what is going wrong from only 'is not working as expected' – Michel Nov 05 '15 at 09:46
  • What exactly do you mean by not working as expected? We cannot possibly guess the error you encountered! Where do you connect to the mysql db in call.php? Do you use innodb as table engine? – Shadow Nov 05 '15 at 09:57
  • Added connect too..What is actually happening here is when the trigger calls after insert it does not insert the value in a table as it is looking for the PHP file and run the select command first and then insert works... – Sam Nov 05 '15 at 10:00
  • can we select the data from the same table where we are firing a trigger?? – Sam Nov 05 '15 at 10:03
  • have you ever successfully called a UDF from a trigger or is this all wishful thinking inside one. I know one can, but have you – Drew Nov 05 '15 at 10:17
  • Many times and it is also calling external program with the help of UDF but my question and requirement is little different on this project.. – Sam Nov 05 '15 at 10:19
  • Just want to know is it possible or not -> can we select the data from the same table where we are firing a trigger?? – Sam Nov 05 '15 at 10:21

1 Answers1

1

yes you can Sam. It is in row aliased by NEW. See Trigger Syntax and Examples

Your task is to do whatever is necessary to get things into variables so that you can concat and call your UDF with them as command line parameters. As seen here, where that gentleman sent a command line argument of Sarbajit to his C program he had compiled.

In your case, you are just calling PHP and nothing is happening!

Enabling sys_exec

It is not as if your average Joe is likely to even going to survive the call to sys_exec() without receiving a Syntax Error. See This Question on the Stack and the link for github at top of that question.

Things Failing silently

Remember that mysql Triggers and Events (as in Create Event) run and succeed to your wishes or don't, but do so silently. You might not even know that the above Syntax Error occurred. That would not be the case for a Stored Procedure run by a user in a query that had error reporting.

Of course, a Trigger or Event could call a Stored Procedure, but there is no UX to that, so sys_exec() syntax errors would go unnoticed.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Thanks..Let me try again but my code is correct ..right? – Sam Nov 05 '15 at 10:30
  • my gut feel is that it is not correct, as one would have to path to the executable in a risk-adverse manner. Yours is assuming the shell that is achieved miraculously finds the executable in the default dir that the shell achieves upon having life breathed into it, or that its environment variables have PHP available. – Drew Nov 05 '15 at 10:37
  • More importantly, UDF's need to be lean and mean, or should be, not bloaty .exe's that have painful startup time. That is why they are traditionally written in C. It is, afterall, a best practice to not put a strain on a resource such as the daemon that is servicing high concurrency requests. But to each their own. – Drew Nov 05 '15 at 10:39
  • Path is correct as when I am running the same command in CLI it is working fine – Sam Nov 05 '15 at 10:40
  • One very quick way to determine that is the following. Let's take parameter passing off the table. Call a PHP file that opens for append a file and writes Hello World to it. Let us know, because we would like to know – Drew Nov 05 '15 at 10:42
  • Working Perfect the below one DELIMITER $$ CREATE TRIGGER CALL AFTER INSERT ON call_test FOR EACH ROW BEGIN SELECT callnumber FROM callfile INTO OUTFILE '/var/www/html/test/test.txt'; END $$ DELIMITER ; – Sam Nov 05 '15 at 10:45
  • The next step is to determine if calling a PHP file in general could take parameter passing the way concat does for C. That is probably a negatory, like as in argc, argv, but not a big deal, because your shell could be with ...myPhpThing.php&a=1?b=Fish?c=10 ... via the concat – Drew Nov 05 '15 at 10:46
  • that "Working Perfect" you just mentioned is a trigger running in mysql that merely makes mysql commands. Nothing to do with UDF's, so not sure what you mean – Drew Nov 05 '15 at 10:48
  • whatever you do, don't turn mysql into the modern equivalent of Perl Scripts and CGI – Drew Nov 05 '15 at 10:53
  • oops sorry you are right..I havent use UDF now for this testing ..ok as per you suggested I am testing with the passing the parameters now – Sam Nov 05 '15 at 11:00
  • Drew it is working and passing the parameter and works fine but the issue is same it is selecting the previous record not the current insertion record – Sam Nov 05 '15 at 11:13
  • 1
    look at this [Pastie](http://pastie.org/10531625). Tell me what you think. Then apply it to what you are doing. But I think this question has been answered :) – Drew Nov 05 '15 at 11:28
  • 1
    and the reason that the trigger is named Callx is because your `CREATE TRIGGER CALL` generates a syntax error. Call is a reserved word. – Drew Nov 05 '15 at 11:32