CREATE TRIGGER sys_exec and python

Question

my python on /usr/esercizi/ is:

#!/usr/bin/python
import datetime
now = datetime.datetime.now()
aa = now.strftime("%Y-%d-%m %H:%M | %S")

out_file = open("/usr/esercizi/test.txt","w")
out_file.write("La data di oggi \n\n")
out_file.write(aa)
out_file.close()

made for test purpose I like it to be called from a TRIGGER:

mysql> CREATE TRIGGER `notifica_cambiamenti` AFTER UPDATE ON `valore`
    -> FOR EACH ROW BEGIN
    ->
    -> SET @exec_var = sys_exec(CONCAT('python /usr/esercizi/tre.py ', NEW.valore));
    -> END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

the table has only two columns: id and valore. every time change the valore should run the tre.py

I also give:

chown mysql:mysql tre.py | and chmod 777 tre.py

the Query OK, seems to indicate that there are no syntax errors but nothing happens on the file: test.txt

What am I doing wrong?

On the command line, what does running python /usr/esercizi/tre.py 1 output? What does running mysql -e "SELECT sys_exec('python /usr/esercizi/tre.py 1');" output? — Ross Smith II
– Ross Smith II, Commented May 21, 2013 at 16:44
thanks for the reply, in the first case it does the job. In the second I have problems with permissions: <code> mysql -e "SELECT sys_exec('python /usr/esercizi/tre.py 1');" ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)</code> — andry88
– andry88, Commented May 22, 2013 at 11:44
What does running mysql -h hostname -u root -ppassword -e "SELECT sys_exec('python /usr/esercizi/tre.py 1');" output? — Ross Smith II
– Ross Smith II, Commented May 23, 2013 at 1:13
@andry88 I think that writing to a table is much faster than calling non-native extension (sys_exec) that forks external process that writes to a file especially from a trigger in a middle of an update with locks and everything. — peterm
– peterm, Commented Jun 11, 2013 at 19:52

Abhik Dey · Accepted Answer · 2013-11-27 11:24:02Z

1

Your problem is solved,just do the following which I did for your problem...

CREATE TABLE log_table( datetime update_time, varchar() valore);

I just created the above table where the updated values will be stored by the trigger.

Now,I defined the trigger as follows..

  DELIMITER ;;

  CREATE TRIGGER `notifica_cambiamenti` AFTER UPDATE ON `valore`

  FOR EACH ROW

     BEGIN

       INSERT INTO log_table

          SET update_time = NOW(),
              valore      = NEW.valore);

   END;;

answered Nov 27, 2013 at 11:24

Abhik Dey

4034 silver badges12 bronze badges

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

Comments

BraveNewCurrency · Accepted Answer · 2013-07-06 20:08:56Z

What you are proposing has severe performance problems. (Imagine someone does a bulk insert of 1000's of rows -- it will create 1000's of python processes and quickly bring your server down.) Here is a much simpler way without the problems: (WARNING: untested pseudo code)

CREATE TABLE log_table( datetime update_time, varchar() valore);

CREATE TRIGGER `notifica_cambiamenti` AFTER UPDATE ON `valore`
-> FOR EACH ROW BEGIN
->
-> insert into log_table(now(), NEW.valore);
-> END;
-> $$

Now, you can make an async job (or cron job) that periodically processes the log table and deletes the rows it has seen: DELETE FROM log_table WHERE update_time < $LAST_ROW_SEEN.

Advanced note: This will work fine until you need to process many jobs per second, or reduce latency without polling the database 100's of times per second. In that case, you shouldn't use an SQL database. Use a real queue like AMPQ, Redis/Resque, Celery, etc. The client will insert the the row into SQL, then also throw the job into the job queue. You can have many workers processing jobs in parallel.

Collectives™ on Stack Overflow

CREATE TRIGGER sys_exec and python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related