| 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: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Linux (linux) |
| Assigned to: | Luis Soares | CPU Architecture: | Any |
[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)
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