MySQL error in Trigger "Unknown column in 'NEW'"

Question

I'm having an inconsistent error with a "before insert trigger" in MySQL and cannot figure out the reason.

I Have a table with the following description:

+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| ROW_ID    | int(11)       | NO   | PRI | 0       |       |
| ID        | int(11)       | NO   | UNI | NULL    |       |
| TITLE     | varchar(32)   | NO   |     | NULL    |       |
| TEXT      | varchar(500)  | NO   |     | NULL    |       |
| URL       | varchar(200)  | YES  |     | NULL    |       |
| MINUTE_IN | int(11)       | YES  |     | NULL    |       |
| SECOND_IN | int(11)       | YES  |     | NULL    |       |
| TVSHOW_ID | int(11)       | NO   | MUL | NULL    |       |
| IMAGE     | varchar(4000) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

And I have a Trigger with the following statement:

delimiter $$
CREATE TRIGGER ACTIVATE_IRT_CONTENT BEFORE INSERT ON WEXTRAS_CONTENT
FOR EACH ROW
BEGIN
    IF (SELECT s.ACTIVE
        from WEXTRAS_TVSHOW s
        where s.id = NEW.tvshow_id) = 1 AND NEW.MINUTE_IN = 0 AND NEW.SECOND_IN = 0
    THEN SET NEW.MINUTE_IN = TIMESTAMPDIFF(MINUTE,(select INIT_TIME from WEXTRAS_TVSHOW s
                                                   where s.id = NEW.tvshow_id
                                                   ),sysdate());
         SET NEW.SECOND_IN = SECOND(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,(select INIT_TIME from WEXTRAS_TVSHOW s
                                                                     where s.id = NEW.tvshow_id
                                                                     ),sysdate())));
    END IF;
END$$

delimiter ;

The issue is that sometimes the following error is returned:

Unknown column 'MINUTE_IN' in 'NEW'

When this error occurs it won't stop until I drop and recreate the trigger with the exact same statement. When I do that, the error stops occurring and several inserts will occur with no problem, until the same issue returns with apparently no reason.

Can anybody help me with this issue? Thanks in advance.

Answer 1

There's a bug in MySQL 5.6.17 on triggers when you reference another table that has been truncated: http://bugs.mysql.com/bug.php?id=72446

Maybe that's the reason? I'm trying to work around this by changing my TRUNCATE table_name to DELETE FROM table_name (where table_name is the table that my trigger goes and grabs data from. I.e.: if you have a trigger on table A that references table B, the bug can occur when you're truncating table B).

Also, the bug report seems to indicate that this is fixed in MySQL 5.6.19, but I haven't tested this yet.