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:	1 Jul 2008 22:01	Modified:	28 Jan 2009 16:34
Reporter:	Daniel Donckers	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1.25, 5.1, 6.0 BZR	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	1048, 1105, null, UPDATE

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.