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
Triage: D2 (Serious)

[27 Mar 2008 14:12] Kristian Koehntopp
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.
[28 Mar 2008 6:29] Valeriy Kravchuk
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
[28 Mar 2008 6:36] Shane Bester
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.
[28 Mar 2008 6:38] Shane Bester
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
[28 Mar 2008 8:56] Kristian Koehntopp
"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.
[28 Mar 2008 10:06] Sergei Golubchik
Not a bug. 5.1.24 has the same behavior, it was a fix for bug#33699.
[28 Mar 2008 10:22] Kristian Koehntopp
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.
[28 Mar 2008 13:37] Sergei Golubchik
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.
[15 Aug 2008 1:52] jon d
This is very annoying.   I will not upgrade because of this.