MySQL Bugs: #35608: changed NOT NULL handling breaks replication in 5.1

Bug #35608	changed NOT NULL handling breaks replication in 5.1
Submitted:	27 Mar 2008 14:12	Modified:	28 Mar 2008 13:37
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.1.23a-maria-alpha-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A 5.0.46 master handles a UPDATE t SET t = NULL differently than a 5.1.23 slave - what was a warning once is an error now. This breaks my replication.

How to repeat:
On a 5.0.46 master, I get

root on mysql.sock [kris]> show create table lall\G
*************************** 1. row ***************************
       Table: lall
Create Table: CREATE TABLE `lall` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `t` tinyint(4) NOT NULL default '1',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
1 row in set (0.00 sec)

root on mysql.sock [kris]> select * from lall;
+----+---+
| id | t |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

root on mysql.sock [kris]> update lall set t = NULL where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

root on mysql.sock [kris]> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 't' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

root on mysql.sock [kris]> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.06 sec)

Here is what the 5.1.23 does:

root@dc01mdb-02 BP [kris]> show create table lall\G
*************************** 1. row ***************************
       Table: lall
Create Table: CREATE TABLE `lall` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` tinyint(4) NOT NULL DEFAULT '1',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
1 row in set (0.00 sec)

root@dc01mdb-02 BP [kris]> select * from lall;
+----+---+
| id | t |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

root@dc01mdb-02 BP [kris]> update lall set t = NULL where id = 1;
ERROR 1048 (23000): Column 't' cannot be null
root@dc01mdb-02 BP [kris]> show global variables like
    -> 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

This breaks replication.

Suggested fix:
Make default behaviour identical, or provide an SQL_MODE to make it identical.

Maybe this is specific to Maria tree. On normal 5.1.23 I get same results as on 5.0.56:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.23-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `lall` (
    ->   `id` bigint(20) unsigned NOT NULL auto_increment,
    ->   `t` tinyint(4) NOT NULL default '1',
    ->   UNIQUE KEY `id` (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.53 sec)

mysql> insert into lall values (1,1);
Query OK, 1 row affected (0.11 sec)

mysql> update lall set t = NULL where id = 1;
ERROR 1048 (23000): Column 't' cannot be null
mysql> set sql_mode='';
Query OK, 0 rows affected (0.02 sec)

mysql> update lall set t = NULL where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

testcase:

drop table if exists t;
create table t(a int not null default '1')engine=myisam;
insert into t set a=1;
update t set a=null;
show warnings;
select version();

run the above on "5.1.23a-maria-alpha-community-maria" and "5.1.23-rc-community-debug" and you'll see one version gives an error while other version gives a warning.

5.1.23a-maria-alpha-community-maria returned:
mysql> update t set a=null;
ERROR 1048 (23000): Column 'a' cannot be null

5.1.23-rc-community-debug returned:
mysql> update t set a=null;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

"Maybe this is specific to Maria tree."

Yes, as Shane has shown this is Maria-Only. We have crossgraded to 5.1.23-nonmaria and it is gone now.

Not a bug. 5.1.24 has the same behavior, it was a fix for bug#33699.

Sergei, the fix for http://bugs.mysql.com/bug.php?id=33699 break replication, see my comment in that bug.

When you have a master with this bug and slaves with the fix for 33699, the slaves will stop because they refuse to execute commands that were valid on the master. There must be a method to run a replication network with a mixed population of servers that have this bug or the fix for it. I have customers with 100+ servers and there is no way ever to roll out a new version atomic, so we always have to updates slaves first, then the master last.

Any bug fix that turns erroneous behavior into an error can break replication.

For example bug#35578 - it's about incorrect constraint syntax. When it'll be fixed the statement "CREATE TABLE tc (c1 INT, CONSTRAINT chk);" will break replication if a slave is newer than a master, Still, we will *not* provide a bug-compatibility mode to allow slave to recognize such a syntax - the application should be fixed not to issue erroneous queries, not the slave.

This is very annoying.   I will not upgrade because of this.