Bug #11491 Misleading error message if not NULL column set to NULL, SQL mode TRADITIONAL

Description:
Contrary to what had been said after I reported the same bug a few weeks ago, it seems that NULL constraint violations are still not handled properly in triggers.

When in a BEFORE UPDATE (or INSERT) trigger you set the new value of a NOT NULL column to NULL, it takes a fake value ('' or 0 for an integer) instead of generating a constraint violation error.

This can lead to incoherence in the database integrity (for example if the column the trigger tries to set to NULL is a key).

How to repeat:
>CREATE TABLE foo (
id INT PRIMARY KEY,
bar INT)

>INSERT INTO foo (id, bar) VALUES (1, 5)

>CREATE TRIGGER tgr_bu_foo BEFORE UPDATE ON foo FOR EACH ROW
BEGIN
   IF new.bar<0 THEN
      SET new.id=NULL;
   END IF;
END;

>UPDATE foo SET bar=-8 WHERE id=1;
1 records affected

>SELECT * FROM foo;

id     bar
--     ----
0      -8

Guillaume,

please test with an InnoDB type table, and set the SQL mode to 'TRADITIONAL'.

For MyISAM type tables, handling a constraint violation is problematic, since there is no rollback.

Regards,

Heikki

InnoDB is a little more verbose but handles the error in the same way ("Column set to default value, NULL supplied to NOT NULL column 'id' at row 1).

My sql_mode variable contains TRADITIONAL but I couldn't get it to contain only TRADITIONAL :

mysql> SET GLOBAL sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------
------------------------------------------------+
| @@sql_mode
                                                |
+-------------------------------------------------------------------------------
------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_D
IVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)

Anyway, I'm reporting this bug because the behaviour INSIDE a trigger is not the same as OUTSIDE. If you try to set a NOT NULL column to NULL (even with a MyISAM table) outside a trigger, an error will be generated, forbidding the operation.

Guillaume,

you are right, it should not set a not-NULL column to NULL.

I am raising the priority of this bug to P1 since it is essential that a database obeys integrity constraints.

Thank you,

Heikki

Oops, I misread the description. The bug sets the column to a fake default value (not to NULL). Anyway, in the TRADITIONAL SQL mode at least, it should not do that but roll back the SQL statement. I am lowering the priority to P2.

Thank you,

Heikki

Guillaume,

both MyISAM and InnoDB do set the column to a fake default value also in a normal update. This behavior is not specific to triggers:

mysql> CREATE TABLE `foo2` (
    ->   `id` int(11) NOT NULL,
    ->   `bar` int(11) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) type = myisam;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into foo2 values (1, 5);
Query OK, 1 row affected (0.01 sec)

mysql> update foo2 set id = NULL;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from foo2;
+----+------+
| id | bar  |
+----+------+
|  0 |    5 |
+----+------+
1 row in set (0.00 sec)

mysql>

I tested InnoDB in the SQL mode 'TRADITIONAL'. The error message is misleading, but MySQL works correctly: it does not change the column value.

Since MyISAM does not have a rollback, it must set the column value to a fake default value. This behavior also applies to transactional table types in the default SQL mode. In the 'TRADITIONAL' mode, transactional tables will roll back the SQL statement.

I recommend using InnoDB in the 'TRADITIONAL' mode because then the behavior is like in most other database brands: no fake default values are used.

Thus, the only bug here is the misleading error message in the 'TRADITIONAL' mode. The error message should be something like: "NOT NULL constraint violation".

I am updating the synopsis, and lowering the priority to P3.

Regards,

Heikki

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY,
    -> bar INT)
    -> type = innodb;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> INSERT INTO foo (id, bar) VALUES (1, 5)
    -> ;
Query OK, 1 row affected (0.03 sec)

mysql> delimiter //
mysql> CREATE TRIGGER tgr_bu_foo BEFORE UPDATE ON foo FOR EACH ROW
    -> BEGIN
    ->    IF new.bar<0 THEN
    ->       SET new.id=NULL;
    ->    END IF;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE foo SET bar=-8 WHERE id=1;
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum
n 'id' at row 1
mysql> select * from foo;
+----+------+
| id | bar  |
+----+------+
|  1 |    5 |
+----+------+
1 row in set (0.00 sec)

You're right. I mixed up the behaviour of UPDATE with INSERT regarding NOT NULL columns (in a BEFORE INSERT trigger, an error is generated and the insertion fails when you try to set a NOT NULL column to NULL).

I'm still wondering how come I couldn't set sql_mode to only TRADITIONAL with the command : SET GLOBAL sql_mode='TRADITIONAL'...

Anyway, thanks for the answers.

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/internals/31494

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/internals/32038

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/internals/32902

Fixed in 5.1.4-alpha

Noted in 5.1.4 changelog.

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/27675

ChangeSet@1.2505, 2007-05-30 17:04:04+05:00, gluh@mysql.com +19 -0
  backport of Bug#11491 Misleading error message if not NULL column set to NULL, SQL mode TRADITIONAL

Pushed into 5.1.20-beta

Pushed into 5.0.44