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: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.1, 6.0 | OS: | Any |
Assigned to: | Zhenxing He | CPU Architecture: | Any |
[16 Apr 2008 8:39]
Zhenxing He
[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.