MySQL After Update Trigger apparently not running

MySQLmysql-5.1trigger

I have the following After Update trigger on a MySQL table:

CREATE 
    DEFINER = 'root'@'localhost'
TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD
    AFTER UPDATE
    ON phpbb_rathermenacing.phpbb_bbdkp_memberlist
    FOR EACH ROW
BEGIN
    IF NEW.member_confirmed = 1 AND 
        NEW.member_rank_id <> OLD.member_rank_id AND 
        ((select min(m.member_rank_id) 
            from phpbb_bbdkp_memberlist m 
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                  m.member_guild_id = NEW.member_guild_id AND 
                  m.member_id != NEW.member_id) >= new.member_rank_id OR 
        (select count(m.member_rank_id) 
           from phpbb_bbdkp_memberlist m 
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                m.member_guild_id = NEW.member_guild_id AND 
                m.member_id != NEW.member_id) = 0)
        THEN
            BEGIN
                IF (SELECT count(*) 
                      FROM phpbb_user_group ug 
                     WHERE ug.group_id = 
                                    (SELECT mr.phpbb_group_id 
                                       FROM phpbb_bbdkp_member_ranks mr 
                                      WHERE mr.guild_id = OLD.member_guild_id AND
                                            mr.rank_id = OLD.member_rank_id) AND
                    ug.group_leader = 0 ) > 0 
                THEN
                    UPDATE phpbb_user_group ug 
                        SET ug.group_id = (SELECT mr.phpbb_group_id 
                                             FROM phpbb_bbdkp_member_ranks mr 
                                            WHERE mr.guild_id = NEW.member_guild_id AND
                                                  mr.rank_id = NEW.member_rank_id),
                            ug.user_pending = 0
                        WHERE ug.user_id = NEW.phpbb_user_id AND 
                              ug.group_id = (SELECT mr.phpbb_group_id 
                                               FROM phpbb_bbdkp_member_ranks mr 
                                              WHERE mr.guild_id = OLD.member_guild_id AND
                                                    mr.rank_id = OLD.member_rank_id);
                ELSE
                    IF(SELECT count(*) 
                         FROM phpbb_user_group ug 
                        WHERE ug.group_id = (SELECT mr.phpbb_group_id 
                                               FROM phpbb_bbdkp_member_ranks mr 
                                              WHERE mr.guild_id = NEW.member_guild_id AND 
                                                    mr.rank_id = NEW.member_rank_id)) = 0 
                    THEN
                        INSERT INTO phpbb_user_group (group_id, user_id, group_leader, user_pending) 
                               VALUES ((SELECT mr.phpbb_group_id 
                                          FROM phpbb_bbdkp_member_ranks mr 
                                         WHERE mr.guild_id = NEW.member_guild_id AND
                                               mr.rank_id = NEW.member_rank_id),
                                      m.phpbb_user_id, 0, 0);
                    END IF;
                END IF;
            END;
    END IF;
END

Basically, the trigger is checking to see if one type of group that the user is associated with has been changed, and in the event that it has been changed, it makes an alteration to set a reference for another type of group.

The problem I'm having is that while the trigger saves perfectly fine, nothing seems to happen when I try doing updates. I can change the rank_id for a memberlist row, but it just updates the row. I've even tried debugging with dbForge Studio and while it will stop on the UPDATE query I use to actually make the update, stepping in to the UPDATE method does not appear to hit the trigger.

What possibilities could be causing the debugging break points to never be hit and no apparent behavior from the trigger to be seen?

Best Answer

My initial guess would be based on the trigger's flow. Why ?

According to the Book

MySQL Stored Procedure Programming

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.

In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:

        ((select min(m.member_rank_id) 
            from phpbb_bbdkp_memberlist m 
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                  m.member_guild_id = NEW.member_guild_id AND 
                  m.member_id != NEW.member_id) >= new.member_rank_id OR 
        (select count(m.member_rank_id) 
           from phpbb_bbdkp_memberlist m 
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                m.member_guild_id = NEW.member_guild_id AND 
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.

I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Mysql – UPDATE waits for lock too long after SELECT in MySQL (Innodb)

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc.

How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running?

My points are: (a) The benchmark is stressing the limits, not looking for reality; (b) Oracle has made great strides recently in handling more connections.

When you go beyond the effective connection limit, latency of queries will suffer terribly. So, don't benchmark beyond that. Furthermore, throttle the users so that not "too many" get to MySQL 'simultaneously'. In older versions, it was so bad that (a) throughput would go down as you add more clients, and (of course) (b) latency would go through the roof. Now, throughput plateaus while latency climbs.

For a single item, be sure to wrap Rolando's SQL in a transaction:

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
COMMIT;

For handling more than one id at the same time, you should sort the ids to help avoid deadlocks. Then do them in a single transaction:

BEGIN;
SELECT * FROM pics WHERE id IN ($id_list) FOR UPDATE;
UPDATE pics SET seen=1 WHERE id IN ($id_list) LIMIT 1;
COMMIT;

You can simplify the code more: Do the

UPDATE...
Check rows_affected; exit if 0
SELECT ...

Since rows_affected is local to the 'session', you can discover whether the UPDATE grabbed the row. Note that there is no need for transactions (as far as this code snippet goes), and autocommit=1 would suffice.

Let me point out another issue with the design: Indexing a flag (seen) has two problems (a) The optimizer is unlikely to use the index, due to low cardinality; and (b) the update has to remove a row from that index and add a new row elsewhere; this is costly.

The logic you described does not seem to need INDEX(seen); does something else need it? If not, DROP that INDEX; that may solve the problem.

Best Answer

SUGGESTIONS

Related Solutions

MySQL using trigger instead of check

Mysql – UPDATE waits for lock too long after SELECT in MySQL (Innodb)

Related Question