mySQL Trigger works after console insert, but not after script insert

I have a problem with a trigger.

I set up a trigger for update other tables after an insert in a table.

If I make an insert from MySQL console, all works fine, but if I do inserts, even with the same data, from an external python script, the trigger does nothing, as you can see bellow.

I tried changing the Definer to 'user'@'%' and 'root'@'%', but it's still doing nothing.

mysql> select vid_visit,vid_money from videos where video_id=487;
+-----------+-----------+
| vid_visit | vid_money |
+-----------+-----------+
|        21 |     0.297 |
+-----------+-----------+
1 row in set (0,01 sec)

mysql> INSERT INTO `table`.`validEvents` ( `id` , `campaigns_id` , `video_id` , `date` , `producer_id` , `distributor_id` , `money_producer` , `money_distributor` , `type` ) VALUES ( NULL , '30', '487', '2010-05-20 01:20:00', '1', '0', '0.009', '0.000', 'PRE' );
Query OK, 1 row affected (0,00 sec)

mysql> select vid_visit,vid_money from videos where video_id=487;                                                                  

+-----------+-----------+
| vid_visit | vid_money |
+-----------+-----------+
|        22 |     0.306 |
+-----------+-----------+

DROP TRIGGER IF EXISTS `updateVisitAndMoney`//
CREATE TRIGGER `updateVisitAndMoney` BEFORE INSERT ON `validEvents`
 FOR EACH ROW BEGIN
    if (NEW.type = 'PRE') THEN
                SET @eventcash=NEW.money_producer + NEW.money_distributor;
        UPDATE campaigns SET cmp_visit_distributed = cmp_visit_distributed + 1 , cmp_money_distributed = cmp_money_distributed + NEW.money_producer + NEW.money_distributor WHERE idcampaigns = NEW.campaigns_id;
        UPDATE offer_producer SET ofp_visit_procesed = ofp_visit_procesed + 1 , ofp_money_procesed = ofp_money_procesed + NEW. money_producer WHERE ofp_video_id = NEW.video_id AND ofp_money_procesed = NEW. campaigns_id;
        UPDATE videos SET vid_visit = vid_visit + 1 , vid_money = vid_money + @eventcash WHERE video_id = NEW.video_id;

        if (NEW.distributor_id != '') then
            UPDATE agreements SET visit_procesed = visit_procesed + 1, money_producer = money_producer + NEW.money_producer, money_distributor = money_distributor + NEW.money_distributor WHERE id_campaigns = NEW. campaigns_id AND id_video = NEW.video_id AND ag_distri开发者_如何学Pythonbutor_id = NEW.distributor_id;
            UPDATE eventForDay SET visit = visit + 1, money = money + NEW. money_distributor WHERE date = SYSDATE()  AND campaign_id = NEW. campaigns_id AND user_id = NEW.distributor_id;
            UPDATE eventForDay SET visit = visit + 1, money = money + NEW.money_producer WHERE date = SYSDATE() AND campaign_id = NEW. campaigns_id AND user_id= NEW.producer_id;
        ELSE
            UPDATE eventForDay SET visit = visit + 1, money = money + NEW. money_producer WHERE date = SYSDATE() AND campaign_id = NEW. campaigns_id AND user_id = NEW.producer_id;
        END IF;
    END IF;
END
//

I think that it's far more likely that you are encountering an uncaught error, rather than that the trigger is not executing, particularly since it executes successfully from the console.

You need to isolate where the error occurs - in the trigger itself, or in the calling script.

In your python script, print out the SQL statement that python sends to MySQL for execution in order to ensure that it is constructed as you expect - for example, if NEW.type does not equal 'PRE', the trigger will have executed, but will not result in any updates.

Also ensure that you are checking for errors on the insert. I'm not a python programmer, so I can't tell you how that is done, but this seems to be what you're looking for.

If neither of these leads you to the problem, comment out the whole if (NEW.type = 'PRE') THEN block and do a simple modification, such as setting NEW.type to 'debug'. After ensuring that the trigger does in fact execute, retest successively with more of the real code added back in until you isolate the problem.

Also, wrt Marcos comment, I would be surprised if the script didn't auto-commit upon successful completion. Indeed, I would make this statement about any script/language.

For anyone finding this question in the future - I am guessing the solution is setting mysql autocommit to true. after opening the connection query the following:

SET autocommit = 1

Shell commands only run in the console, not on the actual server. You can use UDF or polling to accomplish what you need.