Bug #47159 Triggers on not null fields fails in Win 5.1.38, work fine on Mac 5.1.38

• Submitted: 6 Sep 2009 22:18
• Modified: 7 Oct 2009 4:04
• Reporter: Jan Zawadzki
• Status: No Feedback
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.1.38
• OS: Windows (Server 2003)
• CPU Architecture: Any
• Tags: NOT NULL, trigger

[6 Sep 2009 22:18] Jan Zawadzki

Description:
The following sequence works fine on Mac 5.1.38 binary build:

create table th (s1 int, s2 int not null); 
create trigger th_bi before insert on th for each row set new.s2 = 1; 
insert into th (s1) values (99); 
select * from th; 

results are 99,1

Running the same on Windows binary 5.1.38 build results in 

ERROR 1364 (HY000): Field 's2' doesn't have a default value

How to repeat:
install binary

create new schema

execute:

create table th (s1 int, s2 int not null); 
create trigger th_bi before insert on th for each row set new.s2 = 1; 
insert into th (s1) values (99); 
select * from th;

Suggested fix:
Seems like platform bug???

[7 Sep 2009 4:04] Valeriy Kravchuk

Actually, the result depends on storage engine used by default and sql_mode setting use by default. You either get a warning (MyISAM or empty sql_mode on Mac) and trigger has a chance to work, or error (InnoDB + strict SQL mode on Windows), with zero chances to work. In any case, NOT NULL constraint is checked BEFORE trigger fires.

[7 Oct 2009 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".