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 10:06	Modified:	28 Jun 2011 18:07
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (ALL)
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	columns, RBR

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.

Fixed in 5.6.2 by WL#5576, q.v. for docs info. Closed.