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
Tags: columns, RBR
Triage: Triaged: D5 (Feature request)

[17 Oct 2005 10:06] Mats Kindahl
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).
[25 Apr 2008 13:30] Mats Kindahl
This is implemented in 6.0 as a result of fixing BUG#33055.
[5 Jun 2008 7:31] Mats Kindahl
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.
[29 Apr 2009 9:38] Mats Kindahl
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.
[20 Jul 2009 13:32] Sinisa Milivojevic
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.
[28 Jun 2011 18:07] Jon Stephens
Fixed in 5.6.2 by WL#5576, q.v. for docs info. Closed.