MySQL Bugs: #30998: Drop View breaks replication if view does not exist

Bug #30998	Drop View breaks replication if view does not exist
Submitted:	13 Sep 2007 12:04	Modified:	6 Feb 2008 16:42
Reporter:	Dominik Klein	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:	Zhenxing He	CPU Architecture:	Any
Tags:	drop view, replication, stops

Description:
I use mysql-5.0.45 on opensuse 10.2 linux x86.

The following reproducably breaks replication:

master mysql> use anydb;
master mysql> drop view asdasdasd;
ERROR 1051 (42S02): Unknown table 'asdasdasd'

The name of the view does not matter. It does not exist, so mysql gives an error, but the statement makes it to the slave and breaks replication there, as it does not succeed there either.

How to repeat:
Setup replication between two servers. Verify it runs okay.

on master:
mysql> use test;
mysql> drop view does_not_exist;
this gives an error

on slave:
mysql> show slave status\G
this shows that the statement from the master made it to the slave, was not successful and replication was stopped therefor.

Thank you for a bug report. Verified just as described. Note that DROP TABLE non_existant does not go to the binary log. So, this is a bug at least because of inconsistency, and no comments on it at http://dev.mysql.com/doc/refman/5.0/en/drop-view.html.

Much simpler test case:

reset master;
drop view DoEsNoTeXiSt;
show binlog events \G

You will see:

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> drop view DoEsNoTeXiSt;
ERROR 1051 (42S02): Unknown table 'doesnotexist'
mysql> show binlog events \G
*************************** 1. row ***************************
   Log_name: hekla-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 98
       Info: Server ver: 5.0.45-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: hekla-bin.000001
        Pos: 98
 Event_type: Query
  Server_id: 1
End_log_pos: 183
       Info: use `test`; drop view DoEsNoTeXiSt
2 rows in set (0.00 sec)

Obviously it should not be logged.

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

ChangeSet@1.2557, 2007-11-30 12:27:25+08:00, hezx@hezx.(none) +3 -0
  Bug#30998 Drop View breaks replication if view does not exist
  
  Writing binlog after check for errors, if have dropped some views, log the event with error code.

This bug applies to 5.1 too, but in a different way,  the following statements will cause inconsistency on master and slave:

drop table if exists t1;
drop view if exists v1, not_exist_view;
create table t1 (a int);
create view v1 as select * from t1;
drop view v1, not_exist_view;

the last statement will not be written into binlog, while view 'v1' is dropped on master, it will not be dropped on slave.

So if we have dropped some views, we cannot ignore the statement due to some error, it should be logged with error code.

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

ChangeSet@1.2557, 2007-12-03 13:11:40+08:00, hezx@hezx.(none) +3 -0
  Bug#30998 Drop View breaks replication if view does not exist
    
  When executing drop view statement on the master, the statement is written
  into bin-log without checking for possible errors, so the statement would 
  always be bin-logged with error code cleared even if some error might occur, 
  for example, some of the views being dropped does not exist. This would cause 
  failure on the slave.
  
  Writing bin-log after check for errors, if at least one view has been dropped
  the query is bin-logged possible with an error.

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

ChangeSet@1.2632, 2007-12-03 16:54:44+08:00, hezx@hezx.(none) +3 -0
  Bug#30998 Drop View breaks replication if view does not exist
  
  When executing drop view statement on the master, the statement is not written into bin-log if any error occurs, this could cause master slave inconsistence if any view has been dropped. 
  
  If some error occured and no view has been dropped, don't bin-log the statement, if at least one view has been dropped the query is bin-logged possible with an error.

Pushed into 5.0.56

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented bugfix in the 5.0.56, 5.1.24, and 6.0.5 changelogs as follows:

        Issuing a DROP VIEW statement caused
        replication to fail if the view did not actually exist.

Also documented for 5.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.

Bug #51621 was marked as duplicate of this one.