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
Triage: D3 (Medium)

[13 Sep 2007 12:04] Dominik Klein
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.
[13 Sep 2007 16:52] Valeriy Kravchuk
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.
[14 Sep 2007 8:44] Jeremy Cole
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.
[30 Nov 2007 4:35] 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/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.
[30 Nov 2007 8:49] Zhenxing He
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.
[3 Dec 2007 5:20] 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/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.
[3 Dec 2007 9:01] 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/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.
[5 Feb 2008 12:46] Bugs System
Pushed into 5.0.56
[5 Feb 2008 13:03] Bugs System
Pushed into 5.1.24-rc
[5 Feb 2008 13:07] Bugs System
Pushed into 6.0.5-alpha
[6 Feb 2008 16:42] Jon Stephens
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.
[6 Mar 2008 9:47] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 19:47] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.
[2 Mar 2010 7:29] Sveta Smirnova
Bug #51621 was marked as duplicate of this one.