Bug #119214 Document the handling of NULL to NOT NULL conversion in MySQL replication

• Submitted: 23 Oct 2025 3:16
• Reporter: Jericho Arturo Rivera
• Status: Open
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[23 Oct 2025 3:16] Jericho Arturo Rivera

Description:
There is no clarity regarding how MySQL handles conversion for NULL columns on the primary and NOT NULL columns in the replicas. If this is common knowledge and accepted, it should at least be documented in https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html

Results:
Check the table definition in mysql-1...
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` mediumblob,
  `num` int DEFAULT NULL,
  `createdat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Check the table definition in mysql-2...
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` mediumblob NOT NULL,
  `num` int NOT NULL,
  `createdat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Inserting data from mysql-1...

Comparing data from mysql-1 and mysql-2...
+------------+------------------+------+---------------------+
| @@hostname | content          | num  | createdat           |
+------------+------------------+------+---------------------+
| mysql-1    | NULL             | NULL | 2025-10-23 01:32:05 |
+------------+------------------+------+---------------------+
+------------+------------------+-----+---------------------+
| @@hostname | content          | num | createdat           |
+------------+------------------+-----+---------------------+
| mysql-2    | 0x               |   0 | 2025-10-23 01:32:05 |
+------------+------------------+-----+---------------------+

How to repeat:
# Cleanup
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'DROP DATABASE IF EXISTS testdb';
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'SET @@global.sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"';
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-2 -e 'SET @@global.sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"';

# Create database
echo "Creating the database..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'CREATE DATABASE IF NOT EXISTS testdb';

# Create table
echo "Creating the table..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'CREATE TABLE IF NOT EXISTS testdb.tbl1 (id int primary key auto_increment, content mediumblob not null, num int not null, createdat datetime not null default current_timestamp())';

# Check the SQL_MODE
echo "Checking the sql_mode..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'SELECT @@hostname, @@global.sql_mode';
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-2 -e 'SELECT @@hostname, @@global.sql_mode';

# Alter the table on primary
echo "Altering the table on the primary..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'SET sql_log_bin=0; ALTER TABLE testdb.tbl1 MODIFY COLUMN content MEDIUMBLOB NULL; ALTER TABLE testdb.tbl1 MODIFY COLUMN num INT NULL;  SET sql_log_bin=1';

# Check the table definition in mysql-1
echo "Check the table definition in mysql-1..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'SHOW CREATE TABLE testdb.tbl1\G';

# Check the tavke definition in mysql-2
echo "Check the table definition in mysql-2..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-2 -e 'SHOW CREATE TABLE testdb.tbl1\G';

# Insert data
echo "Inserting data from mysql-1..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'INSERT INTO testdb.tbl1 (content, num) VALUES (NULL, NULL)';

# Compare data from mysql-1 and mysql-2
echo "Comparing data from mysql-1 and mysql-2..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-1 -e 'SELECT @@hostname, content, num, createdat FROM testdb.tbl1';
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-2 -e 'SELECT @@hostname, content, num, createdat FROM testdb.tbl1';

# Check replication status
echo "Checking replication status..."
mysql -uroot -p$MYSQL_ROOT_PASSWORD -hmysql-2 -e 'SHOW SLAVE STATUS\G';

Suggested fix:
Document it. If this should not be allowed, then it's a replication bug?