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

*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `pk_key` mediumint(8) unsigned NOT NULL,
  `col_2` tinyint(1) NOT NULL,
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

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.
[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] Shane Bester
are there any triggers on the table ?
[7 Jul 2014 16:14] Simon Mudd
No, no triggers on this table.
[9 Jul 2014 18:40] Shane Bester
Seems to be a change introduced by the fix for:
[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?