MySQL Bugs: #79266: Adding a trigger to a table changes the behaviour of null detection

Bug #79266	Adding a trigger to a table changes the behaviour of null detection
Submitted:	13 Nov 2015 10:54	Modified:	18 Dec 2015 18:54
Reporter:	Mike Lehan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.9	OS:	Ubuntu (14.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	null, trigger

Description:
An operation which should raise a warning (inserting an implicit NULL into a not null column) raises an error, only if the table has a trigger defined.

Thus the same command in the same mode has a different end-state when a trigger is set.

Using MySQL 5.7.9 from this PPA (https://launchpad.net/~ondrej/+archive/ubuntu/mysql-5.7) on Ubuntu 14.04.3 LTS.

How to repeat:
Run the following in CLI:

select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

create table _test (col_1 varchar(20) not null, col_2 varchar(20) not null) engine=myisam charset=utf8;
Query OK, 0 rows affected (0.00 sec)

show columns from _test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col_1 | varchar(20) | NO   |     | NULL    |       |
| col_2 | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 insert into _test (col_1) values ('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

delimiter $$

create trigger `insert_test` before insert on `_test` for each row begin set NEW.col_1 = concat(NEW.col_1, '+test'); end$$
Query OK, 0 rows affected (0.02 sec)

delimiter ;

insert into _test (col_1) values ('abc');
ERROR 1048 (23000): Column 'col_2' cannot be null

***********************

The "ERROR" in the last command is unexpected.

Running the same series of commands on MySQL 5.6.19 the last command results in:

insert into _test (col_1) values ('abc');
Query OK, 1 row affected, 1 warning (0.01 sec)

Which is expected behaviour.

Suggested fix:
Either it should be stated that any insertion of "NULL" into a "NOT NULL" column is prohibited regardless of `sql_mode` (this would represent a large BC break)

OR

The command should still throw a warning instead of an error regardless as to whether a trigger is defined on the table.

Testing 5.6 behavior:

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.28 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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 5.6 > CREATE DATABASE g;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > USE g
Database changed
mysql 5.6 > select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql 5.6 > create table _test (col_1 varchar(20) not null, col_2 varchar(20) not null) engine=myisam charset=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql 5.6 > show columns from _test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col_1 | varchar(20) | NO   |     | NULL    |       |
| col_2 | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql 5.6 > insert into _test (col_1) values ('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql 5.6 > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col_2' doesn't have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 >
mysql 5.6 > delimiter $$
mysql 5.6 >
mysql 5.6 > create trigger `insert_test` before insert on `_test` for each row begin set NEW.col_1 = concat(NEW.col_1, '+test'); end
Query OK, 0 rows affected (0.13 sec)

mysql 5.6 > delimiter ;
mysql 5.6 >
mysql 5.6 > insert into _test (col_1) values ('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql 5.6 > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col_2' doesn't have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. MySQL 5.1/5.5/5.6 don't handle the warnings as error like 5.7 is doing here trigger "give an error instead of a warning” disregarding the sql_mode. There were bugs and discussion related to triggers i.e: https://bugs.mysql.com/bug.php?id=42910.

:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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 5.7 > select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > set sql_mode = NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql 5.7 > CREATE DATABASE g;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE g
Database changed
mysql 5.7 > create table _test (col_1 varchar(20) not null, col_2 varchar(20) not null) engine=myisam charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > show columns from _test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col_1 | varchar(20) | NO   |     | NULL    |       |
| col_2 | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql 5.7 > insert into _test (col_1) values ('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql 5.7 > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col_2' doesn't have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > delimiter $$
mysql 5.7 > create trigger `insert_test` before insert on `_test` for each row begin set NEW.col_1 = concat(NEW.col_1, '+test'); end$$
Query OK, 0 rows affected (0.02 sec)

mysql 5.7 > delimiter ;
mysql 5.7 > insert into _test (col_1) values ('abc');
ERROR 1048 (23000): Column 'col_2' cannot be null

I understand that in 5.7 the correct behavior, as we read in the post: BEFORE triggers and NOT NULL columns in MySQL - http://mysqlserverteam.com/before-triggers-and-not-null-columns-in-mysql/, should be to generate the error in both cases, with and without trigger. Is this correct?.

Surely the behaviour should be that it should **warn** in both cases if "STRICT_TRANS_TABLES" is not in sql_mode, and should **error** in both cases if STRICT_TRANS_TABLES is in sql_mode.

If it's the case that it always errors it represents a BC break from 5.6, as there's no way for code that previously worked with "not null" and strict_trans_tables off to continue working.

Noted in 5.7.11, 5.8.0 changelogs.

When not in strict SQL mode, attempts to implicitly insert NULL into
a NOT NULL column resulted in different behavior depending on whether
the table had a trigger.

Since this is breaking existing functionality when having a server set to non-strict transactions.  Is this going to be fixed? With this it looks like it is just going to be noted.  Is there another bug that we should be tracking that will be addressing this fault?