MySQL Bugs: #36118: RBR used for mysql database tables

Bug #36118	RBR used for mysql database tables
Submitted:	16 Apr 10:39	Modified:	9 May 17:31
Reporter:	Zhenxing He
Status:	Closed
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Zhenxing He	Target Version:
Triage:	D4 (Minor)

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.

I've added the docs request from here and from Jason's email to my TODO list.

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.

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.

NB: I've reverted the changes referenced above until Jason and I have a chance to discuss
this issue more completely (hopefully, tomorrow).

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

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.

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.