Bug #13684 SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine does not exist
Submitted: 1 Oct 2005 16:18 Modified: 6 May 2009 10:35
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Luis Soares CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

[1 Oct 2005 16:18] Guilhem Bichot
Description:
It's an inconsistency with DROP DATABASE IF EXISTS and DROP TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even if the db or table does not exist. It is useful if you are logged in a replication master server and want to send it cleanup-in-case commands and be sure they will also cleanup on slave. For example if you have the output of mysqldump --add-drop-table and you want to send this output to the master, and have it cleanup on the slave too.
It would be nice or at least consistent if DROP PROCEDURE|FUNCTION IF EXISTS worked the same.
And if IF EXISTS is ever added to DROP TRIGGER, it would be nice if it worked the same too.

How to repeat:
mysql> drop function if exists aaaaa;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1305 | FUNCTION aaaaa does not exist |
+-------+------+-------------------------------+
1 row in set (0.00 sec)
mysql> drop view if exists aaa;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings
    -> ;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1051 | Unknown table 'test.aaa' |
+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> show binlog events;
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name            | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| gbichot3-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.0.14-rc-valgrind-max-debug-log, Binlog ver: 4 |
| gbichot3-bin.000001 | 102 | Query       |         1 |         188 | use `test`; drop view if exists aaa
[21 Jan 2009 10:22] 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/63683

