Bug #54896 --one-database option fails to filter row-based binlog replay correctly
Submitted: 29 Jun 2010 15:35 Modified: 15 Oct 2012 14:21
Reporter: Todd Farmer (OCA) Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.47 OS:Any
Assigned to: CPU Architecture:Any

[29 Jun 2010 15:35] Todd Farmer
When performing a point-in-time recovery, it is sometimes necessary to filter such that only a specific database schema is affected.  This has historically been supported by the --one-database option.  However, this option depends on "USE db" statements, which, while included in statement-based binary logs, are not present in row-based binary logs.  Row-based binary logs contain a table map:

#100629  8:22:46 server id 1  end_log_pos 212 	Table_map: `test1`.`t1` mapped to number 26

but this is not used to filter processing.

Other mechanisms which apply to statement-based binary logs, such as executing with specifically-targeted privileges (https://kb.mysql.com/view.php?id=8316), are similarly affected (as BINLOG statement requires SUPER privilege to execute and cannot be applied on a per-database or per-table basis).  This leaves users of row-based binary logging without an effective means to filter when replaying binary logs, and represents a loss of functionality compared to statement-based binary logs.

Indeed, the sudden change to expected and historical behavior will allow point-in-time recovery to silently produce inconsistent results (some expected filtered data will be replayed, incorrectly, while other data will be filtered where it shouldn't, based on any statement-based logging - such as DML - that is in the binary log).

How to repeat:
CREATE TABLE test1.t1 (a INT);
CREATE TABLE test2.t2 (a INT);
INSERT INTO test1.t1 VALUES (1);
INSERT INTO test2.t2 VALUES (2);
TRUNCATE test1.t1;
TRUNCATE test2.t2;

Then replay the binary logs associated with the INSERT statements, using the --one-database option.

Suggested fix:
When --one-database is used, evaluate the table map to determine whether or not to send the associated BINLOG statement to the server.