MySQL Bugs: #37787: UPDATE NULL to NOT NULL column gives errors 1048 and 1105

Bug #37787	UPDATE NULL to NOT NULL column gives errors 1048 and 1105
Submitted:	2 Jul 2008 0:01	Modified:	28 Jan 17:34
Reporter:	Daniel Donckers
Status:	Duplicate
Category:	Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1.25, 5.1, 6.0 BZR	OS:	Any
Assigned to:	Gleb Shchepa	Target Version:
Tags:	1048, 1105, null, UPDATE
Triage:	Triaged: D4 (Minor) / R2 (Low) / E2 (Low)

Description:
I have many tables with NOT NULL columns which are updated via various scripts.  The
expected behavior is that if a NULL or undef value is present in the update, the default
value for the field will be used if the field is a NOT NULL.  Prior versions handled this
with a warning.

In versions 5.1.24 and 5.1.25 I have seen this situation cause the UPDATE to fail on a
Master while slaves replicating from an older version Master will stop replicating when
this scenario occurs.

There are two errors being generated when this happens on a Master: 1048 (which was the
warning in 5.0) and 1105.  Replication stops on slaves and reports only error 1048.

How to repeat:
CREATE TABLE `t1` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` char(8) NOT NULL DEFAULT '',
  `c` varchar(255) NOT NULL DEFAULT ''
);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = NULL;
ERROR 1048 (23000): Column 'a' cannot be null
mysql> SHOW WARNINGS;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Error | 1048 | Column 'a' cannot be null |
| Error | 1105 | Unknown error             |
+-------+------+---------------------------+
2 rows in set (0.00 sec)

mysql> UPDATE IGNORE t1 SET a = NULL;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

Thank you for the report.

Verified as described.

Setting the right lead for a DML bug.

Verified problem is not error 1048 which is expected since 5.1, but error 1105:

Error | 1105 | Unknown error

Note that the change in UPDATE behavior is desired; see Bug#33699, "UPDATE allows NULL as
new value on NOT NULL columns (= default datatype value)".  The buggy behavior here is
that an additional error is reported (Error | 1105 | Unknown error).

So, has anyone looked into the compatibility mode that Kristian Koehntopp proposed in the
comments on Bug #33699?

I understand that this change was probably made for reason of standardization, but there
must be a way to aid transition into this behavior.

We definately need a compatibility mode as well. The fix is definately desired but it will
probably break MANY applications. Many developers will most likely never have cared about
this before as it simply worked. Give us some time to fix our (bad ;) ) applications and
remove the flag in 6.0 again if you have to :|. This is definately an upgrade blocker.

I just got hit by the same thing.  I setup test slave to test 5.1 but replication is
halting because the production server is allowing the NULLs with a warning.   The slave
server then tries to run the same thing and halts with an error.   I have no problem
cleaning this up by fixing our code.   as I can only see it happening in a few places.  
obviously this wont be an issue once i make my production server 5.1 but its making me
weary of upgrading at all.  Im hoping for a smooth upgrade without many code changes.

drop table if exists t1;
create table `t1` (
    `a` int(11) not null default '0',
    `b` char(8) not null default '',
    `c` varchar(255) not null default ''
    );
insert into t1 (a) values (1);
update t1 set a = null;
show warnings;
update ignore t1 set a = null;
show warnings;

Still repeatable against the latest 6.0 (Unknown error is appended).

patch for reverting update null behavior

Attachment: my.patch (text/x-patch), 631 bytes.

Hi,  I wanted to upgrade to 5.1 and couldnt because of this issue.  The patch that
corrects http://bugs.mysql.com/bug.php?id=33699 will prevent me from upgrading to 5.1
because our php code contains bugs.   php is loosely typed and its easy for a developer
to make the mistake if mysql does not show the error.   Our code is too mature now to go
back and fix all of these mistakes all at once.  It will take lots of time and testing
for us to do this.  So i instead created a patch that reverts this behavior on 5.1.30 GA.
 Its only one line of code within the sql_update.cc,  it compiles fine and works as
expected.  No errors during replication.    However,  I did not check the test suite
which was also modified by the patch.  If you do not use the test suite then its probably
fine.

This bug is a duplicate of bug #39265.
"Unknown error" issue will be fixed together with that bug.