2765 Luis Soares	2009-01-21
      Bug #13684  	SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine does not exist
      
      Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to write_bin_log in 
      mysql_execute_command. Added also a test for checking if all documented 
      "DROP (...) IF EXISTS" get binlogged (except for SERVER - see BUG#25705).
[4 Feb 2009 9:42] 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/65106

2768 Luis Soares	2009-02-04
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine does not exist
      
      There is an inconsistency with DROP DATABASE IF EXISTS, DROP
      TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even
      if the DB or TABLE does not exist, whereas DROP PROCEDURE IF
      EXISTS does not. It would be nice or at least consistent if DROP
      PROCEDURE/STATEMENT worked the same too.
      
      Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
      write_bin_log in mysql_execute_command. Checked also if all
      documented "DROP (...) IF EXISTS" get binlogged. Left out DROP
      SERVER IF EXISTS because it seems that it only gets binlogged when
      using row event (see BUG#25705).
[9 Feb 2009 15:54] 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/65629

2768 Luis Soares	2009-02-09
      BUG#25705: Federated: CREATE SERVER logging
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine 
      does not exist
      
      From BUG#25705, one finds that the drop/create server is not binlogged 
      in mixed or statement mode replication. Instead it gets logged only in row
      based replication as rows inserted/deleted into/from mysql.servers. From 
      BUG#13684, one finds that the drop server if exists is not being binlogged 
      also.
      
      This patch addresses these issues by binlogging CREATE/DROP server even if
      the "IF EXISTS" clause is present. Furthermore, it will always log create/drop 
      server as statements as these are referred in the manual to be "DDL":
       * http://dev.mysql.com/doc/refman/5.1/en/drop-server.html
       * http://dev.mysql.com/doc/refman/5.1/en/create-server.html
[11 Feb 2009 17:13] 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/65938

2752 Luis Soares	2009-02-11
      BUG#13684: post push fix for test case.
      
      The test case relies on binlog entries for assertion. The problem is that the
      binlog does not get cleaned in pushbuild between tests, resulting in extra
      entries in the result file, causing the test to fail.
      
      This fix adds a reset master at the beginning of the test, so that we get a
      clean binlog file.
[14 Feb 2009 13:01] Bugs System
Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (merge vers: 6.0.10-alpha) (pib:6)
[16 Feb 2009 11:22] Jon Stephens
Documented bugfix in the 6.0.10 changelog as follows:

        The statements DROP PROCEDURE IF EXISTS and DROP FUNCTION 
        IF EXISTS were not written to the binary log if the procedure 
        or function to be dropped did not exist.

Also noted old behaviour as a known issue in the 5.0 Manual, since we don't plan to fix it there.

Set status to NDI pending merge of fix to 5.1-main.
[17 Feb 2009 9:57] Lars Thalmann
For 6.0, pushed to main.  For 5.1, pushed to bugteam tree.  
Setting bug to Patch Queued.
[17 Feb 2009 11:39] Simon Mudd
Will these patches be backported to 5.0? We have many production servers and don't anticipating updating to 5.1 for some time due to the effort of the upgrade. Having this patch backported to 5.0 would be most helpful.
[17 Feb 2009 12:50] Lars Thalmann
This patch is not planned for 5.0.
[13 Mar 2009 19:02] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:luis.soares@sun.com-20090211174643-5uq8235tktuw2f28) (merge vers: 5.1.32) (pib:6)
[16 Mar 2009 13:08] Jon Stephens
Fix also documented in the 5.1.33 changelog and the 5.1 version of replication-features-drop-if-exists.

Set status back to Open pending determination whether this will also go into 5.0.
[24 Mar 2009 11:51] 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/70188

2745 Luis Soares	2009-03-24
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if
      routine does not exist 
      
      There is an inconsistency with DROP DATABASE IF EXISTS, DROP TABLE IF
      EXISTS and DROP VIEW IF EXISTS: those are binlogged even if the DB or
      TABLE does not exist, whereas DROP PROCEDURE IF EXISTS does not. It
      would be nice or at least consistent if DROP PROCEDURE/STATEMENT
      worked the same too.
      
      Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
      write_bin_log in mysql_execute_command. Checked also if all documented
      "DROP (...) IF EXISTS" get binlogged.
      
      NOTE: This is a 5.0 backport patch as requested by support.
     @ mysql-test/r/rpl_drop_if_exists.result
        Result file for test case added.
     @ mysql-test/r/rpl_sp.result
        Updated result file for existing test case that has now extra events in
        binary log (the ones from drop if exists procedure/function).
     @ mysql-test/t/rpl_drop_if_exists.test
        Added test case for asserting validity of proposed patch.
     @ sql/sql_parse.cc
        Added call mysql_bin_log.write when lex has drop_if_exists enabled for 
        stored procedures.
[9 Apr 2009 10:41] 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/71761

2723 Luis Soares	2009-04-09
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if
      routine does not exist 
            
      There is an inconsistency with DROP DATABASE IF EXISTS, DROP TABLE IF
      EXISTS and DROP VIEW IF EXISTS: those are binlogged even if the DB or
      TABLE does not exist, whereas DROP PROCEDURE IF EXISTS does not. It
      would be nice or at least consistent if DROP PROCEDURE/STATEMENT
      worked the same too.
            
      Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
      mysql_bin_log.write in mysql_execute_command. Checked also if all 
      documented "DROP (...) IF EXISTS" get binlogged.
            
      NOTE: This is a 5.0 backport patch as requested by support.
     @ mysql-test/r/rpl_drop_if_exists.result
        Result file for test case added.
     @ mysql-test/r/rpl_sp.result
        Updated result file for existing test case that has now extra events in
        binary log (the ones from drop if exists procedure/function).
     @ mysql-test/t/rpl_drop_if_exists.test
        Added test case for asserting validity of proposed patch.
     @ sql/sql_parse.cc
        Added call mysql_bin_log.write when lex has drop_if_exists enabled for 
        stored procedures.
[9 Apr 2009 17:05] Luis Soares
Pushed backport to 5.0-bugteam tree.
[5 May 2009 18:53] Bugs System
Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)
[5 May 2009 19:42] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 10:19] Jon Stephens
Documented bugfix in the 5.0.82 and 5.1.35 changelogs as follows:

        Previously, DROP PROCEDURE IF EXISTS and DROP FUNCTION IF EXISTS were
        not wriiten to the binary log if the routine named did not exist. These
        statements are now always logged, so that these statements now behave in
        the same way as DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, and DROP
        VIEW IF EXISTS.

NDI status: Docs waiting for 6.0 merge/version info.
[6 May 2009 10:35] Jon Stephens
Disregard previous comment -- didn't realise I'd worked on this one already. ;)

Fix also noted in 5.0.82 changelog, 5.0 version "Replication of DROP ... IF EXISTS Statements".

Closed.
[6 May 2009 14:07] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:luis.soares@sun.com-20090409113320-rqswvsb9mnk1ainw) (merge vers: 6.0.11-alpha) (pib:6)
[6 May 2009 14:39] Jon Stephens
Also noted fix in 6.0.12 changelog and Manual. Closed.
[9 May 2009 16:39] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:37] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:34] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)
[15 Jun 2009 8:28] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:08] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:49] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)