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

Question

I had an inconsistent error with "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 problem is that the following error is sometimes returned:

Unknown column "MINUTE_IN" in "NEW"

If this error occurs, it will not stop until I close and re-create the trigger using the same instruction. When I do this, the error stops, and some inserts will occur without problems until the same problem returns, apparently for no reason.

Can someone help me on this? Thanks in advance.

Answer 1

In MySQL 5.6.17 there is an error in triggers when you refer to another table that has been truncated: http://bugs.mysql.com/bug.php?id=72446

Perhaps the reason? I am trying to get around this by changing my TRUNCATE table name to DELETE FROM table_name (where table_name is the table where my trigger is launched and captures data. That is: if you have a trigger in table A that references table B, An error may occur when you crop table B).

In addition, an error report indicates that this has been fixed in MySQL 5.6.19, but I have not tested it yet.

Answer 2

If you cannot fix this, you may need to create a cron job that runs the stored procedure every few minutes. This is a hack, but it can help you identify the problem. Can a database be used? You checked the OS to see what happens when this error occurs. You should also check your log files for errors. I have seen errors that occur until they begin to interfere with the database.

Another possibility is a table that you insert into a large table with many indexes? If the delay is caused by the insertion, this can cause a bottleneck in the triggers.