MySQL Bugs: #32435: DROP DATABASE statement writes changes to mysql.proc table under RBR

Bug #32435	DROP DATABASE statement writes changes to mysql.proc table under RBR
Submitted:	16 Nov 2007 13:23	Modified:	28 Nov 2007 11:00
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1.23-beta	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
When dropping a database with a stored procedure in, the delete of the stored procedure from the mysql.proc table is recorded in the binary log after the DROP DATABASE statement.

How to repeat:
The following test should print the same both under statement, mixed, and row format (they are all DDL and DDL are replicated as statements currently)::

  set binlog_format=row;
  reset master;
  create database testing_1;
  use testing_1;
  create table t1 (a int);
  create procedure sp1 (a int) insert into t1 values(a);
  drop database testing_1;
  show binlog events;

Suggested fix:
Disable the binary log while removing objects as a result of a drop database statement.

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/37948

ChangeSet@1.2614, 2007-11-16 15:29:30+01:00, mkindahl@dl145h.mysql.com +4 -0
  Bug#32435:
  DROP DATABASE statement writes changes to mysql.proc table under RBR
  
  When replicating a DROP DATABASE statement with a database holding
  stored procedures, the changes to the mysql.proc table was recorded
  in the binary log under row-based replication.
  
  With this patch, the thread uses statement-logging format for the
  duration of the DROP DATABASE statement. The logging format is
  (already) reset at the end of the statement, so no additional code
  for resetting the logging format is necessary.

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/37952

ChangeSet@1.2614, 2007-11-16 15:55:22+01:00, mkindahl@dl145h.mysql.com +4 -0
  Bug#32435:
  DROP DATABASE statement writes changes to mysql.proc table under RBR
  
  When replicating a DROP DATABASE statement with a database holding
  stored procedures, the changes to the mysql.proc table was recorded
  in the binary log under row-based replication.
  
  With this patch, the thread uses statement-logging format for the
  duration of the DROP DATABASE statement. The logging format is
  (already) reset at the end of the statement, so no additional code
  for resetting the logging format is necessary.

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Documented bugfix in 5.1.23 and 6.0.4 changelogs as follows:

        When dropping a database containing a stored procedure while
        using row-cased replication, the delete of the stored procedure
        from the mysql.proc table was recorded in the
        binary log following the DROP DATABASE
        statement. To correct this issue, DROP
        DATABASE now uses statement-based replication.