Unknown column 'subtest_id' in 'NEW'

[Prashant Tekriwal]

Hi,

I have the following trigger in my mysql database:

-------------------------------------------------------------

CREATE TRIGGER set_subtest_id BEFORE INSERT ON subtest
FOR EACH ROW
BEGIN
UPDATE seq SET id = id + 1;
SET new.subtest_id = (SELECT id FROM seq); <-- Failure is happening here
SET new.time_begin = UNIX_TIMESTAMP(NOW());
IF new.visible_id = -1 AND new.hidden_results = 1 THEN
SET new.visible_id = new.subtest_id;
ELSEIF new.visible_id = -1 THEN
SET new.visible_id = (SELECT visible_id FROM subtest WHERE subtest_id = new.subtest_parent_id);
END IF;
INSERT INTO contains VALUES (new.subtest_id, new.subtest_id);
END; |

-----------------------------------------------------------------------

This above trigger has been working fine for over a year and now suddenly I have started seeing intermittent issue with this trigger.

I assume the error is happening at the point I have marked above ( <-- ).

I sometimes see the following error in my logs:

DBD::mysql::db do failed: Unknown column 'subtest_id' in 'NEW'

This issue is not easily reproducible and I have seen this error only several times till now.

The same set of code is working fine sometimes and sometimes it fails with above error.

I am using Mysql Server 5.6.

Can anybody help me finding out what is the issue?

Let me know if any other information is needed.

Thanks