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
Tags: binary log, delete, replication, row mode

[15 Dec 2006 11:47] Giuseppe Maxia
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
[15 Dec 2006 11:49] Giuseppe Maxia
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.

[15 Dec 2006 17:51] Giuseppe Maxia
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
[15 Dec 2006 20:24] Mats Kindahl
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;
[15 Dec 2006 21:19] Mats Kindahl
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.
[16 Feb 2007 10:40] Mats Kindahl
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).
[16 Feb 2007 18:50] Bugs System
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.
[26 Feb 2007 7:49] Rafal Somla
Good to push
[26 Feb 2007 9:19] Bugs System
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.
[8 Mar 2007 8:15] Andrei Elkin
pushed to 5.1.17-beta
[19 Mar 2007 19:03] Paul Dubois
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.