Bug #32605 | Binary log and replication filtering options differ between RBR and SBR | ||
---|---|---|---|
Submitted: | 21 Nov 2007 22:56 | Modified: | 10 Jan 2008 15:37 |
Reporter: | Todd Farmer (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.1.22 | OS: | Any |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
[21 Nov 2007 22:56]
Todd Farmer
[21 Nov 2007 23:13]
Todd Farmer
Another example, this dealing with binlog-ignore-db: mysql> use test; Database changed mysql> set binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec) mysql> insert into filter.t2 values (1); Query OK, 1 row affected (0.28 sec) mysql> show binlog events in 'knopfler-bin.000004'; +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | knopfler-bin.000004 | 4 | Format_desc | 6 | 106 | Server ver: 5.1.22-rc-community-log, Binlog ver: 4 | | knopfler-bin.000004 | 106 | Query | 6 | 199 | use `test`; create table filter.t2 (i int) | | knopfler-bin.000004 | 199 | Query | 6 | 292 | use `test`; create table filter.t3 (i int) | +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ 3 rows in set (0.00 sec) mysql> show master status; +---------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------+----------+--------------+------------------+ | knopfler-bin.000004 | 292 | | filter | +---------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> create table ft (i int); Query OK, 0 rows affected (0.11 sec) mysql> use filter Database changed mysql> insert into test.ft values (1); Query OK, 1 row affected (0.05 sec) mysql> show binlog events in 'knopfler-bin.000004'; +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | knopfler-bin.000004 | 4 | Format_desc | 6 | 106 | Server ver: 5.1.22-rc-community-log, Binlog ver: 4 | | knopfler-bin.000004 | 106 | Query | 6 | 199 | use `test`; create table filter.t2 (i int) | | knopfler-bin.000004 | 199 | Query | 6 | 292 | use `test`; create table filter.t3 (i int) | | knopfler-bin.000004 | 292 | Query | 6 | 378 | use `test`; create table ft (i int) | | knopfler-bin.000004 | 378 | Table_map | 6 | 419 | table_id: 16 (test.ft) | | knopfler-bin.000004 | 419 | Write_rows | 6 | 453 | table_id: 16 flags: STMT_END_F | +---------------------+-----+-------------+-----------+-------------+----------------------------------------------------+ 6 rows in set (0.02 sec)
[22 Nov 2007 9:27]
Mats Kindahl
Yes, row-based replication is filtering the rows on the actual table (including the database) and not on the currently selected database. This is a deliberate decision made to resolve some of the problems with statement-based replication (e.g., multi-table updates). It is unfortunate that it has not been documented properly. In general when using statement-based replication, one should never qualify the tables with the database, and instead set the current database to the correct one. If that rule is followed, mixed mode replication will not have any problems either. For some more information, please have a look at my post regarding this, which is available under: http://mysqlmusings.blogspot.com/2006/09/replication-and-disappearing.html
[26 Nov 2007 15:02]
Jon Stephens
Changed Category to Docs so this will show up in my queue.
[10 Jan 2008 15:37]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.