Bug #73207 | 5.7 triggers handle not null differently to older versions; breaks compatibility | ||
---|---|---|---|
Submitted: | 5 Jul 2014 12:21 | Modified: | 24 Nov 2014 16:41 |
Reporter: | Simon Mudd (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 5.7.4 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | 5.6, 5.7, cannot be null, replication |
[5 Jul 2014 12:21]
Simon Mudd
[5 Jul 2014 12:33]
Simon Mudd
Ideally I'd hope that no special configuration on the slave is needed to make this work.
[6 Jul 2014 18:32]
MySQL Verification Team
are there any triggers on the table ?
[7 Jul 2014 16:14]
Simon Mudd
No, no triggers on this table.
[9 Jul 2014 18:18]
MySQL Verification Team
The bug has nothing to do with replication. Turns out there was a trigger, due to pt-online-schema-change. The problem can be demonstrated as follows: -------- set sql_mode=''; drop table if exists t1,t2; create table t1(a int, b int not null, primary key(a))engine=innodb; create table t2(a int, b int not null, primary key(a))engine=innodb; insert into t1(a) values (1); create trigger t1_after_delete after delete on t1 for each row delete from t2 where a <> old.a; insert into t1(a) values (2); select version(); ------- On 5.6 the result is: --------------------- mysql> create table t1(a int, b int not null, primary key(a))engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> create table t2(a int, b int not null, primary key(a))engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> insert into t1(a) values (1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create trigger t1_after_delete after delete on t1 for each row delete from t2 where a <> old.a; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(a) values (2); Query OK, 1 row affected, 1 warning (0.00 sec) -------------------- On 5.7 we got error: -------------------- mysql> create table t1(a int, b int not null, primary key(a))engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql> create table t2(a int, b int not null, primary key(a))engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql> insert into t1(a) values (2); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> create trigger t1_after_delete after delete on t1 for each row delete from t2 where a <> old.a; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(a) values (2); ERROR 1048 (23000): Column 'b' cannot be null -----------
[9 Jul 2014 18:40]
MySQL Verification Team
Seems to be a change introduced by the fix for: http://bugs.mysql.com/bug.php?id=6295
[10 Jul 2014 11:19]
Simon Mudd
Thanks for finding the actual issue, and why it has just come up. Will wait to see what can be done about this.
[24 Nov 2014 16:41]
Paul DuBois
Noted in 5.7.6 changelog. If a table had a NOT NULL column, for an INSERT statement on the table for which the column value was not specified, the server produced ERROR 1048 "Column cannot be null" rather than Warning 1364 "Field doesn't have a default value" if there was a BEFORE trigger with an action type different from ON INSERT.
[27 Apr 2017 14:36]
Виталий Кушниренко
Hello!I have the same problem How fix this bag? There is a solution?