MySQL Bugs: #25091: A DELETE statement to mysql database is not logged with ROW mode format

Bug #25091	A DELETE statement to mysql database is not logged with ROW mode format
Submitted:	15 Dec 2006 11:47	Modified:	19 Mar 2007 19:03
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S1 (Critical)
Version:	5.1.14,5.1.15	OS:	Linux (Linux)
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	binary log, delete, replication, row mode

Description:
A DELETE statement issued on an existing table in ROW based logging is not reported to the binary log.

For example, after enabling row-based logging, issue this command as your first statement after you start the server:

DELETE FROM mysql.db where db in ('test', 'test_%');

The statement does not go to the binary log.

How to repeat:
See the attached test files.
There are two tests, one for row-based and one for statement-based format. Run both of them and check the results.

./mysql-test-run.pl --skip-ndb --skip-im  --mysqld=--binlog-format=row rpl_row_delete
./mysql-test-run.pl --skip-ndb --skip-im  --mysqld=--binlog-format=statement rpl_stmt_delete

Suggested fix:
none

test case to check if a delete statement is recorded to the binary log in row-based format

Attachment: rpl_delete.tar.gz (application/gzip, text), 732 bytes.

Just to dispel some doubts that the mysql database may be excluded from binlog, I ran the test with a specific option to force logging of its tables

./mysql-test-run.pl --skip-ndb --skip-im \
   --mysqld=--binlog-format=row \
   --mysqld=--binlog-wild-do-table=mysql.% rpl_row_delete

Again, the test fails on builds 5.1.14 and 5.1.15, while it passes on 5.1.13

The 'mysql' database has never been replicated, not even when using statement-based replication. For a while, we replicated the 'mysql' database, but since the database is treated as node-local data (uses caches extensively and is frequently changed), we decided to replicate the data in this database the same way statement-based replication does it: using statements.

You see the difference between for row-based replication, the rows are replicated based on the *actual* database for the table where the rows are written, instead of by the *current* database as set with USE.

If you explicitly want to force replication of this change, I suggest to switch to statement-based mode before executing it, i.e.::

    SET BINLOG_FORMAT=STATEMENT;
    DELETE FROM mysql.db where db in ('test', 'test_%');
    SET BINLOG_FORMAT=ROW;

It seems my claim above that the 'mysql' database was not replicated previously was incorrect. The workaround is still valid.

We need to make a more thourough evaluation of the situation, since we don't want to break (this aspect of) backwards compatibility.

This bug will be fixed in the following manner:

- Data manipulation statements that changes data in tables directly will be
  replicated according to the settings of BINLOG_FORMAT.

  The following is an incomplete list: INSERT, UPDATE, DELETE, REPLACE, DO,
  CREATE TABLE ... SELECT, LOAD DATA INFILE, SELECT, and TRUNCATE.

- Other statements that do not change the mysql database directly, but rather
  use the mysql database as store ("metadata" manipulation statements) will be
  replicated as statements regardless of the value of BINLOG_FORMAT.

  The following is an incomplete list: GRANT, REVOKE, SET PASSWORD,
  RENAME USER, CREATE (all of them), ALTER (all of them), and DROP (all of
  them).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20026

ChangeSet@1.2411, 2007-02-16 19:50:17+01:00, mats@romeo.(none) +8 -0
  BUG#25091 (A DELETE statement to mysql database is not logged in ROW format):
  With this patch, statements that change metadata (in the mysql database)
  is logged as statements, while normal changes (e.g., using INSERT, DELETE,
  and/or UPDATE) is logged according to the format in effect.

Good to push

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20553

ChangeSet@1.2411, 2007-02-26 10:19:08+01:00, mats@romeo.(none) +8 -0
  BUG#25091 (A DELETE statement to mysql database is not logged in ROW format):
  With this patch, statements that change metadata (in the mysql database)
  is logged as statements, while normal changes (e.g., using INSERT, DELETE,
  and/or UPDATE) is logged according to the format in effect.
  
  The log tables (i.e., general_log and slow_log) are not replicated at all.
  
  With this patch, the following statements are replicated as statements:
  GRANT, REVOKE (ALL), CREATE USER, DROP USER, and RENAME USER.

pushed to 5.1.17-beta

Noted in 5.1.17 changelog.

Statements that affect mysql database tables now are written to the
binary log using the following rules:

  - Data manipulation statements such as INSERT that change data
    in mysql database tables directly are logged according to the 
    settings of the binlog_format system variable.
  - Statements such as GRANT that change the mysql database
    indirectly are logged as statements regardless of the value of
    binlog_format.

For more details, see Section 6.1.2.4, "Logging Format for
Changes to mysql Database Tables". 

Also added the new section detailing the rules.