Bug #74890 slave error when drop database

• Submitted: 17 Nov 2014 6:16
• Modified: 13 Jan 2015 16:25
• Reporter: qinglin zhang (OCA)
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.5, 5.5.42
• OS: Any
• CPU Architecture: Any
• Tags: database, Drop, foreign key, VIEW

[17 Nov 2014 6:16] qinglin zhang

Description:
slave break when drop database with a table depended by another database.

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

connection master;
create database if not exists db1;
create database if not exists db2;

use db1;
create table a(id int);
create view v as select * from a;
create table table_father(id int auto_increment primary key, name varchar(20)) engine=innodb;

use db2;
create table table_child(id int primary key , info varchar(20), father_id int) engine=innodb;
alter table table_child add constraint aaa foreign key (father_id) REFERENCES db1.table_father(id);

--error 1217
drop database db1;
show binlog events;
drop database db2;

--source include/rpl_end.inc

Suggested fix:
fix as 5.6.16 or more

[17 Nov 2014 7:59] MySQL Verification Team

Hello qinglin zhang,

Thank you for the bug report and test case.
Confirmed with 5.5.42 that slave breaks:

// 5.5.42

Last_Error      Error 'Unknown table 'v'' on query. Default database: 'db1'. Query: 'DROP TABLE `a`,`v`'

Thanks,
Umesh

[13 Jan 2015 16:25] David Moss

Thanks for your feedback. This has now been fixed in our source and will be available in the next release. The following text will be added to the changelog:

When an XA transaction was active, executing an internal rollback, for example using the BINLOG statement, resulted in an assertion. The fix ensures that a rollback happens only for a slave when a transaction spans multiple binary log files. Rollback does not happen now if the Format_description comes from the BINLOG statement being executed in the MySQL client.

[19 Jan 2015 10:00] David Moss

Very sorry for the confusion, I managed to get rather mixed up between these 2 bugs. Just to be clear, the text added to the 5.5.42 release notes is this:
If a DROP DATABASE statement failed on the master, mismatched tables could be left on the slave, breaking replication. This was caused by the DROP TABLE statement being binary logged if at least one table was deleted during the DROP DATABASE operation. The fix ensures that in such a situation the DROP TABLE statement is binary logged with the IF EXISTS option.

Thanks!

[11 Feb 2015 10:24] Laurynas Biveinis

5.5 $ git show -s 5da083ef
commit 5da083ef67eabd92aa316ead7fc3a0af69655977
Author: s.sujatha <[e-mail]>
Date:   Mon Dec 29 12:17:55 2014 +0530

    Bug#20041860: SLAVE ERROR WHEN DROP DATABASE
    
    Fix:
    ===
    Backport Bug#11756194 to mysql-5.5. slave breaks if
    'drop database' fails on master and mismatched tables on
    slave.
    
    'DROP TABLE <deleted tables>' was binlogged when
    'DROP DATABASE' failed and at least one table was deleted
    from the database. The log event would lead slave SQL thread
    stop if some of the tables did not exist on slave.
    
    After this patch, It is always binlogged with 'IF EXISTS'
    option.