MySQL error in Trigger "Unknown column in 'NEW'"
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.
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. Ie: 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.
If you can't repair this, then you might have to create a cron job that runs a stored procedure every few minutes. This is hack but it may help you identify the problem. Could it be the database usage is maxed out? Did you check the OS to see what is happening when this error occurs. You should also check your log files for an error. I have seen errors build up to the point where they begin interfering with the database.
Another possibility, is the table you are inserting into a large table with many indexes? If there is a delayed caused by the insert it could cause a bottleneck in the triggers.
链接地址: http://www.djcxy.com/p/18708.html