Bug #36118 RBR used for mysql database tables
Submitted: 16 Apr 2008 8:39 Modified: 9 May 2008 15:31
Reporter: Zhenxing He Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1, 6.0 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any
Triage: D4 (Minor)

[16 Apr 2008 8:39] Zhenxing He
Description:
As documented in http://dev.mysql.com/doc/refman/5.1/en/replication-features-mysqldb.html

We should not replicate any modifications to tables in database 'mysql' in row base format,  but if you run the follow test:

set binlog_format=row;
insert mysql.user set user='rpl', host='127.0.0.1';
update mysql.user set host='localhost' where user='rpl';
delete from mysql.user where user='rpl';

You will see all the statements are logged as row events. However if we use grant privileges, drop user, etc, it will be correctly logged as a statement.

How to repeat:
See Description.

Suggested fix:
As the document stated, row base replication is not supported for database mysql, we should follow that for delete/update/insert statements for tables in database mysql too.
[16 Apr 2008 9:33] Jon Stephens
I've added the docs request from here and from Jason's email to my TODO list.
[19 Apr 2008 16:58] Jon Stephens
Hi Jason,

I've updated the docs per our discussion.

Commit: http://lists.mysql.com/commits/45658

New location of page will be here: http://dev.mysql.com/doc/refman/5.1/en/replication-features-privileges.html (changed page ID and title to reflect content better)

Take a look when the new version gets online and let me know whether you think that any further changes should be made.
[20 Apr 2008 5:01] Zhenxing He
Hi Jon

The same should apply to other system tables such as event, func, proc, servers, etc. So statements like CREATE EVENT, CREATE FUNCTION, CREATE PROCEDURE, CREATE SERVER,  etc will only be replicated using statement format.

Since mysql system table is considered host specific, I think it maybe more appropriate to not replicate DML statements on mysql database. Only specific statements such as GRANT, DROP USER, CREATE EVENT, CREATE FUNCTION, etc, that modifies data on mysql database will be replicated using statement format events.
[21 Apr 2008 8:32] Jon Stephens
NB: I've reverted the changes referenced above until Jason and I have a chance to discuss this issue more completely (hopefully, tomorrow).
[8 May 2008 7:42] Susanne Ebrecht
Verified as described:

mysql> select version();
 5.1.25-rc-debug-log

MASTER:
mysql> drop user foo;
Query OK, 0 rows affected (0.00 sec)

mysql> insert mysql.user set user='foo', host='127.0.0.1';
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> update mysql.user set host='localhost' where user='foo';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from mysql.user where user='foo';
Query OK, 1 row affected (0.00 sec)

GENERAL LOG ON SLAVE:
080508  9:36:52    3 Query      drop user foo
080508  9:37:07    3 Query      BEGIN
                    3 Query     COMMIT
080508  9:37:15    3 Query      BEGIN
                    3 Query     COMMIT
080508  9:37:23    3 Query      BEGIN
                    3 Query     COMMIT
[9 May 2008 13:56] Mats Kindahl
The behavior described is correct and is not a bug. This is the intended behavior.

Non-DML statements that affect tables in the mysql database will be replicated as statements. The rationale is that these statement are more stable than the structure of the tables in the mysql database in the sense that the statements will not change if the structure of the tables changes and that any changes to the statements will only be made in such a way as to ensure backwards compatibility.

DML statements affecting tables in the mysql database will be replicated according to the value of the binlog_format variable, i.e., either statement- or row-based. In this sense, they behave as a DML statement operating on a table in any other database, also with regard to filtering.
[9 May 2008 15:31] 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.