Bug #43279 NOT NULL validation should happen after BEFORE TRIGGER

• Submitted: 28 Feb 2009 19:52
• Modified: 2 Mar 2009 7:18
• Reporter: Ondra Zizka
• Status: Duplicate
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.0.77
• OS: Any
• CPU Architecture: Any
• Tags: before, notnull, trigger, Validation

[28 Feb 2009 19:52] Ondra Zizka

Description:
Sometimes the trigger is designed to convert NULL values to some default value, e.g. computed from other field.

For example, when used in Hibernate, some column doesn't have to be mapped at all, or left as null and annotated as @Generated.

In MySQL, however, this approach forces the column to allow NULLs, because NOT NULL validation happens before the BEFORE trigger. It should be done after, because, afterall, the row values that the trigger code leaves in NEW.* fields is what will be stored to the table.

How to repeat:
CREATE TABLE hashes (
  str VARCHAR(255) NOT NULL,
  hash CHAR(32) NOT NULL
);

CREATE TRIGGER my_trigger BEFORE INSERT ON hashes FOR EACH ROW
  NEW.hash = MD5(NEW.str);

INSERT INTO hashes SET str = 'My string to be hashed';

-- ^^^ Causes validation error for NOT NULL. ---

Suggested fix:
Do NOT NULL validation after the BEFORE INSERT / UPDATE trigger.

[2 Mar 2009 7:18] Susanne Ebrecht

This is a duplicate of bug #6295