Bug #6295 Triggers are not processed for NOT NULL columns
Submitted: 27 Oct 2004 23:30 Modified: 22 Jan 2013 13:11
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0,5.1 OS:Linux (any OS)
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R3 (Medium) / E4 (High)

[27 Oct 2004 23:30] Peter Gulutzan
Description:
I have a column which is defined as NOT NULL. I insert a NULL value. But I have a 
BEFORE trigger which sets the column value to something which is not NULL. This change 
should happen before the check for NOT NULL violation. But I get an error message 
"Column ... cannot be null". 

How to repeat:
mysql> create table th (s1 int not null); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create trigger th_bi before insert on th for each row set new.s1 = 1; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into th values (null); 
ERROR 1048 (23000): Column 's1' cannot be null
[28 Jul 2005 23:12] Jim Winstead
An additional case to consider: an invalid field value with traditional sql_mode

set sql_mode='traditional';
create table t1 (dt datetime);
create trigger t1_bi before insert on t1 for each row set new.dt = '2005-05-01';
insert into t1 values ('0000-00-00');
select * from t1;
drop trigger t1_bi;
drop table t1;
[18 Mar 2007 7:09] David Nichols
In general, MySQL apparently runs "before insert" triggers after it evaluates the data to be inserted against table/column constraints.

This is making it difficult for me to port my application to MySQL.  Oracle and PostgtreSQL (at least that I know of) run "before insert" triggers first, and then check constraints.

I think it's an important bug to be fixed, because it can make life very inconvenient in the following circumstances:
*) porting applications from oracle or PostgreSQL (maybe others?)
*) if you want to change your data model without affecting code accessing the database - by checking constraints before "before insert" triggers are fired means that you cannot abstract some changes from application code - meaning that some changes can be more intrusive/time consuming/expensive, etc

For me, this weakens the power of triggers considerably (also along with the limitation that triggers are not fired on secondary tables) and makes my porting job that much more difficult.

I hope that in my case I can fix this by adding default values to the affected columns (in my case the problem is caused by the fact that not-null columns are not included in the insert queries because they are set by triggers - these columns are also foreign keys into other tables), but I can imagine other scenarios where the fix would not be so easy.

I just wanted to express my opinion that it is a compatibility issue; it can be a significant problem, and, in my opinion, because it weakens the power of MySQL triggers and can make porting from Oracle and PostgreSQL more difficult, it should be addressed in a future release of MySQL.

thanks,
David
[30 Apr 2008 10:07] Olivier GUENARD
This bug still exists in 5.1.23 Does anybody have somme news?
[30 Apr 2008 12:55] Olivier GUENARD
There is some solutions to avoid this problem, but none for triggers having to 
set NOT NULL field.

- set SQL_MODE='' does not work with triggers before b.e SET NEW.FIELD=Value; and it is dangerous to configure server with this option (no more integrity).
- default value does not work when you want to use a GEOMETRY FIELD.

In my case, i need a trigger to set a GEOMETRY field whith x,y values given in the insert.
The field must have a spatial index so must be not null.

The code i have to write is:

DELIMITER//
DROP TRIGGER IF EXISTS MYTRIG//
CREATE TRIGGER MYTRIG BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN
SET @@sql_mode='';
SET NEW.geom=GeomFromText(CONCAT('POINT(',NEW.x,' ',NEW.y,')'));
END//

Any solution ???
Or good news : a new fix for this ugly problem (since 2004)???
[16 Jul 2008 12:48] Matthew Hall
Just came up against this bug using a POINT type column with SPATIAL INDEX applied (as above). The column can not be NULL due to the index limitation.

This seems like a big problem to me, perhaps the 'Non-critical' severity should be elevated.

Any idea if this is going to be fixed in the near future? Been open for around 4 years now!

Simple test case:

CREATE TABLE `test` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`Title` VARCHAR( 50 ) NOT NULL,
`Val1` INT NOT NULL DEFAULT '5',
`Val2` INT NOT NULL ,
PRIMARY KEY ( `ID` )
) ENGINE = MYISAM ;

CREATE TRIGGER Val2_Insert BEFORE INSERT ON test
  FOR EACH ROW SET NEW.Val2 = (NEW.Val1 + 10);

INSERT INTO `test`
(`Title`)
VALUES
('This is an inserted row');
[26 Sep 2008 8:52] Susanne Ebrecht
Bug #39660 is a duplicate of this bug here.

Please look into bug #39660 for getting a test case.
[26 Sep 2008 8:56] Susanne Ebrecht
Copy the test case here:

CREATE TABLE t(i INTEGER NOT NULL);

