MySQL Bugs: #14068: RBR: Write only primary key instead of entire before image

Bug #14068	RBR: Write only primary key instead of entire before image
Submitted:	17 Oct 2005 12:06	Modified:	17 Sep 14:30
Reporter:	Mats Kindahl
Status:	In progress
Category:	Server: Replication	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (ALL)
Assigned to:	Luís Soares	Target Version:
Tags:	RBR, columns
Triage:	Triaged: D5 (Feature request)

Description:
When writing for update, the *entire* before image and after image is written to the
event.  The before image is used to find the correct row to modify on the slave side.  If
there is a primary key or index, that will be used; otherwise, a full scan is made of all
the rows in the table.

If there is a primary key or unique not null index, there is no need to send the entire
before image: only those columns that are part of the key or index need to be written.

How to repeat:
Inspect the format of the Update_rows_log_event.

Suggested fix:
Write only the primary key value instead of the entire before image. To future-proof the
implementation, there shall be an option to log the entire before image).

This is implemented in 6.0 as a result of fixing BUG#33055.

A patch for this is pushed into 6.0 as a result of fixing BUG#33055, however, some more
work is needed (mainly for efficiency reasons), so this bug is kept open.

Full rows are needed for value-based conflict detection and to have a reversible format,
the PKE and all changed columns need to be present in both the before and after image of
an update_row. For that reason, I would suggest to add an option

--binlog-row-image={minimal,reversible,full}

where

- minimal means PKE in the before image and changed columns in after image

- reversible means PKE and changed columns in before and after image

- full means all columns in both before and after image

It is possible that there could be a need for a format using the PKE in the before image
and all columns in the after image; this could be used to save space but still allow
value-based conflict detection on the after image. In this case, the image is not
reversible.

Mats,

I don't think that this feature should be implemented. Sometimes master and slave don't
have the exactly same index structure. Quite often our customers use, for example,
Archive storage engine on slave.

This can only be implemented with a startup option, which should be OFF by default.