Bug #69360 Please add counter of RBR updates/deletes on slave which affect more than 1 row
Submitted: 31 May 2013 5:46 Modified: 31 May 2013 8:32
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2013 5:46] Simon Mudd 
Description:
Row based replication is intended to push out a list of changes when making DELETES or UPDATES that affect a single row. e.g. See: ugs.mysql.com/bug.php?id=64325

However, if a primary key is not set on the table as far as I know this will not generate an error so a change like the following:

### UPDATE emailmkt.email_settings_cron_schedule
### WHERE
###   @1='send'
###   @2=1329132962
### SET
###   @1='send'
###   @2=1329133022

may update more than one row for each "rbr action".  The problem with this is that it's not visible anywhere. That is there is no counter of rbr_updates_not_updating_unique_rows, or rbr_updates_not_deleting_unique_rows, which you would expect to be zero and under such circumstances would be > 0, and thus easier to track.  Optionally warning if this behaviour happens in the error log might be good but only if it's not done all the time and we can switch it off as it will be very noisy on any non-trivially sized table.

Also previously requested, make the SQL thread show which table it is changing in SHOW PROCESSLIST output when in RBR mode so that the DBA can have some idea of what replication is doing rather than now where this activity is suddenly invisible.  If applying a large change to a table having some sort of counter of the "row number" would also given an indication of progress which would be most useful.

How to repeat:
See above.

Suggested fix:
Provide counters to indicate when PK based RBR updates or deletes are affecting more than one row.
Provide information to the SQL thread's activity of which table it is "working on" and how far progress on an individual "event" is taking.
[31 May 2013 5:47] Simon Mudd 
Adjust synopsis
[31 May 2013 5:47] Simon Mudd 
Adjust synopsis
[8 Jun 2013 0:35] Davi Arnaut 
- Expose current row-based replication (RBR) execution state in the SQL Thread state

The changes below add two new states to the replication slave SQL thread that are used to highlight the event that the SQL thread is executing and, for row-based events, to indicate how many rows have been applied. These will generally look like:

  Executing Delete_rows event at position 100
  Handling row 50 of 100 for a Delete_rows event on table `db.table`

https://github.com/twitter/mysql/commit/3a1e4b5682d994ccefc919a33d9ac7d2adc92705
https://github.com/twitter/mysql/commit/d9895b4410f744a231f789ec9d894dbe633e3b14