MySQL Bugs: #56917: RBR should use default for nullable fields if field missing

Bug #56917	RBR should use default for nullable fields if field missing
Submitted:	22 Sep 2010 6:59	Modified:	24 Mar 2011 15:40
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	mysql-5.1.47-ndb-7.1.7	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
When a tuple is inserted on Master with a column omitted, the default value for this column should be used when the row gets replicated on Slave. This works when the column is NOT NULL, but it doesn't work when it's defined as nullable.

This is useful in cases where schema changes are first done on the Slave using RBR. It should work from any data type to any data type (unless really not possible).

This is not specific to the NDB storage engine, same effect is seen with InnoDB.

How to repeat:
Master:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
c1 INT,
c2 VARCHAR(30),
PRIMARY KEY (id)
) ENGINE=NDB;

Slave:
1) ALTER TABLE t1 MODIFY c1 INT NOT NULL DEFAULT 1;
2) ALTER TABLE t1 MODIFY c1 INT DEFAULT 2;
3) ALTER TABLE t1 MODIFY c1 TINYINT DEFAULT 3;

Master:
INSERT INTO t1 (c2) VALUES ('ham');

Result after ALTER operations on Slave:
1) Correct:

mysql> SELECT * FROM t1;
+----+----+------+
| id | c1 | c2   |
+----+----+------+
|  1 |  1 | ham  |
+----+----+------+

2) Not correct:

mysql> SELECT * FROM t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | NULL | ham  |
+----+------+------+

3) Not correct:

mysql> SELECT * FROM t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | NULL | ham  |
+----+------+------+

RBR events carry two images: before image (BI) and after
image (AI). These are shipped from the master to the slave:

  - DELETE: only needs BI (to find the correct row)
  - INSERT: only needs AI (to insert the row)
  - UPDATE: needs both BI (to find the correct row) and AI (to
            update the row found)

Now... the full rows (BI and/or AI) are always written to the
binary log. Ultimately, this means that when the slave replays
the event, it will use all the values got in the AI to
update/write the values to the table. Since all the fields are in
the images got from the master, they will be used to
update/insert the row at the slave (thence, bypassing default
values).

This behavior is the most secure, wrt making the slave not to go
out of sync. However, as Geert points out, default values are not
used at the slave.

Note though, that after WL#5092 is merged in, the user is able to
specify whether he wants full or partial rows. If partial rows
are to be used, then only the fields selected/changed are
actually shipped in the BI and AI respectively. This gives the
slave a chance to use only the fields the user specified on the
master, therefore, the slave is able to use its own default
values when replaying UPDATE or INSERT events.

Moving to PQ as WL#5092 has been pushed to mysql-next-mr.

As explained in a previous comment ([22 Sep 12:51] Luís Soares)
after WL#5092, the user is able to select the desired behavior,
either use full rows or partial rows for images stored in the
binary log. 

If using partial rows, the slave will use its own default values
for missing columns.

Fixed by WL#5092, qv. for docs info. Closed.