MySQL Bugs: #69259: DROP (some object) IF EXISTS should not generate warnings

Bug #69259	DROP (some object) IF EXISTS should not generate warnings
Submitted:	16 May 2013 18:30	Modified:	14 Jun 2013 15:38
Reporter:	Juan Manuel Palacios	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.5.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	IF EXISTS, warning

Description:
The MySQL 'IF EXISTS' extension should prevent the server from generating warnings when used with the DROP clause to drop some object, like a table or a procedure. When used on those two types of objects (and maybe others, but I haven't tested), e.g. DROP PROCEDURE IF EXISTS foo, and said object doesn't exist, the server generates a warning precisely about the object not existing.

I understand the server producing an error when the object doesn't and IF EXISTS is not used. But, the way I see it, by using that extension I am explicitly telling the server that I am perfectly aware that the object I'm dropping may not exist at one point or another, so from that perspective it's absolutely unnecessary to generate a warning if in fact it doesn't.

One scenario in which this is important is when running scripts to initialize DB's. In such cases you may not be able to pay attention to every single query because there may be a multitude of them, each executing very fast, and at best what you're gonna get is a summary at the end telling you there was a warning that now you have to hunt out. And it's not very pleasant to find out the warning was generated by a case you explicitly contemplated by using IF EXISTS.

If generating warnings even when using this extension is necessary for some reason I'm not seeing, then maybe there should be configurable modes for it that we could tweak to turn them on/off.

How to repeat:
mysql> CREATE PROCEDURE foo() SELECT NOW();
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure foo;
Query OK, 0 rows affected (0.02 sec)

mysql> drop procedure foo;
ERROR 1305 (42000): PROCEDURE tests.foo does not exist

mysql> drop procedure if exists foo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1305 | PROCEDURE tests.foo does not exist |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Make the IF EXISTS extension suppress all server warnings about an object not existing when used in conjunction with the DROP clause.

If this is not entirely possible for whatever reason, then please consider creating an operational mode under which the server does indeed suppress this kind of warnings.

Thank you for the bug report. Even understanding your point I could say isn't a bug if removed that warning anyone will open a bug report asking enabling again the warning because he wants to know through the warning or absence of it if actually an object was removed or not. At least could be a feature request to introduce a way to disable that warning with a variable for option file/command line.

Yes, the disabling of the warning could be a server setting, available to the cnf file, to the server during runtime (e.g. SET @@warn_if_exists = 1), and maybe even to command line clients as well.

And since I think the argument about knowing that the object may not exist if 'IF NOT EXISTS' is used, I suggest the mode be set by default to silence the warnings, i.e. to not emit them.

Anything I should provide to get this ticket out of "Need Feedback" and into planning for implementation?

Regards!

The documentation (http://dev.mysql.com/doc/refman/5.5/en/drop-table.html) says:
Use IF EXISTS to prevent an error from occurring for tables that do not exist. A NOTE is generated for each nonexistent table when using IF EXISTS. See Section 13.7.5.41, “SHOW WARNINGS Syntax”.

Sounds to me like it works as documented.
Which means this is not a bug, but a feature request (change of behavior).

Changing this ticket to feature request, according to comments recently posted to it.