MySQL Bugs: #73207: 5.7 triggers handle not null differently to older versions; breaks compatibility

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:	None
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

Description:
A recent issue came up on a 5.6.15 master replicating to a 5.7.4 slave.  The 5.6 master had several other 5.6 slaves and ran fine, however the 5.7.4 slave stop replicating due to this error.

2014-07-04T13:41:53.440080Z 533349 [ERROR] Slave SQL: Error 'Column 'col_2' cannot be null' on query. Default database: 'mydb'. Query: 'INSERT INTO MyTable (pk_key)
VALUES ('1111111')', Error_code: 1048
2014-07-04T13:41:53.448930Z 533349 [Warning] Slave: Column 'col_2' cannot be null Error_code: 1048

The error is correct in that the statement is obviously not good but the upstream master accepted this so the downstream slave should do too.

How to repeat:
The table is defined as:

> SHOW CREATE TABLE MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `pk_key` mediumint(8) unsigned NOT NULL,
  `col_2` tinyint(1) NOT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The master and slave have the following settings:

binlog_format = STATEMENT  # not relevant to the slave that does not write binlogs
sql_mode = NO_ENGINE_SUBSTITUTION

Master running: 5.6.15
Slave running: MySQL-server-5.7.4_m14-1.el6.x86_64

Suggested fix:
Under normal circumstances the behaviour seems correct, to not accept the statement.

However, under replication during an upgrade process, where the master runs 5.6 and the slave runs 5.7 I would expect that this should NOT break replication, and the slave should behave like a 5.6 slave and SHOULD accept the statement. Failing to do so can potentially break replication on a number of server during an upgrade process and this is very disruptive.

Ideally I'd hope that no special configuration on the slave is needed to make this work.

are there any triggers on the table ?

No, no triggers on this table.

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

-----------

Seems to be a change introduced by the fix for:
http://bugs.mysql.com/bug.php?id=6295

Thanks for finding the actual issue, and why it has just come up.
Will wait to see what can be done about this.

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.

Hello!I have the same problem

How fix this bag? There is a solution?