MySQL Bugs: #18196: before insert trigger fires too late

Bug #18196	before insert trigger fires too late
Submitted:	13 Mar 2006 16:55	Modified:	2 Jun 2006 5:47
Reporter:	Roland Volkmann	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-nt/5.1BK	OS:	Windows (WinXP/linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
IF you have InnoDB table with field declared "not null", you can't initialize it using a before-insert-trigger.

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table ttt (id integer, info varchar(20) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> create trigger tr_ttt_bi before insert on ttt for each row set new.info := 'Hello';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ttt (id) values (1);
ERROR 1364 (HY000): Field 'info' doesn't have a default value
mysql>

Suggested fix:
check the "not null" condition after the before-triggers

Thank you for the bug report. It happens also with MyISAM engine and the
error instead of a warning is according the sql_mode. However I was not
able for to find a restriction for triggers in our manual mentioning that
behavior:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> create table ttt (id integer, info varchar(20) not null);
Query OK, 0 rows affected (0.56 sec)

mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) default NULL,
  `info` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.23 sec)

mysql> create trigger tr_ttt_bi before insert on ttt for each row set new.info
    -> := 'Hello';
Query OK, 0 rows affected (0.53 sec)

mysql> insert into ttt (id) values (1);
ERROR 1364 (HY000): Field 'info' doesn't have a default value

mysql> set sql_mode="";
Query OK, 0 rows affected (0.23 sec)

mysql> insert into ttt (id) values (1);
Query OK, 1 row affected, 1 warning (0.39 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'info' doesn't have a default value
1 row in set (0.25 sec)

mysql> select * from ttt;
+------+-------+
| id   | info  |
+------+-------+
| 1    | Hello |
+------+-------+
1 row in set (0.25 sec)

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table ttt (id integer, info varchar(20) not null)
    -> engine=MyISAM;
Query OK, 0 rows affected (0.56 sec)

mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) default NULL,
  `info` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.24 sec)

mysql> create trigger tr_ttt_bi before insert on ttt for each row set new.info
    -> := 'Hello';
Query OK, 0 rows affected (0.31 sec)

mysql> insert into ttt (id) values (1);
ERROR 1364 (HY000): Field 'info' doesn't have a default value
mysql>

Bug: http://bugs.mysql.com/bug.php?id=18262 was marked as duplicate
of this one.

Thanks for the report.
It's a known issue and won't be fixed in 5.0.
See #6295: Triggers are not processed for NOT NULL columns