DELIMITER §

CREATE TRIGGER tr_null BEFORE INSERT ON t
FOR EACH ROW BEGIN
IF NEW.i IS NULL THEN
SET NEW.i = 0;
END IF;
END§

DELIMITER ;

INSERT INTO t(i) VALUES(NULL);
[2 Mar 2009 7:18] Susanne Ebrecht
Bug #43279 is set as duplicate of this bug here
[2 Jan 2010 20:52] Robby Dermody
bump

This bug is majorly impacting the usefulness of triggers for us -- e.g. we have to make a bunch of fields which should be NOT NULL into NULLable fields because of it. 5 years and no fix?
[5 May 2010 10:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107478

3170 Jon Olav Hauglid	2010-05-05
      Bug#6295 Triggers are not processed for NOT NULL columns
      
      *** This is a preliminary version of the patch. ***
      
      The problem was that BEFORE INSERT/UPDATE triggers where not executed
      until after NOT NULL had been checked. This meant that inserting NULL
      into a NOT NULL column would fail even if a BEFORE INSERT trigger
      existed that changed NULL to a valid value.
      
      This patch fixes the problem by changing the code so BEFORE INSERT/
      UPDATE triggers are run before NOT NULL is checked.
      
      For this to work, the patch also makes it so that NOT NULL Fields
      temporarily can store NULL. Before, memory would not be allocated
      for storing the presence of NULL in NOT NULL Fields.
      
      Test case added to trigger.test.
     @ mysql-test/r/trigger.result
        Added test coverage for Bug#6295.
     @ mysql-test/t/trigger.test
        Added test coverage for Bug#6295.
     @ sql/field.cc
        Renamed maybe_null to is_nullable.
        Since NOT NULL fields now must be able to store NULL temporarily,
        we can no longer use null_ptr to check for NOT NULL. Changed Field
        constructors to have nullability as an explicit parameter.
        NOT NULL fields can now temporarily be made to store NULL using
        Field::force_set_null().
        Added Field::check_constraints() to check if the current record
        meets the constraints (NOT NULL) of the given Field.
     @ sql/field.h
        Renamed maybe_null to is_nullable.
        Since NOT NULL fields now must be able to store NULL temporarily,
        we can no longer use null_ptr to check for NOT NULL. Changed Field
        constructors to have nullability as an explicit parameter.
        NOT NULL fields can now temporarily be made to store NULL using
        Field::force_set_null().
        Added Field::check_constraints() to check if the current record
        meets the constraints (NOT NULL) of the given Field.
     @ sql/field_conv.cc
        Renamed maybe_null to is_nullable.
        Removed checking of NOT NULL from set_field_to_null() and
        set_field_to_null_with_conversions(). This is now done later
        after triggers have been executed.
        NOT NULL fields can now temporarily be made to store NULL using
        Field::force_set_null()
     @ sql/filesort.cc
        Renamed maybe_null to is_nullable.
     @ sql/ha_ndbcluster_cond.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/item.cc
        Renamed maybe_null to is_nullable.
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/item.h
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/item_cmpfunc.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/item_func.h
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/item_sum.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/log_event.cc
        Renamed maybe_null to is_nullable.
     @ sql/log_event_old.cc
        Renamed maybe_null to is_nullable.
     @ sql/opt_range.cc
        Renamed maybe_null to is_nullable.
     @ sql/opt_sum.cc
        Renamed maybe_null to is_nullable.
     @ sql/partition_info.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/rpl_record.cc
        Renamed maybe_null to is_nullable.
     @ sql/sp_head.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_base.cc
        Use l-value Fields for updates. NOTE: Currently not needed for the patch.
        Moved checking of constraints (NOT NULL) to separate functions.
        Refactored so that triggers are executed before constraints are checked.
     @ sql/sql_base.h
        Added function to check if constraints are met by the current record.
     @ sql/sql_handler.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_insert.cc
        Use l-value Fields for updates. NOTE: Currently not needed for the patch.
        Allocate extra memory for temporary storage of null_bits for NOT
        NULL fields.
        Initialize l-value Fields. NOTE: Currently not needed for the patch.
     @ sql/sql_load.cc
        Renamed maybe_null to is_nullable.
        Use l-value Fields for updates. NOTE: Currently not needed for the patch.
     @ sql/sql_partition.cc
        Renamed maybe_null to is_nullable.
     @ sql/sql_select.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_select.h
        Renamed maybe_null to is_nullable.
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_show.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ sql/sql_table.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
        Use l-value Fields for updates. NOTE: Currently not needed for the patch.
     @ sql/sql_union.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_update.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_view.cc
        Use l-value Fields for updates. NOTE: Currently not needed for the patch.
     @ sql/table.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
        Allocate extra memory for temporary storage of null_bits for NOT
        NULL fields.
        Initialize l-value Fields. NOTE: Currently not needed for the patch.
     @ sql/table.h
        Added separate Fields for l-values. 
        NOTE: Currently not needed for the patch.
     @ sql/unireg.cc
        Changed NULL assignment to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/csv/ha_tina.cc
        Renamed maybe_null to is_nullable.
     @ storage/federated/ha_federated.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/heap/ha_heap.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/ibmdb2i/ha_ibmdb2i.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/innobase/handler/ha_innodb.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/myisam/ha_myisam.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
[1 Jun 2010 13:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109762

3221 Jon Olav Hauglid	2010-06-01
      Bug#6295 Triggers are not processed for NOT NULL columns
      
      *** This is a preliminary version of the patch. ***
      
      The problem was that BEFORE INSERT/UPDATE triggers where not executed
      until after NOT NULL had been checked. This meant that inserting NULL
      into a NOT NULL column would fail even if a BEFORE INSERT trigger
      existed that changed NULL to a valid value.
      
      This patch fixes the problem by changing the code so BEFORE INSERT/
      UPDATE triggers are run before NOT NULL is checked.
      
      For this to work, the patch also makes it so that NOT NULL Fields
      temporarily can store NULL. Before, memory would not be allocated
      for storing the presence of NULL in NOT NULL Fields.
      
      Test case added to trigger.test.
     @ include/mysql_com.h
        Added flag for fields made temporarily nullable for trigger processing.
     @ mysql-test/r/trigger.result
        Updated result file - multi-value INSERT will give warning, not error.
        Added test coverage for Bug#6295.
     @ mysql-test/t/trigger.test
        Added test coverage for Bug#6295.
     @ sql/field.cc
        Renamed maybe_null to is_nullable.
        Since NOT NULL fields now must be able to store NULL temporarily,
        we can no longer use null_ptr to check for NOT NULL. Changed Field
        constructors to have nullability as an explicit parameter.
        Added Field::check_constraints() to check if the current record
        meets the constraints (NOT NULL) of the given Field.
     @ sql/field.h
        Renamed maybe_null to is_nullable.
        Since NOT NULL fields now must be able to store NULL temporarily,
        we can no longer use null_ptr to check for NOT NULL. Changed Field
        constructors to have nullability as an explicit parameter.
        NOT NULL fields can now temporarily be made nullable using
        Field::set_temporary_nullable(bool).
        Added Field::check_constraints() to check if the current record
        meets the constraints (NOT NULL) of the given Field.
     @ sql/field_conv.cc
        Renamed maybe_null to is_nullable.
        Removed checking of NOT NULL from set_field_to_null() and
        set_field_to_null_with_conversions(). This is now done later
        after triggers have been executed.
        NOT NULL fields are now temporarily be made nullable using
        Field::set_temporary_nullable()
     @ sql/filesort.cc
        Renamed maybe_null to is_nullable.
     @ sql/ha_ndbcluster_cond.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/item.cc
        Renamed maybe_null to is_nullable.
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
        NEW.columns inside triggers are made temporarily nullable.
     @ sql/item.h
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/item_cmpfunc.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/item_func.h
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/item_sum.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/log_event.cc
        Renamed maybe_null to is_nullable.
     @ sql/log_event_old.cc
        Renamed maybe_null to is_nullable.
     @ sql/opt_range.cc
        Renamed maybe_null to is_nullable.
     @ sql/opt_sum.cc
        Renamed maybe_null to is_nullable.
     @ sql/partition_info.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/rpl_record.cc
        Renamed maybe_null to is_nullable.
     @ sql/sp_head.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/sql_base.cc
        Moved checking of constraints (NOT NULL) to separate functions.
        Refactored so that triggers are executed before constraints are checked.
        Use TABLE::nullable_field for updates.
     @ sql/sql_base.h
        Added function to check if constraints are met by the current record.
     @ sql/sql_handler.cc
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/sql_insert.cc
        Allocate extra memory for temporary storage of null_bits for NOT
        NULL fields.
        Initialize TABLE_SHARE::nullable_field.
        Use TABLE::nullable_field for updates.
     @ sql/sql_load.cc
        Renamed maybe_null to is_nullable.
        Use TABLE::nullable_field for updates.
     @ sql/sql_partition.cc
        Renamed maybe_null to is_nullable.
     @ sql/sql_select.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/sql_select.h
        Renamed maybe_null to is_nullable.
        Constraints (NOT NULL) must now be checked separately after
        save_in_field().
     @ sql/sql_show.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ sql/sql_table.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ sql/sql_trigger.cc
        Use TABLE::nullable_field for NEW.columns inside triggers.
        Check constrains (NOT NULL) on NEW.columns after the
        trigger has ben executed.
     @ sql/sql_union.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_update.cc
        Constraints (NOT NULL) must now be checked separately after
        fill_record.
     @ sql/sql_view.cc
        Use TABLE::nullable_field for updates.
     @ sql/table.cc
        Renamed maybe_null to is_nullable.
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
        Allocate extra memory for temporary storage of null_bits for NOT
        NULL fields.
        Initialize TABLE_SHARE::nullable_field.
     @ sql/table.h
        Added TABLE::nullable_field to be used for updates.
     @ sql/unireg.cc
        Changed NULL assignment to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/csv/ha_tina.cc
        Renamed maybe_null to is_nullable.
     @ storage/federated/ha_federated.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/heap/ha_heap.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/ibmdb2i/ha_ibmdb2i.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/innobase/handler/ha_innodb.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
     @ storage/myisam/ha_myisam.cc
        Changed NOT NULL tests to use a Field function rather than 
        directly accessing Field member variables.
[23 Jul 2010 7:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114204

3319 Jon Olav Hauglid	2010-07-23
      A pre-requisite patch for Bug#6295.
      
      This patch prepares the way for fixing
      "Triggers are not processed for NOT NULL columns"
      by implementing the following changes:
      
      Since NOT NULL fields now must be able to store NULL temporarily,
      we can no longer use null_ptr (i.e. the ability to store NULL)
      to check for nullability. Nullability is now checked using the
      NOT_NULL_FLAG rather than using null_ptr directly.
      
      Changed Field constructors to have nullability as an explicit
      parameter since we cannot rely on checking null_ptr != NULL.
      
      Changed nullability checks to use Field functions rather than 
      directly accessing Field member variables. This is done to make
      it easier to split nullability from the ability to store NULL.
      
      Renamed maybe_null to is_nullable.
[25 Aug 2010 14:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116754

3246 Jon Olav Hauglid	2010-08-25
      A pre-requisite patch for Bug#6295.
      
      This patch prepares the way for fixing
      "Triggers are not processed for NOT NULL columns"
      by implementing the following changes:
      
      Since NOT NULL fields now must be able to store NULL temporarily,
      we can no longer use null_ptr (i.e. the ability to store NULL)
      to check for nullability. Nullability is now checked using the
      NOT_NULL_FLAG rather than using null_ptr directly.
      
      Changed Field constructors to have nullability as an explicit
      parameter since we cannot rely on checking null_ptr != NULL.
      
      Changed nullability checks to use Field functions rather than 
      directly accessing Field member variables. This is done to make
      it easier to split nullability from the ability to store NULL.
      
      Renamed maybe_null to is_nullable.
[12 Nov 2010 15:21] Russ Chinoy
This bug continues to exist in 5.1.50, and is really a pain for all of the reasons stated above. How about finally fixing it!
[13 Dec 2010 17:38] Pavel Alexeev
I can confirm this still reproducible. Please see http://netbeans.org/bugzilla/show_bug.cgi?id=190731
[30 May 2012 10:09] C.A. Pelle
Still exists in 5.5.23
Holy cow... this one takes a long time to be fixed.
[8 Oct 2012 13:33] Juergen Thomas
Still exists in 5.5.27. This behavior prevents to migrate from Firebird, too. I like to waite 8 years more...
[21 Jan 2013 5:13] Jeff Rule
I can also confim that this still exists in 5.5.27.  

It also has one other bad side effect that is specific to timestamps.   If you try to modify a timestamp in a not null column with a before insert trigger 'SET NEW.my_timestamp_column = <some_value>',  after the trigger fires mysql overrides the value again and replaces it with current_timestamp.  This behavior is only exhibited when the timestamp column is not referenced in the insert statement at all.    I understand that the first timestamp column that is not specified in the values list is to to be replaced with the current_timestamp.  But It only makes sense that this happens before any triggers are fired.  It makes triggers for timestamps behave differently from all other datatypes and dis-allows you from overriding the calling application as you should be able to do.

As a side note, timestamps in not null columns unlike all the other data types can be provided with a null value during insert and you can successfully override the value in a before insert trigger.   The overriding value is correctly written into the database.  This behavior is different from all the others and basically the correct one.

I can't believe it is 2013 and this bug was identified in 2004 and we are still waiting for a fix.   It is seriously time to start looking for a different DBMS engine.

I wrote a test script that runs a test suite against and most of the basic data types if you are interested.  It is written in bash and mysql.  i will attach it as bugs.sh
[21 Jan 2013 5:15] Jeff Rule
Script to exhibit a number of problems with triggers

Attachment: bug.sh (application/octet-stream, text), 6.46 KiB.

[22 Jan 2013 13:11] Paul Dubois
Noted in 5.7.1 changelog.

If a column is declared as NOT NULL, it is not permitted to insert
NULL into the column or update it to NULL. However, this constraint
was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE
trigger) that set the column to a non-NULL value. Now the constraint
is checked at the end of the statement, per the SQL standard.
[14 Sep 2014 15:39] Massimo Di Primio
Unbelivable, problem seems still there!
Here's what I've tried:

$ mysql -u root -p test_triggers
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1600
Server version: 5.5.39-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t_foo;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t_foo (col_1 varchar(20) not null, col_2 varchar(20) not null default '') engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;;
mysql> create trigger t_foo_bi before insert on t_foo for each row 
    -> BEGIN
    ->    SET new.col_2 = 'col_2_MyValue';
    ->    SET new.col_1 = 'col_1_MyValue';
    -> END ;;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> insert into t_foo (col_1) values ('MyValue1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_foo (col_1) values (NULL);
ERROR 1048 (23000): Column 'col_1' cannot be null
mysql> select * from t_foo;
+---------------+---------------+
| col_1         | col_2         |
+---------------+---------------+
| col_1_MyValue | col_2_MyValue |
+---------------+---------------+
1 row in set (0.00 sec)

mysql>
[15 Sep 2014 13:06] Ståle Deraas
Hi Massimo,

The bugreport states that this was fixed in 5.7.1.

I see two problems with your repro:

1. It seems you have repro'ed this on MariaDB, and not MySQL.
2. If it had been a MySQL 5.5.39, you would not see the fix in this version, as it is fixed in version 5.7.1 .
[15 Sep 2014 16:39] Massimo Di Primio
@Ståle Deraas
As you can notice only the client is MariaDB, while the server is:
"Server version: 5.5.39-log MySQL Community Server (GPL) by Remi"
Nonetheless I ran the same test in your favorite environment as follows:

$ mysql -h localhost -u root -p test_triggers
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2131
Server version: 5.5.39-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t_foo;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t_foo (col_1 varchar(20) not null, col_2 varchar(20) not null default '') engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> DELIMITER ;;
mysql> create trigger t_foo_bi before insert on t_foo for each row
    -> BEGIN
    ->    SET new.col_2 = 'col_2_MyValue';
    ->    SET new.col_1 = 'col_1_MyValue';
    -> END ;;
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> insert into t_foo (col_1) values ('MyValue1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_foo (col_1) values (NULL);
ERROR 1048 (23000): Column 'col_1' cannot be null
mysql>

Hope this is better.
FTI mysql Server is on: CentOS release 6.5 (Final)
[16 Sep 2014 11:35] Ståle Deraas
Hi again Massimo,

It seems your server version is still 5.5.39.

"Server version: 5.5.39-log MySQL Community Server (GPL) by Remi"

As mentioned above, the fix was included in 5.7.1, so the behaviour in 5.5 is as such unchanged,
[24 Feb 2016 20:56] Diego Acosta
Is somebody going to fix this issue?? I don't know why the status of this ticket is 'Closed'... It is still happenning in the 5.6 version.
[24 Feb 2016 22:26] Diego Acosta
Still happening on 5.7.11-log MySQL Community Server (GPL)
[25 Feb 2016 12:38] Ståle Deraas
Hi Diego,

This is what we get when running the testcase, which is expected after the fix in 5.7.1.

mysql> create table th (s1 int not null);
Query OK, 0 rows affected (0,00 sec)

mysql> create trigger th_bi before insert on th for each row set new.s1 = 1;
Query OK, 0 rows affected (0,01 sec)

mysql> insert into th values (null); 
Query OK, 1 row affected (0,00 sec)

mysql> select VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.7.12-debug-log |
+------------------+
1 row in set (0,00 sec)

If you see other issues, please file a bug and attach a testcase.
[27 Apr 2017 14:35] Виталий Кушниренко
Hello!I have the same problem

How fix this bag? There is a solution?
[27 Apr 2017 15:19] Ståle Deraas
The issue is fixed in 5.7 GA releases. What MySQL version do you use?
[27 Apr 2017 15:20] Ståle Deraas
The issue is fixed in 5.7 GA releases. What MySQL version do you use?