MySQL Bugs: #104712: IMPLICITLY added NOT NULL constraint missing in the binary log

Bug #104712	IMPLICITLY added NOT NULL constraint missing in the binary log
Submitted:	24 Aug 2021 16:55	Modified:	8 Oct 2021 15:34
Reporter:	Systems Engineer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	implicit declaration not replicated

Description:
Unexpected behaviour with DDL statement which has a primary key that is not having a constraint 'NOT NULL'. The statement logged in the binary log differs from that executed in the master.

As per the MySQL 5.7 document, "PRIMARY KEY columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently)"

Issue seen for the CREATE TABLE statement, where the column for Primary Key doesn't have a 'NOT NULL' constraint  in the original query statement.
Mysql implicitly adds the NOT NULL constraint to the column definition  during execution. However, in the binary log, the DDL statement captured doesn't have this implicitly declared  NOT NULL constraint

Test environment
-----------------------

MySQL 5.7.35
Binary logging enabled
binlog_format = MIXED

Original DDL Statement :-

CREATE TABLE `test7` (
  `id` INT(11) ,   -- No constraint
  `number` INT(11) DEFAULT NULL,
  `form` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Table successfully created and table definition seen after table creation:-

SHOW CREATE TABLE `test7`

CREATE TABLE `test7` (
  `id` int(11) NOT NULL,    -- NOT NULL constraint added by mysql
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Statement from the Binlog:-

CREATE TABLE `test7` (
  `id` int(11) ,    -- No contraint
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;

It seems the actual DDL executed and the one captured in the binlog are different. Was expecting binlog to capture the same DDL as executed (in this case it should have kept the NOT NULL constraint which was implicitly added)
When replication is across different mysql versions, this behaviour is causing replication breaks.

Example:

Master: MySQL 5.6.42
Slave: MySQL 5.7.35

DDL Statement executed in master (5.6.42):-

CREATE TABLE `test10` (
  `id` INT(11) DEFAULT NULL,    -- DEFAULT NULL. Expecting mysql to change this silently
  `number` INT(11) DEFAULT NULL,
  `form` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Table successfully created and table definition seen after table creation:-

SHOW CREATE TABLE test10

CREATE TABLE `test10` (
  `id` int(11) NOT NULL DEFAULT '0', -- mysql added a default value of 0 and a NOT NULL constraint
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

However in binary log the statement is logged as :

CREATE TABLE `test10` (
  `id` INT(11) DEFAULT NULL,  -- No change in binlog, still has the original defnition which is different from executed statement
  `number` INT(11) DEFAULT NULL,
  `form` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1
/*!*/;

When the above statement from binlog is executed on a replicated 5.7.35 slave instance, it will result in replication break with error :-  "Error Code: 1171 , All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead"
Note :- If the statement in master 5.6.42 did not have the 'DEFAULT NULL' clause, the 5.7.35 slave would have implicitly added  a constraint of  'NOT NULL'

How to repeat:

1)On MySQL 5.7.35 , enable binary logs with configuration as below in the cnf 

                max_binlog_size         = 50M
                log_bin                 = /data/log/mysql_repl_log/mysql57-bin
                log-bin-index           = /data/log/mysql_repl_log/mysql57-bin.index
                binlog_format           = MIXED
                sync_binlog             = 1

2) Execute FLUSH LOGS; and note down the current binary log (eg mysql57-bin.000005) 

2) Create a table with a primary key and without a not null constraint

CREATE TABLE `test7` (
  `id` INT(11) ,   -- No constraint
  `number` INT(11) DEFAULT NULL,
  `form` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

3) Verify the table definition after creation. You can see the NOT NULL constraint added by MySQL silently

SHOW CREATE TABLE `test7`

CREATE TABLE `test7` (
  `id` int(11) NOT NULL,    -- NOT NULL constraint added by mysql
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

4) Check the statement logged to the the Binlog using mysqlbinlog utility and you can see that the NOT NULL constraint is missing-

# mysqlbinlog mysql57-bin.000005;

CREATE TABLE `test7` (
  `id` int(11) ,    -- No contraint
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;

Suggested fix:
Binary logs should contain implicitly added constraint

Hi,

Why would binlog need this when it will be created properly on the slave?

master [localhost:20236] {msandbox} (test) > CREATE TABLE `test7` (
    ->   `id` INT(11) ,   -- No constraint
    ->   `number` INT(11) DEFAULT NULL,
    ->   `form` VARCHAR(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

master [localhost:20236] {msandbox} (test) > show create table test7\G
*************************** 1. row ***************************
       Table: test7
Create Table: CREATE TABLE `test7` (
  `id` int(11) NOT NULL,
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

master [localhost:20236] {msandbox} (test) > \q
Bye

arhimed@localdev rsandbox_5_7_35]$ ./s2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost:20238] {msandbox} ((none)) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave2 [localhost:20238] {msandbox} (test) > show create table test7\G
*************************** 1. row ***************************
       Table: test7
Create Table: CREATE TABLE `test7` (
  `id` int(11) NOT NULL,
  `number` int(11) DEFAULT NULL,
  `form` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

slave2 [localhost:20238] {msandbox} (test) >

For a 5.7 Slave replicated from a 5.6 Master, it breaks the replication.
The 5.6 handling of the situation is a bit different - For the primary key with DEFAULT NULL,  it adds a DEFAULT 0, with a NOT NULL constraint.
But in binlog it still writes it as 'int(11) DEFAULT NULL' . 
In 5.7 Slave 'int(11) DEFAULT NULL is errored out.
This situation (usually during an upgrade process) may be averted in possibly two different methods

1) The binlog generated by master shoud have the implicitly modified statement
2) Slaves should detect that the master is on 5.6 and should fall back to the old behaviour

Hi,

So this is replication from 5.6 to 5.7 that you have issues with and fix would have to go into 5.6. Normal support for 5.6 ended in February 2021 and since then 5.6 is in sustaining support meaning security bugs, critical stuff ... I don't see this being critical bug that fix will go into 5.6.

I'll check with my colleagues if we can offer some solution but I doubt we can patch 5.6

Thanks

I did not find a proper workaround, verifying the behavior for completeness. 

Thanks for the report