Bug #58381 Failed DROP DATABASE can break statement based replication

• Submitted: 22 Nov 2010 13:40
• Modified: 21 Apr 2011 1:15
• Reporter: Jon Olav Hauglid
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5
• OS: Any
• CPU Architecture: Any

[22 Nov 2010 13:40] Jon Olav Hauglid

Description:
If DROP DATABASE fails to drop a table, nothing will get written to the
binlog even if some tables were dropped successfully.

This problem was found while working on Bug#57663.
It is repeatable both in 5.1 and 5.5.

How to repeat:
--source include/have_innodb.inc
--source include/have_log_bin.inc
--source include/have_binlog_format_statement.inc

--disable_warnings
DROP DATABASE IF EXISTS db1;
DROP TABLE IF EXISTS t4;
--enable_warnings

CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb;
CREATE TABLE db1.t3 (c INT);
CREATE TABLE t4 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) engine=innodb;
RESET MASTER;

--error ER_ROW_IS_REFERENCED
DROP DATABASE db1;                     # Fails because of the fk
SHOW TABLES FROM db1;                  # Only db2.t2 remains in db1
SHOW BINLOG EVENTS;                    # But the binlog will be empty

# Cleanup
DROP TABLE t4;
DROP DATABASE db1;

Suggested fix:
Write DROP TABLE to the binlog for tables that were dropped when DROP DATABASE as a whole fails.

[24 Nov 2010 16:37] Omer Barnir

triage: setting tag to SR51MRU

[21 Apr 2011 1:15] Paul DuBois

Noted in 5.5.11, 5.6.3 changelogs.

A failed DROP DATABASE statement could break statement-based
replication.  

CHANGESET - http://lists.mysql.com/commits/134381