8

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.

barras
  • 83
  • 1
  • 1
  • 6
  • why do you have 3 extra semicolons in your IF condition? and other semicolons in other places too, I would've thought it would complain about syntax error. – Tin Tran Jan 23 '14 at 19:44
  • Oops...That was actually a copy/paste mistake when reverting some past attempts to see if the issue wouldn't occur anymore. I edited my original post to correct it. Sorry... – barras Jan 23 '14 at 19:52
  • 3
    +1 I would like to know what the answer is too as i think you've found a very strange bug. – Tin Tran Jan 24 '14 at 04:25
  • I could not reproduce the problem, the error is not generated. What version of MySQL are you using? [SQL Fiddle Test](http://sqlfiddle.com/#!2/9835e/1) – wchiquito Jan 24 '14 at 09:58
  • 1
    I'm using 5.6.15 on a CentOS 64bit machine. I'm also starting to think it may be a MySQL bug. I cannot always replicate the error as well...this behavior seems quite random... – barras Jan 24 '14 at 11:33
  • minute_in is set to 'null : yes', maybe if you insert data without putting a value for minute in the problem occur, could you test for this ? – dagfr Jan 28 '14 at 07:27
  • Checked it but no luck. Nevertheless the insert statement when the error occurs has the value '0'. Yesterday I tried setting the values I need to variables before setting them to MINUTE_IN and SECOND_IN using those variables and so far, so good... Never had a full day without the error occurring, so I may have found a workaround for this issue. Going to make a few more tests to make sure if this solved the problem and if it did, I'll be sure to update this post. – barras Jan 28 '14 at 12:40
  • Just to give you the good news, the problem got solved by declaring two variables inside the trigger and setting them with the expression I was using to set MINUTE_IN and SECOND_IN and after that setting MINUTE_IN and SECOND_IN to those variables. – barras Jan 31 '14 at 16:30

2 Answers2

2

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.

Ward W
  • 640
  • 6
  • 14
1

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.

Clark Vera
  • 183
  • 1
  • 7